CODEWITHSUNDEEP
c#linqentity-framework-core
Matt R
Matt R

Reputation: 2627

linq groupjoin does not generate expected sql

I am currently not getting the expected results from Microsoft.EntityFrameworkCore version 1.1.1.

Tables definition

There is currently a Story with Id = 1 which has 0 Votes. I m trying to get GroupJoin to return an object where if there are no votes, the Story object is not null and the Votes list is either empty or null (I think it will be empty)

I have come up with this query in c#:

var storyVotes = await StoriesDbContext.Stories.GroupJoin(StoriesDbContext.Votes,
                                                          s => s.Id,
                                                          v => v.StoryId,
                                                          (s, v) => new { Story = s, Votes = v })
                                                          .Where(dict => dict.Story.Id == storyId && (dict.Votes == null || dict.Votes.Any(vo => vo.UserId == userId)))
                                                          .FirstOrDefaultAsync();

Edit: As Christos pointed out, the above C# did not compile. I made an editing mistake when I posted the code. The above now correctly represents the code and the result is still the same.

storyVotes is null in this situation. I'm a little confused.

linqpad results

LinqPad returns the result I expect. It returns the Story object and the a list (with 0 items).

The query I run directly on the DB also returns the results.

SELECT s."Id" as StoryId, v."Id" as VoteId
FROM "Stories" s
left outer JOIN "Votes" v on s."Id" = v."StoryId"
WHERE v."UserId" = 'fa157323-c4b2-4d8a-b7fc-c38988405f61' or v."UserId" is null
and s."Id" = 1;

pgadmin 3 results

According to Application Insights, EF generates this query

SELECT "s"."Id", "s"."CreatedDate", "s"."Description", "s"."DescriptionMarkdown", "s"."ModifiedDate", "s"."Score", "s"."Title", "s"."Upvotes", "s"."Url", "s"."UserId", "s"."UserIsAuthor", "v"."Id", "v"."CommentId", "v"."CreatedDate", "v"."ModifiedDate", "v"."StoryId", "v"."UserId"
FROM "Stories" AS "s"
LEFT JOIN "Votes" AS "v" ON "s"."Id" = "v"."StoryId"
WHERE "s"."Id" = @__storyId_0
ORDER BY "s"."Id"

It doesn't seem to take into consideration the && (dict.v == null || dict.v.Any(vo => vo.UserId == userId) part of the where clause.

Can you explain why this is happening and explain how I can achieve the result I am looking for?

Upvotes: 1

Views: 347

Answers (1)

Christos
Christos

Reputation: 53958

I think that this 

dict.v == null || dict.v.Any(vo => vo.UserId == userId

should change to this

dict.Votes == null || dict.v.Any(vo => vo.Votes.UserId == userId)

(as you already have done for dict.Story). To be honest with you, I wonder why the compiler didn't inform that the first is not correct. Where v is coming from? You make a projection of the grouped items and each projection has two properties, Story and Votes. Where v then comes from? It's not clear to me.

Regarding the LINQPad, note there that your projection has two properties...s and v...

UPDATE

By looking more carefully your query in LINQPad I noted that 

dict.v.Any(vo => vo.Votes.Id == 1)

That changes the things ! In the query you mention that there is a problem the corresponding predicate is:

dict.Votes.Any(vo => vo.UserId == userId)

So you compare the results from two different queries.

Upvotes: 1

Related Questions