CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
Jun Rikson
Jun Rikson

Reputation: 1884

IsNull in LINQ Sum()

I have problem with IsNull in LINQ :

db.WarehouseInputsDetails
    .Select(p => new WarehouseInputDetailsViewModel
    {
        Id = p.Id
        RemainingQuantity = p.Quantity - p.LoadingsDetails.Sum(n => n.Quantity)
    }).AsQueryable();

p.LoadingsDetails.Sum(n => n.Quantity) possibility NULL. I want to have result like this :

select Id, IsNull(Sum(Quantity),0) as Quantity from LoadingsDetails

I have tried something like :

db.WarehouseInputsDetails
    .Select(p => new WarehouseInputDetailsViewModel
    {
        Id = p.Id
        RemainingQuantity = p.LoadingDetails.First() == null ? p.Quantity : p.Quantity - p.LoadingsDetails.Sum(n => n.Quantity)
    }).AsQueryable();

It return errors :

System.NotSupportedException: The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.

I have tried something like this :

db.WarehouseInputsDetails
    .Select(p => new WarehouseInputDetailsViewModel
    {
        Id = p.Id
        RemainingQuantity = (p.LoadingsDetails.Sum(n => n.Quantity) == DBNull.Value) ? p.Quantity : p.Quantity - p.LoadingsDetails.Sum(n => n.Quantity)
    }).AsQueryable();

OR

db.WarehouseInputsDetails
    .Select(p => new WarehouseInputDetailsViewModel
    {
        Id = p.Id
        RemainingQuantity = p.LoadingsDetails.First().Quantity == DBNull.Value ? p.Quantity : p.Quantity - p.LoadingsDetails.Sum(n => n.Quantity)
    }).AsQueryable();

Return Errors :

Operator '==' cannot be applied to operands of type 'decimal' and 'DBNull'

enter image description here

Upvotes: 0

Views: 297

Answers (1)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62498

You can use Any() here which will check if there is any row for LoadingDetails, if yes then Sum the Quantity for LoadingDetails:

RemainingQuantity = p.LoadingDetails.Any()  ? 
                p.Quantity - p.LoadingsDetails.Sum(n => n.Quantity) : 
                p.Quantity

Upvotes: 2

Related Questions