How to Reuse Code for Selecting a Custom Dto Object for a Child Property with Ef Core

Can I reuse code for selecting a custom DTO object for a child property with EF Core?

There are several libraries which allows to do that in intuitive way:

LINQKit

[Expandable(nameof(AsDtoImpl))]
public static ModelDto AsDto(Model model)
{
   _asDtoImpl ??= AsDtoImpl() .Compile();
   return _asDtoImpl(model);
}

private static Func<Model, ModelDto> _asDtoImpl;

private static Expression<Func<Model, ModelDto>> AsDtoImpl =>
    model => new ModelDto
    { 
        ModelId = model.ModelId,
        ModelName = model.ModelName,
        ChildModels = model.ChildModels.AsQueryable().Select(ChildModel.AsDto).ToList(),
        AnotherChildModel = new AnotherChildModelDto
        {
            AnotherChildModelId = model.AnotherChildModelId
        }
    };

dbContext.Models
   .Where(m => SomeCriteria).Select(m => Model.AsDto(m))
   .AsExpandable()
   .ToList();

UPDATE: For EF Core, LINQKit can be confugred globally and AsExpanding() can be omitted.

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

NeinLinq - almost the same as in LINQKit

[InjectLambda]
public static ModelDto AsDto(Model model)
{
   _asDto ??= AsDto() .Compile();
   return _asDto(model);
}

private static Func<Model, ModelDto> _asDto;

private static Expression<Func<Model, ModelDto>> AsDto =>
    model => new ModelDto
    { 
        ModelId = model.ModelId,
        ModelName = model.ModelName,
        ChildModels = model.ChildModels.AsQueryable().Select(ChildModel.AsDto).ToList(),
        AnotherChildModel = new AnotherChildModelDto
        {
            AnotherChildModelId = model.AnotherChildModelId
        }
    };

dbContext.Models
   .Where(m => SomeCriteria).Select(m => Model.AsDto(m))
   .ToInjectable()
   .ToList();

UPDATE: For EF Core, NenLinq can be confugred globally and ToInjectable() can be omitted.

builder
    .UseSqlServer(connectionString)
    .WithLambdaInjection(); // enabling NeinLinq extension

DelegateDecompiler - less verbose than others

[Compute]
public static ModelDto AsDto(Model model)
  => new ModelDto
    { 
        ModelId = model.ModelId,
        ModelName = model.ModelName,
        ChildModels = model.ChildModels.AsQueryable().Select(ChildModel.AsDto).ToList(),
        AnotherChildModel = new AnotherChildModelDto
        {
            AnotherChildModelId = model.AnotherChildModelId
        }
    }

dbContext.Models
   .Where(m => SomeCriteria).Select(m => Model.AsDto(m))
   .Decompile()
   .ToList();

All libraries do the same thing - correct expression tree before EF Core processing. All of them need additional call to inject it's own IQueryProvider.

How can I fetch child entities as DTO in parent using reusable queries/Expression's with EF code first?

I think you are vastly over complicating it.

var results=_context.Parents
  .Include(p=>p.Children);

will return your EF objects. That's what you should be working with. If you want to convert the EF objects to DTO objects, save that for the final projection (I rarely use DTO objects as the POCO objects from EF are usually just fine).

var parents=results.Select(p=>new ParentDTO
  { id=p.id,name=p.name,children=p.Children.ToList()}
);

If you just want parent 1, then:

var parent=results.Where(p=>p.id==1);

if you want it as parentDTO:

