CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
Jaxidian
Jaxidian

Reputation: 13509

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)

Upvotes: 2

Views: 241

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205849

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.

Upvotes: 2

Related Questions