CODEWITHSUNDEEP
c#sqllinq
Jez
Jez

Reputation: 30071

How do I do this SQL in LINQ

I have something I can do really easily in SQL but I just can't figure out how to do it in LINQ. So I have 3 tables: Return, ReturnItem, and ReturnItemTest. Return has 1..n ReturnItems and ReturnItem has 0..1 ReturnItemTests. The tables look like this:

Return
======
ReturnId int not null (PK)
ReturnName nvarchar(max) not null

ReturnItem
==========
ReturnItemId int not null (PK)
ReturnId int not null (FK)
ReturnItemStatus int not null

ReturnItemTest
==============
ReturnItemId int not null (PK, FK)
ReturnItemTestStatus int not null

Each return has return items, and each return item may have 0 or 1 tests. Both return items and return item tests have a status. I want to count up how many return item status codes and return item test status codes there are, grouping by the status number of both. However a LEFT OUTER JOIN is needed because a return item may not have a test. So in SQL I say:

SELECT
          ri.[ReturnItemStatus] AS ItemStatus,
          rit.[ReturnItemTestStatus] AS TestStatus,
          COUNT([ReturnItem].[ReturnItemStatus]) as ComboCount
FROM
          [Return] r
          INNER JOIN [ReturnItem] ri ON r.ReturnId = ri.ReturnId
          LEFT OUTER JOIN [ReturnItemTest] rit ON ri.ReturnItemId = rit.ReturnItemId
GROUP BY
          ri.[ReturnItemStatus], rit.[ReturnItemTestStatus]

This gives me a result showing all the extant combinations of return item status, return item test status, and the count for each combination. How do I achieve the same with LINQ? I got this far:

var returns =
    (
        from r in ctx.Returns
        join ri in ctx.ReturnItems on r.ReturnID equals ri.ReturnID
        join rit in ctx.ReturnItemTests on ri.ReturnItemID equals rit.ReturnItemTestID into ritJoined
        from rit in ritJoined.DefaultIfEmpty()
        select new {
            ReturnItemStatus = ri.ReturnItemStatus,
            ReturnItemTestStatus = rit == null ? null : (int?)rit.ReturnItemTestStatus
        }
    ).ToList();

... which shows me the return item statuses LEFT OUTER JOINed to the test statuses, but I can't figure out how to get the grouping and counting to work.

Upvotes: 1

Views: 118

Answers (2)

Jez
Jez

Reputation: 30071

This is how I managed to do it in the end:

var returns = (
    // Grab from returns table
    from r in ctx.Returns
    // Inner join with return items
    join ri in ctx.ReturnItems on r.ReturnID equals ri.ReturnID
    // Filter down by return 'closed on' date
    where (
        r.ClosedOn > startDate &&
        r.ClosedOn <= endDate
    )
    // Join with return item tests.  The 'into' clause is powerful and should be used regularly for complex queries;
    // really, the lack of an 'into' projection clause can usually be thought of as shorthand.  Here, 'into' projects
    // the 0..n join hierarchically as an IEnumerable in what is called a 'group join'.
    join rit in ctx.ReturnItemTests on ri.ReturnItemID equals rit.ReturnItemID into ritGroupJoined
    // 'Flatten out' the join result with the 'from' clause, meaning that group join results with eg. 3 matches will
    // cause 3 items in the resultant enumeration, and group join results with zero matches will cause zero items
    // in the resultant enumeration.  The .DefaultIfEmpty() method means that these results will instead cause one
    // item in the resultant enumeration, having the default value for that type (ie. null, as it's a reference type).
    // Note that without the 'into' group join above, it's not possible to access the join results with zero matches as
    // they are automatically discarded from the results during the default 'inner join'-style flattening.
    from rit in ritGroupJoined.DefaultIfEmpty()
    // Project these results into an intermediary object to allow ReturnItemTestStatus to be null (as a int? type);
    // without this, we couldn't group them because any grouped items whose ReturnItemTestStatus was null would cause
    // a type error, null being an invalid value for the ReturnItemTests.ReturnItemTestStatus property (an int type).
    select new {
        ReturnItemStatus = ri.ReturnItemStatus,
        ReturnItemTestStatus = rit == null ? null : (TestStatusEnum?)rit.ReturnItemTestStatus,
    } into retData
    // Finally, we can now group this flattened data by both item status and item test status; to group by multiple
    // fields in LINQ, use an anonymous type containing the fields to group by.
    group retData by new { retData.ReturnItemStatus, retData.ReturnItemTestStatus } into retGrouped
    // ... and project into an object to get our item status counts.
    select new
    {
        ReturnItemStatus = retGrouped.Key.ReturnItemStatus,
        ReturnItemTestStatus = retGrouped.Key.ReturnItemTestStatus,
        Count = retGrouped.Count()
    }
).ToList();

Upvotes: 0

Anton&#237;n Lejsek
Anton&#237;n Lejsek

Reputation: 6103

As you do not use the Return table at all, I would skip it. You have this query

SELECT
          ri.[ReturnItemStatus] AS ItemStatus,
          rit.[ReturnItemTestStatus] AS TestStatus,
          COUNT(*) as ComboCount
FROM
          [ReturnItem] ri
          LEFT OUTER JOIN [ReturnItemTest] rit ON ri.ReturnItemId = rit.ReturnItemId
GROUP BY
          ri.[ReturnItemStatus], rit.[ReturnItemTestStatus]

While you can just append grouping to your query, it may not be the best approach. You explicitely define joining keys even when that should not be necessary. In your case you can have at most one test per item so you should be able to write this:

ctx.ReturnItems
   .Select(ri => new { ri.ReturnItemStatus, ri.ReturnItemTest.ReturnItemTestStatus })
   .GroupBy(x => x, (x, y) => new { x.ReturnItemStatus, x.ReturnItemTestStatus, Count = y.Count() })

Note, that ri.ReturnItemTest.ReturnItemTestStatus is executed on sql server and it would return null when ReturnItemTest is null as a default behaviour of the server.

Upvotes: 1

Related Questions