var parent=results.Where(p=>p.id==1).Select(p=>new ParentDTO {
  { id=p.id,name=p.name,children=p.Children.ToList()}
);

You could use things like AsParentDto, but doesn't that imply that you are going to be copying the entire Parent properties? (In your simple case -- id and name). And if you are copying the entire property list, why are you creating a new object with all the same properties as the EF object instead of just reusing the EF object? The only time I'd use a Dto object is if I wanted to pass around a parent that only has some of the properties and I wanted to save myself from retrieving the additional properties from the database, in which case, I'd still use the original database query and just project into it as the final step.

var slimparent=results.Where(p=>p.id==1).Select(p=>new SlimParentDto {
  id=p.id });

Of course, if all I wanted was the parent id's then I'd just use an even simplier IQueryable<int> like:

var parentids=results.Where(p=>p.id==1).Select(p=>p.id);

--- TL;DR ---

Create a single method to retrieve your object will all the properties included. Everything then should use that as it's base, and attach further refinements in your controller to filter it down to just the data subset you want. Then as a last step, project the result into whatever DTO you want. Never use any methods to cause the IQueryable to be enumerated until you've done the projection. EF/LINQ will then generate an optimal query for you, just retrieving the properties required to fill your DTO.

Querying a deep child object with a certain property, but returning the root object which cascades down

As of EF Core 5.0, filtered includes are supported, so you could write your query as follows:

var schools = Context.Schools
    .Include(s => s.Classes)
    .ThenInclude(c => c.Students.Where(s => s.Gender == Gender.Male));

If your targeting previous versions of EF Core your existing query is fine, as you could perform a GroupBy in the client to project your structure:

var schoolsWithStudents = maleStudents
  .GroupBy(x => x.Classes
  .First().School);

Although this assumes that each student only belongs to classes relating to the same school.

---

Update after comment

If you only want to bring back schools that have Male students, you can add an extra Where after the filtered include:

var schools = Context.Schools
    .Include(s => s.Classes)
    .ThenInclude(c => c.Students.Where(s => s.Gender == Gender.Male))
    .Where(s => s.Classes.Any(c => c.Students.Any(st => st.Gender == Gender.Male)));

Linq selects all columns in SQL query when using projection

Your pl => pl.AsDto() statement is delegate. EF Core provider can not translate it to SQL.

Method must return Expression.

internal static class Extensions
{
    public static Expression<Func<PackingList, PackingListDto>> ToDto()
        => packingList => new PackingListDto
        {
            Id = packingList.Id,
            Name = packingList.Name
        };
}

Use it like this:

await dbQuery
    .Select(Extensions.ToDto())
    .AsNoTracking()
    .ToListAsync();

You must use the method exactly like this: Select(Extensions.ToDto()).

You must not use it like this: Select(x => Extensions.ToDto()).

The method Extensions.ToDto() returns Expression that can be translated by EF into SQL.

The lambda x => Extensions.ToDto() converts it into a delegate that EF doesn't know what to do with.

---

Other approach. Method returns IQueryable.

internal static class Extensions
{
    public static IQueryable<PackingListDto> ToDto(this IQueryable<PackingList> packingLists)
        => packingLists.Select(packingList => new PackingListDto
        {
            Id = packingList.Id,
            Name = packingList.Name
        });
}

Use it like this:

await dbQuery
    .ToDto()
    .AsNoTracking()
    .ToListAsync();

Proper way to compare request with entity

You can use LINQKit for injecting Expression Tree into filters. It is needed to configure DbContextOptions:

builder
    .UseSqlServer(connectionString) // or any other provider
    .WithExpressionExpanding();     // enabling LINQKit extension

Your classes should be extended with static function which returns Expression<Func<>> and current methods should have ExpandableAttribute

For example:

public class Gym
{
    [Expandable(nameof(IsAppreciateToRequestImpl))]
    public bool IsAppreciateToRequest(GetGymRequest other)
    {
        return (string.IsNullOrEmpty(other.Name) || Name == other.Name)
                && (string.IsNullOrEmpty(other.Location) || Location == other.Location) 
                && (other.SectionRequest == null || Sections.All(section => section.IsAppreciateToRequest(other.SectionRequest)));
    }

    private static Expression<Func<Gym, GetGymRequest, bool>> IsAppreciateToRequestImpl()
    {
        // first parameter is current object
        return (gym, other) => (string.IsNullOrEmpty(other.Name) || gym.Name == other.Name)
                && (string.IsNullOrEmpty(other.Location) || gym.Location == other.Location) 
                && (other.SectionRequest == null || gym.Sections.All(section => section.IsAppreciateToRequest(other.SectionRequest)));
    }
}

// the same technique as in Gym class
public class Section
{
    [Expandable(nameof(IsAppreciateToRequestImpl))]
    public bool IsAppreciateToRequest(GetSectionRequest other)
    {
        return // unknown;
    }

    private static Expression<Func<Section, GetSectionRequest, bool>> IsAppreciateToRequestImpl()
    {
        // first parameter is current object
        return (section, other) => // unknown;
    }
}

Then LINQKit will expand expressions returned by your static methods and will inject conditions into predicate.

The same approach can be used for projecting entity to DTO. Similar my answer here, which also shows known alternatives for LINQKit.

InvalidOperationException in where clause when attempting to create projection with Compile/Invoke

You can use LINQKit to make this query working. It needs just configuring DbContextOptions:

builder
    .UseSqlServer(connectionString) // or any other provider
    .WithExpressionExpanding();     // enabling LINQKit extension

Then you can inject your projection using LINQKit's Invoke method (but possible your query will be corrected also)

ds_channel = ESP_CHANNEL.Projection.Invoke(x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation)

Also you may find helpful this answer. It shows how to hide expression magic from end user.

---

Related Topics