Jaxidian Jaxidian - 3 years ago 71
C# Question

Translating query from SQL to EF Lambda expressions

I'm having trouble translating a SQL query into the appropriate EF query. I'm close but I think I'm missing something with the left join.

Here's my SQL (a slightly contrived example):

SELECT
Count(*), -- count posts
Tag.Name,
ISNULL(Category.Name, 'Other')
FROM Post
INNER JOIN Tag ON Post.TagID=Tag.ID
LEFT OUTER JOIN Category ON Tag.CategoryID=Category.ID
GROUP BY
Tag.Name, ISNULL(Category.Name, 'Other')


Posts have 0-1 Tags (like I said, slightly contrived example). Tags have 0-1 Categories. So the
INNER
and
LEFT
joins are important.

Here's my not-quite-right EF Query:

var counts = ctx.Posts
.GroupBy(po =>
new
{
Tag = po.Tag.Name,
Category = po.Tag.Category.Name ?? "Other"
})
.Select(agg =>
new
{
NumberOfPosts = agg.Count(),
Tag = agg.Key.Tag,
Category = agg.Key.Category
})
.ToList();


This EF query results in this SQL Query, which isn't quite right:

SELECT
1 AS [C1],
[GroupBy1].[A1] AS [C2],
[GroupBy1].[K1] AS [Name],
[GroupBy1].[K2] AS [C3]
FROM ( SELECT
[Join2].[K1] AS [K1],
[Join2].[K2] AS [K2],
COUNT([Join2].[A1]) AS [A1]
FROM ( SELECT
[Extent2].[Name] AS [K1],
CASE WHEN ([Extent3].[Name] IS NULL) THEN N'Other' ELSE [Extent3].[Name] END AS [K2],
1 AS [A1]
FROM [dbo].[Post] AS [Extent1]
LEFT OUTER JOIN [dbo].[Tag] AS [Extent2] ON [Extent1].[TagID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[Category] AS [Extent3] ON [Extent2].[CategoryID] = [Extent3].[ID]
) AS [Join2]
GROUP BY [K1], [K2]
) AS [GroupBy1]


One of the joins is incorrect. Also, I'm not sure if the ISNULL is handled correctly by the GROUP BY or not (it's important since I want it to group with both null values as well as values in the DB with an "Other" value all together as one).

How would I fix this? Or is this just one of those funny scenarios where I need to fall back to something else (a sproc or view)?

VS2017/C#/.NET4.7/EF6.13/SQLAzure

(Edited to add the resulting SQL statement)

Answer Source

The type of the generated joins from reference navigation properties depends of how the navigation property has been setup - Required -> inner join, Optional -> left outer join.

Since both your relationships are optional, the generated SQL uses left outer joins.

Simply inserting .Where(po => po.Tag) will produce a correct result. I was also hoping that EF will be smart enough to turn the corresponding left outer join into inner join, but it doesn't.

However, inserting intermediate projection and then applying not null filter does the trick:

var counts = ctx.Posts
    .Select(po => new { po.Tag })
    .Where(po => po.Tag != null)
    .GroupBy(po => new
    {
        Tag = po.Tag.Name,
        Category = po.Tag.Category.Name ?? "Other"
    })
    .Select(agg => new
    {
        NumberOfPosts = agg.Count(),
        Tag = agg.Key.Tag,
        Category = agg.Key.Category
    })
    .ToList();

which generates the desired join types:

SELECT
    1 AS [C1],
    [GroupBy1].[A1] AS [C2],
    [GroupBy1].[K1] AS [Name],
    [GroupBy1].[K2] AS [C3]
    FROM ( SELECT
        [Filter1].[K1] AS [K1],
        [Filter1].[K2] AS [K2],
        COUNT([Filter1].[A1]) AS [A1]
        FROM ( SELECT
            [Extent2].[Name] AS [K1],
            CASE WHEN ([Extent3].[Name] IS NULL) THEN N'Other' ELSE [Extent3].[Name] END AS [K2],
            1 AS [A1]
            FROM   [dbo].[Post] AS [Extent1]
            INNER JOIN [dbo].[Tag] AS [Extent2] ON [Extent1].[TagId] = [Extent2].[Id]
            LEFT OUTER JOIN [dbo].[Category] AS [Extent3] ON [Extent2].[CategoryId] = [Extent3].[Id]
            WHERE 1 = 1
        )  AS [Filter1]
        GROUP BY [K1], [K2]
    )  AS [GroupBy1]

The only redundant is WHERE 1=1, but the SQL query optimizer should be able to eliminate it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download