EF Core how select entity with many-to-many relationship

Question

I ave table structure like this

users
user_profiles
profiles

description:

user has many user profiles, user_profile table join user and profile tables together.(there is a many to many relationship between user and the profile table)

user>one-to-many>user_profiles>one-to-one>profiles

user>many user_profiles> one profile

Problem:

How can i select user with profile by using linq.

sample:

var user=cbContext.user.include("user_profiles").include("profiles").Where(predicate).FirstOrDefault();

score 14 · Answer 1 · edited Oct 01 '18 at 06:55

14

Found the answer

dbContext.Users
  .Include(user => user.UserProfiles)
  .ThenInclude(userProfiles => userProfiles.Profile) 
  .Where(predicate)
  .FirstOrDefault();

edited Oct 01 '18 at 06:55

krzyski

318
2
15

answered Oct 01 '18 at 05:05

Eranga Gamagedara

536
1
3
10

3

plus for using meaningful names in lambdas! – krzyski Oct 01 '18 at 07:04
2

But what does 'predicate' look like to filter by "Profile"? – MDave Apr 29 '20 at 18:32

score 8 · Answer 2 · answered Oct 01 '18 at 07:35

If you have full entity-framework, then the many-to-many is designed similar to:

class User
{
     public int Id {get; set;}

     // every User has zero or more Profiles (many-to-many)
     public virtual ICollection<Profile> Profiles {get; set;}

     ...
}
class Profile
{
     public int Id {get; set;}

     // every Profile belongs to zero or more Users (many-to-many)
     public virtual ICollection<User> Userss {get; set;}

     ...
}

If you have your classes designed like this and you want "users that ... with their profiles" you can use the collections and Select the properties you plan to use:

using (var dbContext = new MyDbContext(...))
{
    var requestedUsers = dbContext.Users
        .Where(user => ...)                      // only if you don't want all Users
        .Select(user => new
        {    // Select only the properties you plan to use:
             Id = user.Id,
             Name = user.Name,
             ...
             Profiles = user.Profiles
                 .Where(profile => ...)         // only if you don't want all profiles
                 .Select(profile => new
                 {
                      Name = profile.Name,
                      ...
                 })
                 .ToList(),
        })

One of the slower parts of a database query is the transport of the selected data from the Database Management System to your process. Hence it is wise to limit the data you are transferring to the data that you actually plan to use.

Include will select all properties of the included object, inclusive primary and foreign keys. Include a Collection will select the complete collection, even if you only plan to use a few.

Advise: only use Include if you plan to change the fetched data. Using Select is faster. Select only the properties you actually plan to use

Use (Group)Join if you can't use the ICollection

I understood from some that you can't use the virtual ICollections when you use EF-core. In that case you'll have to perform a GroupJoin yourself

dbContext.Users
    .Where(user => ...)
    .GroupJoin(dbContext.UserProfiles,         // GroupJoin the users with the UserProfiles
        user => user.Id                        // from every user take the Id
        userProfile => userProfile.UserId,     // from every userProfile take the UserId
        (user, userProfiles) =>  new           // when thay match,
        {                                      // take the user and its matching UserProfiles
            UserId = user.Id,                  // again: select only properties you plan to use
            UserName = user.Name,
            ...

            // for the Profiles, do a new Join with the Profiles
            Profiles = userProfiles.Join(dbContext.Profiles, // join with Profiles
               userProfile => userProfile => profileId       // from the userProfile take profileId
               profile => profile.Id,                        // from the Profile take the Id
               (userProfile, profile) => new                 // when they match, make an object
               {   // again: use only properties you plan to use
                   ProfileId = profile.Id,
                   ProfileName = profile.Name,
                   ...
               })
               .ToList(),
        });

Careful: You won't get Users without any Profiles!
It is an Inner join.

If you also want Users without profiles, use a Left-Outer-GroupJoin as described here on Stackoverflow Scroll down for the highest ranked answer, which is way better than the selected answer

The advise is particularly interesting "... Using Select is faster ..." . Do you have any source for this claim ? — Pac0, Oct 01 '18 at 19:22
Most of the times you don't need all column values. If I use SQL Service provider to investigate the SQL statements that the `IQueryable.Provider` creates, then I see that both `Queryable.Select` and `DbExtensions.Include` create a SQL Select. Linq Select selects only the selected columns, Include selects all columns, inclusive foreign keys of which you usually already know the value "Give me the Teachers with their Students" Every Student.TeacherId has the same value as Teacher.Id, so no use to select this same value of Student.TeacherId for every Student. — Harald Coppoolse, Oct 02 '18 at 06:25

Pac0 · Answer 3 · 2018-10-01T11:25:35.457

In addition to your own answer with the lambdas and the use of ThenInclude , which is my preferred version for simple queries with n to n relationships, you can also use strings to specify your inclusions.

You just need to write the "path" of properties separated with dots . like this :

dbContext.Users
  .Include("UserProfiles.Profile")
  .Where(predicate)
  .FirstOrDefault();

It works for 1 to 1, 1 to many and many to many relationships the same.

It's useful when you have deep inclusions of entities (but you lose the compilation-time checking)

EF Core how select entity with many-to-many relationship

3 Answers3