SQL query not working on C# entity framework

Question

This SQL query works perfectly:

Select events.Name, results.Description, Events.Id, results.EventId, results.UserId
FROM Events
LEFT JOIN Results ON events.Id=Results.EventId
where results.UserId!='86' OR results.UserId IS NULL;

I'm trying the same thing for my API but no results return with this code:

public IActionResult AllEventsMatches()
    {
        var events = from Event in _dbContext.Events
                     join Result in _dbContext.Results on Event.Id equals Result.EventId
                     where Result.UserId != 86 || Result.UserId == null
                     select new
                     {
                         Name = Event.Name,
                         Description = Event.Description,
                         Id = Event.Id,
                         EventId = Result.EventId,
                         UserId = Result.UserId

                     };
        return Ok(events);
    }

Does this answer your question? [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Charlieface, Jul 25 '21 at 00:06

score 0 · Accepted Answer · answered Jul 24 '21 at 21:09

you have to use a left join too

 var events = 
 ( from Event in _dbContext.Events
    join Result in _dbContext.Results on Event.Id equals Result.EventId into ResultJ
    from Recult in ResultJ.DefaultIfEmpty()    
 where  Result.UserId == null || Result.UserId != 86 
                     select new
                     {
                         Name = Event.Name,
                         Description = Event.Description,
                         Id = Event.Id,
                         EventId = Result.EventId,
                         UserId = Result.UserId

                     }).ToList();

Thanks a lot, I figured I needed a left join, I just wasn't too sure how to code it. — esty12, Jul 25 '21 at 01:10

SQL query not working on C# entity framework

1 Answers1