Solved

LINQ Entity Query Return Null Subtract Error?

Posted on 2014-01-02
8
1,562 Views
Last Modified: 2016-02-10
I have a simple LINQ Entiry query which returns null because NO table rows exist yet. I would like a more elegant code instead of the one I currently have working. Help.

var incomes = (from i in context.Incomes
                           where i.UserID == 101
                           group i by i.UserID into g
                           select new { Total = g.Sum(s => s.Amount) }).FirstOrDefault();

decimal incomesTotal = 0;
if (incomes != null)
{
    incomesTotal = incomes.Total;
}

MyViewModel model = new MyViewModel
{
    // totalDiff = (decimal?)incomes.Total ?? 0 - budget.Total,   // Not Working Solution

    totalDiff = incomesTotal - budget.Total
};
0
Comment
Question by:WorknHardr
  • 4
  • 4
8 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39751462
Why not simply:

MyViewModel model = new MyViewModel
{
    totalDiff = context.Incomes
                       .Where(i => i.UserID == 101)
                       .Sum(i => i.Amount)
};

Open in new window

0
 

Author Comment

by:WorknHardr
ID: 39751510
I still get an error if "Amount" is null.

Error: The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

MyViewModel model = new MyViewModel
{
    totalDiff = context.Incomes
                       .Where(i => i.UserID == 101)
                       .Sum(i => i.Amount) - budget.Total
};

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39751540
Ah, I missed that detail. You can add one more call:

MyViewModel model = new MyViewModel
{
    totalDiff = context.Incomes
                       .Where(i => i.UserID == 101)
                       .Sum(i => i.Amount)
                       .DefaultIfEmpty() - budget.Total
};

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:WorknHardr
ID: 39751634
Hmm, still same error. This is the most difficulty I've experienced with LINQ. I had to place the DefaultIfEmpty differently because it doesn't exist the other way and still doesn't work.
MyViewModel model = new MyViewModel
{
    totalDiff = context.Incomes
                       .Where(i => i.UserID == 101)
                       .DefaultIfEmpty() //<--
                       .Sum(i => i.Amount) - budget.Total
};

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39751688
Let's blame it on my sleep deprivation   ; )

What is the type of the Amount property?
0
 

Author Comment

by:WorknHardr
ID: 39751746
Okay making progress,...

1. changed 'totalDiff' to Nullable
2. added Nullable to query
3. added .GetValueOrDefault(0)
4. Works!
    - view returns $-2191.03 which is correct

public Nullable<decimal> totalDiff{ get; set; }

MyViewModel model = new MyViewModel
{
    totalDiff = context.Incomes
                       .Where(i => i.UserID == 101)
                       .Sum(i => (decimal?)i.Amount)
                       .GetValueOrDefault(0)
                      - budget.Total
};

[View]
@Model.totalDiff .Value.ToString("c")
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 400 total points
ID: 39751959
If the Amount property is itself nullable, then you could shorten that to:

MyViewModel model = new MyViewModel
{
    totalDiff = context.Incomes
                       .Where(i => i.UserID == 101)
                       .Sum(i => i.Amount ?? 0M)
                      - budget.Total
};

Open in new window

0
 

Author Closing Comment

by:WorknHardr
ID: 39754222
I like it, thanks again for great help :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question