0

this might be an old and on-going question for Entity Framework Core but I researched a lot and couldn't get an answer. We used to write store procedure in SQL so we will use operator like Inner Join and Left Join, recently we're rewriting everything in Entity Framework Core using C#. However, we found that no matter if we use linq-to-sql or Include operator, the sql execution behind the scene is always using the LEFT JOIN.

For example our store procedure will look like

SELECT *
FROM Users u
INNER JOIN UserRole ur on ur.Uid = u.Uid
LEFT JOIN UserGroup ug on ug.Uid = u.Uid

Suppose we already have a User, UserRole and UserGroup class defined in Entity Framework already, how could we to perform Inner Join with UserRole and Left Join with UserGroup at same time in Entity Framework Core here?

Things I've tried with using Include:

dbContext.Users.Inlcude(u => u.UserRoles).Include(u => u.UserGroups).ToList();

and using linq-to-sql:

from u in dbContext.Users 
join ur in dbContext.UserRole on u.Uid equals ur.Uid 
join ug in dbContext.UserGroup on ug.Uid = u.Uid
select new {u.Name, ur.UseRole, ug.Groupname}

Both above two approach is using a LEFT JOIN

So far what we can do is using DbQuery with FromSql by plain sql, but we don't think that's a property way of using Entity Framework. So can it be done with traditional Include and linq-to-sql ways?

Also I looked at answer about using WithRequired and WithOptional in table mapping relationship with FluentApi, but that's not what I want, because some of my business logic needs INNER JOIN between User and UserRole while other scenario may need LEFT JOIN between User and UserRole, so we need to write query or code that EF understand to differentiate the correct join we want.

Drex
  • 3,346
  • 9
  • 33
  • 58
  • What have you tried so far? – Ben Jun 13 '19 at 03:17
  • 1
    A useful comment [If the navigation property's key is nullable and the entity mapping uses WithOptional, then the query EF uses will use a LEFT JOIN. If navigation property's key is not nullable or the entity mapping uses WithRequired then it uses INNER JOIN](https://stackoverflow.com/a/4299667/2557128). Also see [this answer](https://stackoverflow.com/a/35349312/2557128). – NetMage Jun 13 '19 at 21:41
  • Also note EF Core 2.2 has some fixes for `SelectMany` and proper join types. – NetMage Jun 13 '19 at 21:45
  • @NetMage, Thank you for your information, I did check about that answer already, however the "WithRequired" and "WithOptional" are configuration between tables mapping in FluentApi(One-time), not what I could use in my specific query logic. For example, some of my business logic may need `User INNER JOIN UserRole` and some other logic may need `User LEFT JOIN UserRole`. – Drex Jun 14 '19 at 14:52
  • Hi @Ben, please see my edited approaches that I tried with using `Include` and `linq-to-sql` so far, the ef core doesn't know that it should use `INNER JOIN` with one property and `LEFT JOIN` with another property..Thank you! – Drex Jun 14 '19 at 15:11

0 Answers0