Solved

LINQ Entity Query Return Null Subtract Error?

Posted on 2014-01-02
8
1,546 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now