Linq query to join three tables and return a object along with a list of another object in Entity Fr-CodePudding

I have three tables, Organization, Department, and OrganizationDepartments. here is the relationship between them.

Now I would like to join these three tables and create another object for a DTO class. This DTO object has some properties and a list of other DTOs. Here is the DTO Class.

Organization DTO:

public class OrganizationDto
{   
    public string Id { get; set; }
    public string OrganizationName { get; set; }
    public string Logo { get; set; }    
    public bool? IsActive { get; set; }
    public IList<OrganizationDepartmentDto> OrganizationDepartments { get; set; }
}

OrganizationDepartment DTO:

public class OrganizationDepartmentDto
{       
    public string OrganizationId { get; set; }  
    public string OrganizationName { get; set; }    
    public string DepartmentId { get; set; }    
    public string DepartmentName { get; set; }
}

Now I would like to write a LINQ query to get a Organization object along with all the departments related to that organization. The query is imcomplete because I don't know how can I get all the department information as list in a single query. The code is below:

var organizationInfo = (from org in _dbContext.Organizations
                     join orgDept in _dbContext.OrganizationDepartments on org.Id equals orgDept.OrganizationId
                     join dept in _dbContext.Departments on orgDept.DepartmentId equals dept.Id
                     where org.Id.ToUpper() == id.ToUpper()
                     orderby org.CreatedOn ascending
                     select new OrganizationDto
                     {
                         Id = org.Id,
                         OrganizationName = org.OrganizationName,
                         Logo = org.Logo,                                 
                         IsActive = org.IsActive,
                         OrganizationDepartments = //TODO:.. 
                     }
                );

Can anyone help me to get the department lists of that organization's object (see the TODO:)?

CodePudding user response：

If your entities are mapped correctly, and the relationships are correctly configured.

you can use .Include("OrganizationDepartment") and .ThenInclude("Department")to ensure relations are included into the generated Query.

If you insist on using Query Syntax. e.g from org in context.Organization

you can write out the query like this.

var q = (from org in _dbContext.Organizations
         where org.Id.ToUpper() == id.ToUpper()                     
         orderby org.CreatedOn ascending
                     select new OrganizationDto
                     {
                       Id = org.Id,
                       OrganizationName = org.OrganizationName,
                       Logo = org.Logo,                                 
                       IsActive = org.IsActive,
                       OrganizationDepartments = org.OrganizationDepartments.ToList()
                     }

Depending on your usecase. Sometimes you are not interested in actually showing the "many to many" table outside of the scope of your database.

so it might make more sense to actually flatten the Dto.

that query would look like

var q = (from org in _dbContext.Organizations
         where org.Id.ToUpper() == id.ToUpper()                     
         orderby org.CreatedOn ascending
                     select new OrganizationDto
                     {
                       Id = org.Id,
                       OrganizationName = org.OrganizationName,
                       Logo = org.Logo,                                 
                       IsActive = org.IsActive,
                       Departments= org.OrganizationDepartments.Select(t => t.Departments).ToList()
                     }