Convert Sql query to C# linq entity framework

Question

i need to convert the the following sql query to C# linq with entity framework:

SELECT        AccNo, SUM(Debit) - SUM(Credit) AS Balance FROM  TrxAffair
WHERE        (TrxDate > '2021-01-01') GROUP BY AccNo

i have tried the following but i get:: '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.'

partial void OnGetPreviousBalance(decimal items);

public decimal GetPreviousBalance(DateTime trxDate)
{
    var items = context.TrxAffairs
                       .AsQueryable()
                       .Where(i => i.TrxDate < trxDate)
                       .GroupBy(o => o.AccNo)
                       .Select(s => s.Sum(i=>(i.Credit-i.Debit)));

    OnGetPreviousBalance(items.Sum(s=>s.Value));

    return items.Sum(s => s.Value);
}

In SQL you have a difference of sums, not the sum of differences. As for casts, it seems to complain that one of the class properties should be nullable. You didn't post the class or the table schema so one can only guess. If either one is null though, i.Credit -i.Debit would cause problems in SQL as well — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 22, 2021 at 7:36
Also comparing TrxDate seems opposite in SQL and LINQ > vs < — Tomas Chabada
– Tomas Chabada, Commented Apr 22, 2021 at 7:37
In SQL, if you used SUM(Credit - Debit) you'd get a NULL if either was null. Instead of +100 or -100, you'd get nothing. Use s.Sum(i.Credit) - s.Sum(i.Debit) to generate the same SQL query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 22, 2021 at 7:39
AND add a ToList(), to avoid enumerating the results over and over again. And store the grand total in a variable to avoid calculating it twice — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 22, 2021 at 7:40

Panagiotis Kanavos · Accepted Answer · 2021-04-22 09:21:39Z

3

There are several problems in the LINQ query.

The aggregation calculates the sum of differences, not the difference of sums. If either field is NULL, the difference will be NULL as well.
The date check is reversed.
The account is missing from the results.

The equivalent would be :

var items = context.TrxAffairs
                   .Where(i => i.TrxDate > trxDate)
                   .GroupBy(o => o.AccNo)
                   .Select(s => new {
                       o.AccNo, 
                       Balanse=s.Sum(i=>i.Credit)-s.Sum(i=>i.Debit)
                   })
                   .ToList();

After that, calculate the grand total just once and reuse it :

var grandTotal=items.Sum(s=>s.Value);
OnGetPreviousBalance(grandTotal);

return grandTotal;

You can replace NULLs with 0s. In SQL you'd use ISNULL(Credit,0). In C#/LINQ, you can use i.Credit ?? 0:

var items = context.TrxAffairs
                   .Where(i => i.TrxDate > trxDate)
                   .GroupBy(o => o.AccNo)
                   .Select(s => new {
                       AccNo=s.Key, 
                       Balanse=s.Sum(i=>(i.Credit??0) - (i.Debit??0))
                   })
                   .ToList();

If you only want the grand total theres' no reason to group:

var grandTotal = context.TrxAffairs
                   .Where(i => i.TrxDate > trxDate)
                   .Sum(i=>(i.Credit??0) - (i.Debit??0));

Don't rush to replace SQL with LINQ though.

LINQ isn't SQL. SQL is far more powerful when it comes to reporting and analytics. A server is far more powerful than any client and can aggregate data while filtering it.

You can get both the totals and grand total in SQL if you use ROLLUP. This can't be done in LINQ. In this case, the following query:

SELECT        AccNo, SUM(Debit) - SUM(Credit) AS Balance 
FROM  TrxAffair
WHERE        (TrxDate > '2021-01-01') 
GROUP BY AccNo
WITH ROLLUP

or

SELECT        AccNo, SUM(Debit) - SUM(Credit) AS Balance 
FROM  TrxAffair
WHERE        (TrxDate > '2021-01-01') 
GROUP BY ROLLUP (AccNo)

Would return the grand total as the last column with a NULL AccNo. This can have a significant impact when there are a lot of data or multiple grouping levels.

Other clauses, like CUBE or GROUPING SETS can be used to generate intermediate totals for multiple combinations. This would be very complex (and expensive) to do on the client.

For example, you could aggregate sales by Country/Product and Product/Country in a single query :

SELECT Country, Product, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( (Country, Product), (Product, Country) );

Will generate subtotals for two different combinations. Without this, you'd have to write two different queries.

edited Apr 22, 2021 at 9:21

answered Apr 22, 2021 at 7:45

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Magnus Over a year ago

o.AccNo --> s.Key

Panagiotis Kanavos Over a year ago

@Magnus oops, fixed

Panagiotis Kanavos Over a year ago

@Charlieface oops again. I started copying the doc example when I realized that using Country and Region in both cases wouldn't be the best example. Then I forgot to clean the rest of the query

Tomas Chabada · Accepted Answer · 2021-04-22 07:44:08Z

1

Despite edits stated in comments Ensure that Credit and Debit values aren't propagated with null values.

GetValueOrDefault() may help there if Credit or Debit values are nullable

answered Apr 22, 2021 at 7:44

Tomas Chabada

3,0391 gold badge20 silver badges19 bronze badges

Collectives™ on Stack Overflow

Convert Sql query to C# linq entity framework

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related