CODEWITHSUNDEEP
c#linqincludelinq-to-entitiesentity
Alex
Alex

Reputation: 3968

Include unrelated table in Linq to entity query

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....

Upvotes: 1

Views: 1840

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109185

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);

Upvotes: 1

Related Questions