Link to home
Create AccountLog in
Avatar of WorknHardr
WorknHardr

asked on

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
};
Avatar of kaufmed
kaufmed
Flag of United States of America image

Why not simply:

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

Open in new window

Avatar of WorknHardr
WorknHardr

ASKER

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

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

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

Let's blame it on my sleep deprivation   ; )

What is the type of the Amount property?
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")
ASKER CERTIFIED SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I like it, thanks again for great help :)