• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1629
  • Last Modified:

LINQ Entity Query Return Null Subtract Error?

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
WorknHardr
Asked:
WorknHardr
  • 4
  • 4
1 Solution
 
käµfm³d 👽Commented:
Why not simply:

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

Open in new window

0
 
WorknHardrAuthor Commented:
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
 
käµfm³d 👽Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
WorknHardrAuthor Commented:
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
 
käµfm³d 👽Commented:
Let's blame it on my sleep deprivation   ; )

What is the type of the Amount property?
0
 
WorknHardrAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
WorknHardrAuthor Commented:
I like it, thanks again for great help :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now