Include unrelated table in Linq to entity query

Question

I have the following simplified setup:

Public User
{

  //Primary key
  public int Id {get;set;}

  public string Name {get; set;}
}

Public UserInfo
{

  //Primary key
  public int Id {get;set;}

  //Foreign key to user table
  public int userKey {get; set;}
}

The relationship between the tables is one user to Many userInfo

I am trying to select from the user table and include the userInfo table.

I cannot do this:

var users = Context.user.Include(u => u.userInfos);

as there is no reference to the UserInfo table from the user table.

I can do this:

context.userInfo.include(x => x.user)

but if there are no corresponding entries in the userInfo table, this will not return any results, which is not what I want. Also, this will return one row for each userInfo, whereas I want one row for user, with a list of userInfo as a parameter.

Similarly I could join the tables like this:

var users = from us in Context.user
    join inf in Context.userInfo
    on us.Id equals inf.userKey
    select new //etc

But this will also return one row per userInfo entry, the same problem as above.

To summarise, is there a way of including this table to produce a result in the same way as the include function does.

I know I could adjust my setup to all me to include this, but that is not what I am asking here.

I suspect this cannot be done, but from all my googling so far I have not been able to find a definitive answer....

I guess `User` has a property `UserInfos` or something like that? (BTW, do yourself a great favor and adhere to naming conventions, like class and property names starting with capitals). — Gert Arnold, Jul 06 '17 at 13:29
so you are looking for left join in linq? here is a solution https://stackoverflow.com/questions/3404975/left-outer-join-in-linq — Karthik Ganesan, Jul 06 '17 at 15:35

score 1 · Accepted Answer · answered Jul 06 '17 at 21:15

I want one row for user, with a list of userInfo as a parameter

I assume you mean a list of userInfo as a property. My understanding of what you ask it that you're just looking for:

var users = from us in Context.user
    join inf in Context.userInfo
        on us.Id equals inf.userKey into infg
    select new
    {
        User = us,
        UserInfos = infg
    };

join ... into amounts to a GroupJoin, i.e. a user entity joined with a group of userinfos.

Better still is to use a navigation property user.userInfos (reluctantly following your naming convention):

var users = Context.user.Include(u => u.userInfos);

I have edited the question to explain why I cannot use your suggested include method, but the first solution you propose works so thank you for that!! — Alex, Jul 07 '17 at 09:21

Include unrelated table in Linq to entity query

1 Answers1