How to convert this SQL statement with left join, grouping to EF Core Linq?

Question

I have 2 tables, Events and EventTracking.

Event

id 
Name
Order

EventTracking

Id
EventId
Details
CreatedDate

I am trying to recreate basically this query below in EF Core.

SELECT 
    events.NAME, 
    COUNT(eventtrackings.details) AS Count 
FROM   
    events 
LEFT JOIN 
    eventtrackings ON events.id = eventtrackings.eventid 
WHERE
    eventtrackings.CreatedDate > '4/22/2019'
GROUP BY 
    NAME, eventtrackings.CreatedDate
ORDER BY
    events.Order

I want everything in Events to be shown, but the filtering is on the tracking part.

I'm not sure what's the problem. Wouldn't something simple as this work? `_context.Events.Select(evt => new { Name = evt.Name, Count = evt.EventTrackings.Count() }).ToList()` — Camilo Terevinto, Apr 23 '19 at 22:56
This is just a left join, did you even try to figure out how to do that in Linq? For example https://stackoverflow.com/questions/39919230/asp-net-core-entityframework-core-left-outer-join-in-linq — DavidG, Apr 23 '19 at 22:57
hmm, well one problem that might not be shown in my more simple example is that how to do the Where Clause? I want to filter down on properties in the EventTracking such as CreatedDate — chobo2, Apr 23 '19 at 22:58
@DavidG - Is it possible ot do a left join using the linq method way? Or I got to use the other way? — chobo2, Apr 23 '19 at 23:01
You know what might help? Google! Takes about 4 seconds...https://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods — DavidG, Apr 23 '19 at 23:04
@DavidG With navigation properties one don't need left, right or whatever explicit joins. And EF Core works/translates better implicit joins via navigation properties. People should stop thinking in SQL - see Camilo's comment containing simple answer. — Ivan Stoev, Apr 23 '19 at 23:26
@IvanStoev Indeed, but with the additional filter required on the navigation property, that would be awkward to do without a join. — DavidG, Apr 23 '19 at 23:28
Btw, once you add `where` with condition including the "right" side of the "left outer join" (other than `right IS NULL`), it effectively becomes "inner join". — Ivan Stoev, Apr 23 '19 at 23:29
@IvanStoev - Well the problem I am having is with Camilo is grouping it by Events.Name, Once I do a select, where and then try to the join. I don't have access to Events.Name — chobo2, Apr 23 '19 at 23:31
@CamiloTerevinto - I updated my code, the problem is I need to do some where clauses on fields int he EventTracking object so I am not sure how to do that then go back and group. — chobo2, Apr 24 '19 at 00:02
So the filter should be done on the count? How about `Count = evt.EventTrackings.Count(et => et.CreatedDate > new DateTime(2019, 4, 22))`? — Camilo Terevinto, Apr 24 '19 at 00:04
@CamiloTerevinto - Well that might work but I still need to also groupby CreatedDate — chobo2, Apr 24 '19 at 16:45
@chobo2 Are you saying that my last comment didn't work or what happened when you tried it? It should work as-is — Camilo Terevinto, Apr 24 '19 at 17:05
It did not work as I can't access the CreatedDate from EventTracking to groupby. — chobo2, Apr 24 '19 at 17:36
How about something like [this](https://dotnetfiddle.net/9GT6mh)? It doesn't compile there but you can copy-paste it to run it — Camilo Terevinto, Apr 24 '19 at 21:09
Here is your answer: https://stackoverflow.com/questions/22995454/linq-fluent-api-version-of-left-join-query — Dawid Wekwejt, Sep 15 '21 at 20:05

How to convert this SQL statement with left join, grouping to EF Core Linq?

0 Answers0