Dynamic LINQ Select on query to DTO

Question

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

What's the generated sql if you do `Name = ignoreName ? null : p.Name` in your object initializer? I wonder if EF is that smart. — Jonesopolis, Jun 28 '22 at 19:40
It just generates a "CASE" as in: ``` CASE WHEN ([Join5].[TicketId] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN (39 IN (1, 19, 39)) THEN [Join5].[BusinessName] END AS [C2], ``` In this context, I have a list of fields (the 39 IN (1,19,39)) and it just translates it to SQL — Mathias Hemmer, Jun 28 '22 at 19:42
[Possible solution](https://stackoverflow.com/questions/54549506/select-only-specific-fields-with-linq-ef-core). It does seem like you'll have to build an expression. — Jonesopolis, Jun 28 '22 at 19:51
That could work, but the problem is that I'm converting from a simpler entity to a complex DTO. This means that its not a IQueryable that returns a T, instead is a IQuearyable that returns a TResult. I'm not fluent enought with reflections to modify or even understand whats going on. — Mathias Hemmer, Jun 29 '22 at 13:24

score 0 · Accepted Answer · answered Jun 28 '22 at 19:42

0

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.

answered Jun 28 '22 at 19:42

mustafa

400
1
9

Thats not good... I have about 12 properties that are dynamically mapped, so I would need about 12! combinations of selects? – Mathias Hemmer Jun 28 '22 at 19:47
without using ifs you cant do this from my perspective. Did you think about using raw sql queries as a string to send database with ef? it can be more configurable – mustafa Jun 28 '22 at 20:37
Can't be done using if's, since there are a lot of properties to be mapped... Using raw SQL might be an option, but since I'm relying on navegational properties to translate to joins, it will be a pain to use manual SQL – Mathias Hemmer Jun 29 '22 at 13:12

Dynamic LINQ Select on query to DTO

1 Answers1