CODEWITHSUNDEEP
c#
Martin Katumba
Martin Katumba

Reputation: 1

Convert Sql query to C# linq entity framework

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);
}

Upvotes: 0

Views: 248

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131704

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.

Upvotes: 3

Tomas Chabada
Tomas Chabada

Reputation: 3019

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

Upvotes: 1

Related Questions