Fetching only related properties from related data in Entity Framework Core

Question

Using the Include() method from LINQ, I can easily fetch all attributes from the related Users table.

public IEnumerable<Group> GetAllUserGroups()
{
    IEnumerable<Group> groups = _dbContext.Group
                                          .Include(g => g.Users)
                                          .ToList<Group>();

    return groups;
}

But, I'm not sure how to select only the first name and the last name from the users table. I figured, it will be easiest to create a DTO, but then I am not really sure how to populate the DTO since Users is a list.

Essentially, Group and User have a one-to-many relationship.

User class:

public class User
{
    [Key]
    public int UserID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }

    public int GroupID { get; set; }

    [JsonIgnore]
    public Group Group { get; set; }
}

Group class:

public class Group
{
    [Key]
    public int GroupID { get; set; }
    public string GroupName { get; set; }
    public List<User> Users { get; set; }
}

Thanks for suggestions.

Answer 1

For the following way, you can select the User with only FirstName and LastName contains value and other properties are null/empty/0:

IEnumerable<Group> groups = _dbContext.Group
                                .Select(a => new Group { 
                                    GroupID= a.GroupID,
                                    GroupName= a.GroupName,
                                    Users= a.Users.Select(b=>new User() { FirstName= b.FirstName,LastName= b.LastName}).ToList(),
                                }).ToList();

If you only want to display FirstName and LastName property value without other properties, you can use the anonymous class:

var groups = _dbContext.Group
                    .Select(a => new  { 
                        a.GroupID,a.GroupName,
                        Users= a.Users.Select(b=>new { b.FirstName,b.LastName}).ToList(),
                    }).ToList();

If you want to use the DTO, you need create the GroupDTO and UserDTO like below:

var groups = _dbContext.Group
                    .Select(a => new GroupDTO { 
                        GroupName= a.GroupName,
                        GroupID=  a.GroupID,    
                        Users=a.Users.Select(b=>new UserDTO() { FirstName=b.FirstName,LastName=b.LastName}).ToList()
                    }).ToList();