CODEWITHSUNDEEP
c#asp.netsqllinqentity-framework
user2719100
user2719100

Reputation: 1734

Entity Framework and LINQ left join and include on same table

I have the following class structure for my Users and the permissions they're in for the different companies they may be associated to:

public class User
{
    public Guid Id { get; set; }
    public List<Permission> Permissions { get; set; }
    public Company DefaultCompany { get; set; }
}

public class Permission
{
    public User User { get; set; }
    public Company Company { get; set; }
    public int PermissionLevel { get; set; }
}

public class Company
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

This results in three SQL tables. There is a FK between Permission.User_Id > User.Id and Permission.Company_Id > Company.Id. There is no explicit relationship (ie. FK) between User.DefaultCompany and the Company table. This is on purpose due to a legacy choice in our database schema.

I also have a database repository method that grabs a user by it's Id, and includes the full Company record:

public User GetById(Guid Id)
{
    return (from r in this.Context.Users.Include("Permissions.Company")
        where r.Id == Id
        select r)
        .SingleOrDefault();
}

This works fine, but it doesn't set the DefaultCompany property. So I tried setting that by changing this method to the following. It's worth pointing out that the Company record that represents the DefaultCompany shares the same ID value as the User.

public User GetById(Guid Id)
{
    return (from r in this.Context.Users.Include("Permissions.Company")
            where r.Id == Id
            join c in this.Context.Companies on r.Id equals c.Id into companies
            from company in companies.DefaultIfEmpty()
            select new { User = r, Company = company })
            .ToList()
            .Select(p => { p.User.DefaultCompany = p.Company; return p.User; })
            .SingleOrDefault();
}

And this does, in fact, set the DefaultCompany but it has the side effect of not selecting the Permissions list. I can do this all as two separate operations, as in the following code, but I'd rather not hit the database twice if I don't have to.

public User GetById(Guid Id)
{
    var u = (from r in this.Context.Users.Include("Permissions.Company")
            where r.Id == Id
            select r)
            .SingleOrDefault();

    u.DefaultCompany = (from r in this.Context.Companies where r.Id == u.Id select r).SingleOrDefault();

    return u;
}

Is there another way to accomplish this?

Edit: explaining resulting SQL data model and additional example.

Upvotes: 0

Views: 6041

Answers (1)

JotaBe
JotaBe

Reputation: 39014

There are two possible solutions for this problem.

The cleanest is to use the Fluent API to indicate the model that there is a 1 to 1 relation between User and Company.

Override the OnModelCreating(DbModelBuilder modelBuilder) method of your context.

Inside it, configure the 1 ro 1 relation like this:

 modelBuilder.Entity<User>()
             .HasOptional(u => u.DefaultCompany)
             .WithRequired();

NOTE: with this configuration there is a relationship of 1 user to 0 or 1 default companies (note the Optional in HasOptional). And the default company must have a User on the other side. When a 1 to 1 (or 1 to 0..1) relation is configured, EF will automatically use the PK of the related tables to create the relation between them. You can fine tune the relation using other Fluent API functions

After doing so, you can include the DefaultCompany using Include():

User user = ctx.Users
               .Include(u => u.DefaultCompany)
               .SingleOrDefault(u => u.Id == userId);

The other, more ugly solution, is to use your second query and include the missing permissions in the projection, to force EF to recover them from the DB.

// ...
select new { User = r, Company = company, Companies = r.Permissions } 
// ...

Upvotes: 1

Related Questions