Dynamic LINQ expression for Select with child collection (Entity Framework)

Question

I would like to ask you about a way to dynamically create a LINQ Select expression with a nested child collection. Fields in the selected child collection can be static, however I would like to dynamically pass a list of fields in the current entity, and also fields in other entities, referenced by navigation properties. Here's the static version of the query, similar to the one which I use in my code, in many places, and which I would like to create dynamically:

var listItems = _efDbContext.Blogs.Select(x => new {      
    ID = x.ID,
    Name = x.Name, //field from the current entity     
    AuthorName = x.Author.Name, //field referenced by navigation property
    ...<other fields from current or referenced entities(like AuthorName above), passed dynamically>
    Posts = x.Posts.Select(y => new { //this select is 'static', is the same for other queries 
       Id = x.Id,
       Name = x.Name
    })
});

I tried to figure out something from the answers in these two posts bewlo but I did not succeed. Answer by Ivan Stoev is really cool but it does not support referenced properties and I also have this static nested child collection select in my example above

Dynamically build select list from linq to entities query

EF Linq- Dynamic Lambda expression trees

I have also tried to accomplish this using libraries like Dynamic.Linq.Core or Automapper but it seems that the former does not support select with child collections and the latter needs a DTO class and I dont want to create hundred of DTOs for each combination of fields in the select expression.

I also thought that this whole approach might be overcomplicated and maybe I should use parametrized SQL query instead, and that's definitely a possible option, but I think that I could reuse this dynamic LINQ expression in different variations, in other places in my project, and it would be helpful in a long run :)

(I'm using EF Core 3.1)

Answer 1

My solution from the linked post handles quite simplified scenario. It can be extended for handling nested properties and method invocations, but then main problem will it is that it result isn't truly dynamic. It requires a predefined type and selectively selects/populates members of that type. Which makes it similar to AutoMapper explicit expansion feature, thus it might be better to use the later since it offers flexible automatic/manual mapping options.

For truly dynamic output you need a library which generates dynamic classes at runtime. Dynamic LINQ is one of them, and apart from its unusual expression language actually can be used for your scenario (at least the one from https://dynamic-linq.net/, because there are many flavors of that library and all they support/do not support some things).

Following is an example using System.Linq.Dynamic.Core package (or EF Core version of it Microsoft.EntityFrameworkCore.DynamicLinq in case you need async queryable support):

var selectList = new List<string>();
// Dynamic part
selectList.Add("ID");
selectList.Add("Name");
selectList.Add("Author.Name as AuthorName");
// Static part. But the nested Select could be built dynamically as well
selectList.Add("Posts.Select(new (Id, Name)) as Posts");

var listItems = _efDbContext.Blogs
    .Select($"new ({string.Join(", ", selectList)})")
    .ToDynamicList();

Answer 2

If you're okay with adding an additional dependency, I strongly suggest relying on Automapper's ProjectTo(), which effectively replaces your Select with a method that allows for reusable mappings, including nested ones.

A simple example of this would be:

public class PostDto
{
    public string Title { get; set; }
}

public class BlogDto
{
    public string Name { get; set; }
    public PostDto[] Posts { get; set; }
}

public class AuthorDto
{
    public string Name { get; set; }
    public PostDto MostRecentPost { get; set; }
}

var configuration = new MapperConfiguration(cfg => {
    cfg.CreateMap<Post, PostDto>();
    cfg.CreateMap<Blog, BlogDto>();
    cfg.CreateMap<Author, AuthorDto>()
       .ForMember(
           dto => dto.MostRecentPost, 
           conf => conf.MapFrom(
               author => author.Posts
                               .OrderByDescending(x => x.Date)
                               .FirstOrDefault()));
});

public List<OrderLineDTO> GetLinesForOrder(int orderId)
{
    using (var context = new orderEntities())
    {
        var blogsWithPosts = context
                               .Blogs
                               .ProjectTo<BlogDto>(configuration)
                               .ToList();

        var authorsWithPost = context
                               .Authors
                               .ProjectTo<AuthorDto>(configuration)
                               .ToList();
    }
}

Notice how I didn't even need to mention Post or PostDto in my actual LINQ query. This is because ProjectTo uses the mapping configuration to figure out which fields it needs to fill in, and the configuration contains the information on which entity fields are needed for each DTO field.

I assumed for the most part that the names of all properties between entity and DTO matched, because then you don't need to manually map them. This was done to keep the example simple, but also included an example of how you can manually map AuthorDto.MostRecentPost to an actual subquery.
For more intricate mappings, I suggest looking at Automapper's documentation. That is more than I can explain in a single answer here.

Effectively, ProjectTo generates the appropriate Select statement for you.

This also means that if I decide to change PostDto and its mapping, that this change is automatically reflected in the two LINQ queries without needing to change the LINQ queries themselves.