CODEWITHSUNDEEP
c#sql-serverlinqlambda
Amr Ibrahim
Amr Ibrahim

Reputation: 187

Lambda expression group by select last with a condition

With this Code :

List<Transaction> list = db.Transaction
                           .GroupBy(t => t.ProcessId)
                           .Select(g => g.OrderByDescending(t => t.TransactionId).FirstOrDefault())
                           .Where(t => t.xId != null && t.PAmount > 0)
                           .ToList();

I was trying to get the last record where xId is not null .. the grouping works fine .. but it gets me the last record even if xId is null

Any help!!

Upvotes: 1

Views: 1489

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236268

Just move filtering before grouping:

db.Transaction
  .Where(t => t.xId != null && t.PAmount > 0)
  .GroupBy(t => t.ProcessId)
  .Select(g => g.OrderByDescending(t => t.TransactionId).FirstOrDefault())
  .ToList();

Thus you will get for each process latest transaction which has id and amount greater than zero. Otherwise if latest transaction has id equal to null, you will completely filter out that process transactions.

EF will generate query like

SELECT
    [Limit1].[TransactionId] AS [TransactionId],
    [Limit1].[xId] AS [xId],
    [Limit1].[PAmount] AS [PAmount],
    [Limit1].[ProcessId] AS [ProcessId]
    FROM 
    (SELECT DISTINCT
        [Extent1].[ProcessId] AS [ProcessId]
        FROM [dbo].[Transactions] AS [Extent1]
        WHERE ([Extent1].[xId] IS NOT NULL) AND ([Extent1].[PAmount] > 0)
    ) AS [Distinct1]
    OUTER APPLY
    (SELECT TOP (1) 
       [Project2].[TransactionId] AS [TransactionId],
       [Project2].[xId] AS [xId],
       [Project2].[PAmount] AS [PAmount],
       [Project2].[ProcessId] AS [ProcessId]
       FROM ( SELECT
            [Extent2].[TransactionId] AS [TransactionId],
            [Extent2].[xId] AS [xId],
            [Extent2].[PAmount] AS [PAmount],
            [Extent2].[ProcessId] AS [ProcessId]
            FROM [dbo].[Transactions] AS [Extent2]
            WHERE ([Extent2].[xId] IS NOT NULL) AND ([Extent2].[PAmount] > 0)
                   AND ([Distinct1].[ProcessId] = [Extent2].[ProcessId])
            ) AS [Project2]
        ORDER BY [Project2].[TransactionId] DESC
    ) AS [Limit1]

As you can see xId NOT NULL and PAmount > 0 conditions applied twice - first time to get distinct process ids (which have appropriate transactions). And second time when latest transaction is selected for each process.

Upvotes: 2

Related Questions