Property Translation for Where/Order/Grouping for IQueryable

[JohnGalt1717]

This is related to: #287

Is your feature request related to a problem? Please describe.
When using a DTO there is often the use case of a grid or other end user facility to define filters, ordering and grouping. When receiving this back from the client it will reference the fields in the DTO not the original data source, which makes linq problematic because you first have to project into a select then filter/group/order which causes a major degradation in performance and lots of document dbs won't even let you do this (i.e. Raven, Cosmos etc.)

Thus you have to be able to translate a filter on a requested field to the source field and not only do this functionally but as an expression tree.

Describe the solution you'd like
LIke Automapper can sort of do, I'd like to see mapperly not only generate expression trees for select, but also to take a where/order/group by clause and map it back to the original source.

I.e. I should be able to do something like queryable.AsMapperly().Where({some predicate on the dto}).OrderBy({some predicate on the dto}).GroupBy({some predicate on the dto}).ProjectTo()

And it should do it all automatically with all of the information that it already has. Basically the .Where (etc.) would be an extension method that would recognize the DTO type and translate the lambda to the source from the dto automatically.

Describe alternatives you've considered
Manually doing this for every single field. Yuk.
Automapper with "AsDataSource".

Additional context
This shows up with basically all grid controls where you do server side for optimizations like Telerik Kendo, DevExpress etc. The grid generates filters, groups and sorting and you need to return the exact dataset back to the client based on that information and you don't have the original field names so if it doesn't match exactly, then it blows up.

[latonz]

Thanks for opening this issue. I don't think a source generator can support this. How could the generated source for this look like?

[AdCodicem]

