Dynamic LINQ Select on query to DTO-CodePudding

Suppose I have an Entity class:

public class Person : Entity
{
    public int TenantId { get; set; }
    public string Name { get; set; }
    public string Role { get; set; }
    ...many more properties

}

And a near identical DTO class:

public class PersonDTO
{
    public string Name { get; set; }
    public string Role { get; set; }
    ...many more properties
}

And I have a EF LINQ query to select some properties from my DTO. The thing is I only know what properties are going to be requested in runtime, and I want to minimize the load on my DB.

If, for instance, I have this query:

DbContext.PersonSet
         .Where(p => p.TenantId == tenantId)
         .Select(p => new PersonDTO
                          {
                              Name = p.Name,
                              Role = p.Role
                          })

The output SQL will look something similar to:

SELECT 
    [Project1].[Name] as [Name]
    [Project1].[Role] as [Name]
FROM 
    (SELECT
         [Project1].[Name], [Project1].[Role]
     FROM
         [Database].[Person] as [Project1])

What I would like to be able to do is dynamically add properties to the select statement, modifying the result query to include those properties. If not specified, the query should ignore the values, and let de .NET environment initialize the properties with their default values.

As the pseudo code shows:

var ignoreName = true;

DbContext.PersonSet
         .Where(p => p.TenantId == tenantId)
         .Select(p => new PersonDTO
                      {
                          Name = ignoreName ? Ignore this property in que query building : p.Name,
                          Role = p.Role
                      })

Generating a SQL similar to:

SELECT 
    [Project1].[Role] as [Name]
FROM
    (SELECT
         [Project1].[Role]
     FROM
         [Database].[Person] as [Project1])

EDIT:

Some of my properties generate JOINS to get related data from another table, that's why I want to conditionally create them.

If the user doesn't ask to see the City Name from the Person, there is no need to generate a " LEFT JOIN Database.City ON Person.CityId = City.Id". EF generates those automatically for navigation properties

CodePudding user response：

You cant do this in the .Select() if i understand you right.

i think the best you can is this:

var filteredPersonSet = DbContext.PersonSet.Where(p => p.TenantId == tenantId);

if(ignoreName)
    //select without name
    var result = filteredPersonSet.Select(...)
else
    ///select with name
    ...

and also any other joins can be done with similar approach.

Edit: i used var result in if but the code may be improved with carrying outside of if and using in the else.