Linq to SQL - Query with multiple joins, sum, grouping, having

Question

I have the following query that I would like to translate to linq.

SELECT
SUM(Credits.CreditAmount)
,Transactions.Id
,Person.FullName
,Person.Id
FROM
Person
JOIN
Transactions
ON Person.AccountId = Transactions.AccountId
JOIN Credits
ON Transactions.Id = Credits.TransactionId
WHERE
Person.Type = 'AccountHolder'
AND Person.Status = 'Active'
AND Transactions.CancelledDate IS NULL
AND Credits.CancelledDate IS NULL
GROUP BY Transactions.AccountId, Person.FullName, Person.Id
HAVING SUM(Credits.CreditAmount) > 20

This is what I came up with. It's an absolute pig... The SQL it generates must be awful.

var query = from p in Person
            join t in Transactions
            on p.AccountId equalas t.AccountId
            join c in Credits
            on t.TransactionId = c.TransactionId
            where p.Status == "Active" &&
            p.Type = "AccountHolder" &&
            t.CancelledDate == null &&
            c.CancelledDate == null
            group new { c.CreditAmount, t.AccountId, p.FullName, p.Id } by new { t.AccountId, p.FullName, p.SSN } into grp
            let sumC = grp.Select(x => x.CreditAmount).Sum()
            select new
            {
                TotalCredit = sumC,
                AccountId = grp.Key.AccountId,
                FullName = grp.Key.FullName,
                Id = grp.Key.Id
            };
query.Where(p => p.TotalServiceCredit > 20);

The SQL query runs in approximately 3 seconds but I have yet to find the patience to let the Linq query finish. I was wondering if there is something different I should be doing to accomplish this "group, sum, having" query I'm trying to write? Is there something I can do to help Linq generate more performat SQL?

UPDATE

Turns out sgmoore had the right idea. The key to the performance issue was in his answer.

The difference between this

let sumC = grp.Select(x => x.CreditAmount).Sum()

and this

TotalCredit = grp.Sum(x => x.CreditAmount)

was the difference between a query that finishes and one that does not.

See my revised LINQ query below which completes in about the same time as the SQL (5.3 seconds for SQL vs 5.6 seconds for LINQ).

var query = from p in Person
        join t in Transactions
        on p.AccountId equalas t.AccountId
        join c in Credits
        on t.TransactionId = c.TransactionId
        where p.Status == "Active" &&
        p.Type = "AccountHolder" &&
        t.CancelledDate == null &&
        c.CancelledDate == null
        group new { c.CreditAmount, t.AccountId, p.FullName, p.Id } by new { t.AccountId, p.FullName, p.SSN } into grp
        select new
        {
            TotalCredit = grp.Sum(x => x.CreditAmount),
            AccountId = grp.Key.AccountId,
            FullName = grp.Key.FullName,
            Id = grp.Key.Id
        };
query.Where(p => p.TotalServiceCredit > 20);

Thanks for all your help!

Why are you trying to convert the SQL query to LINQ? If SQL is performing fast, why disturb a good thing? — SS_DBA
– SS_DBA, Commented Oct 31, 2017 at 13:56
I'm using it as a learning exercise to grow my understanding of LINQ. Your suggestion is, of course, the practical solution, but "don't use LINQ" doesn't help me learn about LINQ. This is a relatively simple aggregate query. I'm simply interested in how to accomplish this using LINQ. — Nick
– Nick, Commented Nov 1, 2017 at 15:06
Very good. Do you get the same results from both? Or you never let it return rows from LINQ due to performance issue. — SS_DBA
– SS_DBA, Commented Nov 1, 2017 at 15:09
I never let it complete. The longest I let it go was one minute. The SQL returns in 5 seconds. — Nick
– Nick, Commented Nov 1, 2017 at 15:46

sgmoore · Accepted Answer · 2017-10-31 16:58:52Z

3

I don't disagree with WEI_DBA's comment but if you need to do this, then you might find it easier to break this into several queries, eg

    var query1 = from p in Person
        join t in Transactions on p.AccountId equals t.AccountId
        join c in Credits  on t.TransactionId equals c.TransactionId

        where p.Status == "Active" &&
        p.Type = "AccountHolder" &&
        t.CancelledDate == null &&
        c.CancelledDate == null
        select new { c.CreditAmount, t.AccountID, p.FullName, p.Id};


    var query2 = (from p in query1
        group p by new { p.AccountId, p.FullName, p.Id } into grp

        select new
        {
            TotalCredit = grp.Sum(x => x.CreditAmount),
            AccountId   = grp.Key.AccountId,
            FullName    = grp.Key.FullName,
            Id          = grp.Key.Id
        };


    var query3 = (from p in query2 where p.TotalCredit > 20 select p);

Then you can let LINQ combine this into one sql command.

As always, it is a good idea to check and verify the actual TSQL generated.

answered Oct 31, 2017 at 16:58

sgmoore

16.1k6 gold badges46 silver badges72 bronze badges

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

1 Comment

Nick Over a year ago

Thanks, I'll check it out!

Collectives™ on Stack Overflow

Linq to SQL - Query with multiple joins, sum, grouping, having

UPDATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related