Hello,
I was searching for a source generated mapper and Mapperly seems to be a very good option! However, like @JohnGalt1717, I need to be able to generate projection.
I use Entity Framework Core to query my data and I like the results to be the DTOs directly.
I am currently using Mapster to generate the mappers files (it's not completely a source generator as you need to execute the CLI to generate them).

Basically, Mapster do things like that :

public class Car 
{
    public int Id { get; set; }

    public Person Owner { get; set; }
    
    public DateTime PurchaseDate { get; set; }
}

public class Person
{
    public int Id { get; set; }

    public string FullName { get; set; }
    
    public DateTime BirthDate { get; set; }
}

public class CarDto
{
    public int Id { get; set; }
    
    public DateTime PurchaseDate { get; set; }

    public int OwnerId { get; set; }

    public string OwnerFullName { get; set; }
    
    public DateTime OwnerBirthDate { get; set; }
}

List<CarDto> results =
    _dbContext // Consider an EF Core DB context
        .Set<Car>()
        .Where(car => car.PurchaseDate > minPurchaseDate)
        .Where(car => car.Owner.BirthDate >= minBirthDate) // You can add any Where clause on Car before the projection
        .Select(CarMapper.ProjectToCarDto) // The mapper generate a static method for projection
        .Where(dto => dto.OwnerBirthDate < maxPurchaseDate) // You can add any Where clause on CarDto because it's just a projection
        .ToList();
        
// Mapster generates a Mapper like this
public partial class CarMapper
{
    public static Expression<Func<Car, CarDto>> ProjectToDto => car => new CarDto()
    {
        Id = car.Id,
        PurchaseDate = car.PurchaseDate,
        OwnerId = car.Owner.Id,
        OwnerFullName = car.Owner.FullName,
        OwnerBirthDate = car.Owner.BirthDate
    };

    // My suggestion would just be to make the generated mapping method an extension method for readability...
    public static IQueryable<CarDto> ProjectToDto(this IQueryable<Car> query)
    {
        return query.Select(ProjectToDto);
    }
}

// ... so it can be used like that :
List<CarDto> results =
    _dbContext
        .Set<Car>()
        .ProjectToDto()
        .ToList();

I hope this help 😄 !

[CommonGuy]

IQueryable projections are supported in the upcoming release, see https://github.com/riok/mapperly/blob/main/docs/docs/02-configuration/11-queryable-projections.mdx.

You can try it out in the 2.8.0-next.1 pre-release nuget package. Does this help you?

[AdCodicem]

Great news! That seems to be exactly what I'm looking for!

[jhancock4d]

So in the example the where is before the ProjectTo. Will this properly map the where from the project to?
ie: Query.ProjectTo().Where().ToListAsync()

Should take the where that is passed and map it to the fields of the root query.

This is super important so that you don't get something like this in SQL:

Select * from (select ..mappings.. from table) where ...based on mappings...

Because this is really slow.

You want it to come out with Select ...mappings.. from table where ...mapped fields with conditions...

If this works, my suggestion is to update the docs so that it's obvious that this is what it's doing.

If it doesn't, then this really isn't done yet.

[jhancock-taxa]

I'd just like to add to this that I don't think this was actually done. The o/p was requesting AsDataSource() type functionality like AutoMapper provides.

This allows things like oData to avoid the Select before Where problem, using an expression visitor to reverse the map back to the original properties for the where before the projection does (as you've done with your IQueryable implementation)

Automapper allows this:

dbSetCars.AsDataSource<CarsDto>().where({Expression<Func<CarDto>> where}).ProjectTo<CarDto>()

Right now with mapperly you'd have to do something like this:

dbSetCars.ProjectTo<CarDto>().Where({Expression<Func<CarDto>> where})

The resulting SQL is bad because it will cause index misses and can create queries that have to create table scans etc. and you won't know it. The automapper way is VASTLY more performant, and will error if you do things that can't be translated directly back to SQL (i.e. concatenation etc) without a table scan.

What the o/p and myself are asking for is Mapperly to be able to create the Expression Vistor that maps CarDto expressions back to Car where, groupby and orderby expressions without having to project first, thus avoiding this issue, thus copying the AsDataSource<>() function in AutoMapper.

[latonz]

Ohhh I understand the original issue now, thanks for explaining again and sorry for not getting it in the first place! 🙈
This needs a lot of effort in Mapperly and is not an easy task to implement. We may need a concept on how the generated code should exactly look like, how the source generation should work and how it integrates with the existing concepts.
An idea could be to use a similar AsDataSource / ProjectTo methods combination as AutoMapper and intercept any IQueryable method calls after AsDataSource (see also Roslyn Interceptors).

[jhancock-taxa]

So what this basically is, is the existing IQueryable mapping function but from DTO to Data Object.

It seems to me, that you could do an Expression<Func<TEntity, bool>>(Expression<Func<TDto, bool>) tool which would do the same and using a source generator would mean that what you would do by hand, would work directly. It would just use an ExpressionVistor to visit the inputted expression, and then map one field to another while maintaining the rest (which is basically what AutoMapper is doing). As a result you could have a "MapWhere" and "MapOrderBy" in a pretty straightforward manner. (And also MapGroupBy, and Expand)

This is super useful for both OData and GraphQL. (and most people don't know just how bad it is to do select before where, so super differentiator when you tell them)

[latonz]

Your proposal is to generate expression visitors for MapWhere, MapGroupBy, ... which translate the expression from the dto member access to the entity member access, correct?
IMO I'd prefer the proposal with AsDataSource / ProjectTo for the following reasons:

• The method body of all intercepted method calls between AsDataSource and ProjectTo would be pretty easy to read (would read the same as the method body the dev has written, just with all DTO member access replaced by entity member access) and debug. In contrast the generated ExpressionVisitor is hard to read, understand and debug.
• I think the complexity of the implementation in Mapperly will be about the same for both approaches.

Do you know why EF Core doesn't do this optimization by itself? AFAIK it should have all the necessairy information by traversing through the expression trees...

[jhancock-taxa]

It does a Select X,Y,Z from (SELECT A,B,C FROM dataTable) WHERE X = ...

Which is the select before where problem. It doesn't remap it in any case that I've tried. There might be simplistic cases where this isn't true, but I've never seen it. This also happens with RavenDb, Mongo and others. (and For no-sql, they simply don't let you do SELECT before WHERE so it fails and you must have something like this, otherwise it won't work at all)

As long as I can take an Expression<Func<TDto, true>> and it becomes Expression<Func<TData, true>> (as an example for where) then good to go however you do the implementation. Important to note that Order By also suffers the same issue on databases because the order by uses indexes so it's needed. Group bys technically can be ignored because that's done as part of the project, but Odata tries to do it all in one, so we don't have access to breaking them out into separate expressions so to do it with OData it would need to handle all 3.