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

Question

I ave table structure like this

1. users
2. user_profiles
3. 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();

Answer 1

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)

Answer 2

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

Answer 3

Found the answer

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