Support AutoMapper's ProjectTo in DataSourceLoader

[statler]

As described in https://www.devexpress.com/Support/Center/Question/Details/T758528/modify-the-datasourceloader-to-support-projection-as-part-of-the-original-query-operation and referenced threads - repeated below for simplicity

In a nutshell, the issue is this;

It is best practice in EF to return a DTO rather than the original object. Regardless of best practice, efficiency demands in so in my application as I have tables with large text fields that are not necessary for populating lists and would increase the size of the payload over 100x. I get the data for my lists using DataSourceLoader GET controllers, and I use filtering, sorting and grouping in the DataSourceLoader extensively. I ProjectTo to ensure that my payload from SQL to API, and my payload from API to client are efficient and contain no more data that is necessary.

At the moment, it is impossible to perform operations on the full set of object properties, but return only a subset using ProjectTo. Any property specified in the options e.g. a filter occurs after the ProjectTo, so the property is not available for filtering at that point in the SQL. As per the ticket, you cannot simply operate on the data after it is returned, as it breaks other elements of the returned set for more complex operations like grouping.

Also, a Select is not the answer as this requires far too much hard coding to move between types - this is what automapper and ProjectTo are for.

At the moment I have created a workaround that;

1. Returns the DataSourceLoader result if a Select is specified (obviously no projection is required in this case)
2. Programatically identifies the key of the original entity (e.g. User => UserId)
3. Runs the DataSourceLoader without Projecting, but returning only the Id of the entity - at this point I have all of the IDs matching the original query - IDset
4. Performs a simple where(x=>IDset.Contains(x=>[IDProperty])).ProjectTo()

This works, but it would be far better if the datasourceloader could be modified to append my projection so it occurs after the datasourceloader filtering / sorting / grouping. I can't see that this would require much modification.

My code below for anyone else with this issue.

var src = _context.Approval.Where(x =>
  x.ProjectId == _userService.Project_ID &&
  x.PublishDate != null &&
  x.NewApprovalId == null).Include(x => x.ApprovalTo);
var result = _context.FilterAsDto<Approval, ApprovalListDto>(src, loadOptions);

public LoadResult FilterAsDto<T, TDto>(Func<T, bool> preFilter, DataSourceLoadOptions loadOptions) where T : class
{
	var qryResult = DataSourceLoader.Load(Set<T>().Where(preFilter), loadOptions);
	if (loadOptions.Select == null || loadOptions.Select.Count()==0) return FilterAsDto<T, TDto>(qryResult, loadOptions);
	else return qryResult;
	
}

public LoadResult FilterAsDto<T, TDto>(IQueryable<T> sourceQuery, DataSourceLoadOptions loadOptions) where T : class
{

	var qryResult = DataSourceLoader.Load(sourceQuery, loadOptions);
	if (loadOptions.Select == null || loadOptions.Select.Count() == 0) return FilterAsDto<T, TDto>(qryResult, loadOptions);
	else return qryResult;
}

private LoadResult FilterAsDto<T, TDto>(LoadResult loadedData, DataSourceLoadOptions loadOptions) where T : class
{
	var pkey = Model.FindEntityType(typeof(T)).FindPrimaryKey().Properties.Select(n => n.Name).Single();
	var pKeyExp = Expression.Parameter(typeof(T));
	var pKeyProperty = Expression.PropertyOrField(pKeyExp, pkey);
	var keySelector = Expression.Lambda<Func<T, int>>(pKeyProperty, pKeyExp).Compile();

	if (loadedData.data is IEnumerable<Group>) return loadedData;
	else
	{
		var OriginalSummary = loadedData.summary;
		List<int> idList = loadedData.data.Cast<T>().Select(keySelector).ToList();

		var pKeyExpDto = Expression.Parameter(typeof(TDto));
		var pKeyPropertyDto = Expression.PropertyOrField(pKeyExpDto, pkey);
		var method = idList.GetType().GetMethod("Contains");
		var call = Expression.Call(Expression.Constant(idList), method, pKeyPropertyDto);
		var lambda = Expression.Lambda<Func<TDto, bool>>(call, pKeyExpDto);
		var defOptions = new DataSourceLoadOptionsBase();
		defOptions.Sort = loadOptions.Sort;
		defOptions.RequireTotalCount = loadOptions.RequireTotalCount;
		var returnData= DataSourceLoader.Load(Set<T>().ProjectTo<TDto>(_mapper.ConfigurationProvider).Where(lambda), defOptions);

		returnData.summary = OriginalSummary;
		returnData.totalCount = loadedData.totalCount;

		return returnData;
	}
}

[AlekseyMartynov]

Thank you for sharing your implementation.

Your points regarding DTOs in general and AutoMapper in particular are totally valid. The topic has been raised several times (#335, #338).

Do you mind if I change the subject to 'Support AutoMapper's ProjectTo in DataSourceLoader'?

[statler]

No problem at all. Your repo :)

[AlekseyMartynov]

Hello @statler

I'm trying to sketch a possible design for the DTO mapping feature.

I see the following key points:

1. Data processing (filter, sort, etc) is performed in the context of the full model (Product).
2. Mapping to a DTO (ProductDTO) occurs at the very last step, after filtering, sorting, grouping, paging.
3. Model and DTO property names don't necessarily match (Product.ProductName vs ProductDTO.Name).
4. A DevExtreme widget bound to a collection of DTOs knows nothing about the full model property names. A data grid will display the 'Name' column and its header click will issue the order by Name request.
5. DataSourceLoader needs to know how to map DTO properties back to the model properties to build valid LINQ expressions.

The most generic implementation can be a pair of user-defined functions:

1. Project a data item to a DTO
2. Map a DTO property name back to the full model property name

Does it look right? Do I miss any additional requirements?

[statler]

Hi Aleksey

Can we assume that your implementation will use Automapper, or at least read its mappings from the Automapper config? I would really recommend that just slots in automapper as this will provide simple and complex mapping that would form the basis for your projections, and the great majority of anyone projecting to Dtos will already have automapper definitions as there is no other sane way to do this. I expect that if you were rolling your own, you would only be able to do simple property name replacement in the Dto, and that would defeat the purpose because Dtos regularly use complex mappings e.g. here is one of mine

        CreateMap<LotQuantity, LotQuantityDto>()
            .ForMember(dest => dest.ApprovedByName, opt => opt.MapFrom(src => src.ApprovedByUser == null ? null : src.ApprovedByUser.FirstName + " " + src.ApprovedByUser.LastName))
            .ForMember(dest => dest.LotAdjDate, opt => opt.MapFrom(src => src.Lot == null ? src.ModifiedOn :
                src.Lot.DateRejected != null ? src.Lot.DateRejected :
                src.Lot.DateConf != null ? src.Lot.DateConf :
                src.Lot.DateGuar != null ? src.Lot.DateGuar :
                src.Lot.DateOpen));

Whether it is automapper or another system, you will need a config that deals with cases where the property names change. The automapper config would be ideal.

The question becomes, does the query specify property names using the Order or the OrderDto. While the consumer might realistically expect that all of their query would be in terms of the OrderDto, this is not the solution. The projection is happening after the filter and sort because in many cases the filter needs to operate on properties not available in the OrderDto, so properties in the query may contain at least some properties relative to Order, and not necessarily the OrderDto.

The problem then becomes that this negates the ability to sort on properties only contained in the OrderDto that are only available after the projection, including calculated properties such as OrderDto.SumValues.

One option might be (though it would be a bit of a design change) to introduce a new set of operators which can assume the ProductDto properties, something like

Change

{
    Filter:"",
    Sort:"",
    Group:""
}

To

{
    Filter:"",
    Sort:"",
    Group:""
    PostProjection : {
        Filter:"",
        Sort:"",
        Group:""
}

This would have stacks of advantages, and increase the power of the library considerably. It would also deal with the question of when and where to use Order vs OrderDto properties. You could even make the library smart enough that if the property in the base Filter (for example) is not present in Order, but does exist in OrderDto, then it is applied in PostProjection.Filter. This would ensure compatibility with the widgets (even backwards compatibility), and make the projection issues invisible on the client side.

E.g if a datagrid is showing a property OrderDto.SumValues, and the user filters on that column, the widget will send {"Filter",[["SumValues",">=", 10]]. If the library tests and determines that there is no property on Order called OrderValue, but there is a property on OrderDto, then it moves the property to PostProjection.Filter. Doing this with the ordering would fix issue #388

This would eliminate the need for any property substitutions, and everything could be done with automapper. No changes would be necessary for the existing widgets, as everything is done in the library. It should make implementation relatively simple too. Effectively instead of the existing library which effectively just does this (after all expression trees etc., and ignoring sorts and aggregations);

Order.Where(x=>OrderFilter).Orderby(x=>OrderSort);

You are simply doing this

Order.Where(x=>OrderFilter).Orderby(x=>OrderSort).ProjectTo<OrderDto>(_mapper.ConfigurationProvider).Where(x => OrderDtoFilter).Orderby(x=>OrderDtoSort);

This would also allow clientside stores to compile complex queries and sorts either pre or post projection (or both)

You could call the projection something like this;

DataSourceLoader.LoadAndProject<OrderDto>(OrderEntities, _mapper.ConfigurationProvider, loadOptions)

where _mapper.ConfigurationProvider is the mapping configuration from automapper - IConfigurationProvider.

I think this would be preferable to

DataSourceLoader.Load(OrderEntities, loadOptions).Project<OrderDto>(_mapper.ConfigurationProvider)

The first option would make it easier to do a single pass and identify any properties that need to move from Filter or Sort into PostProjection.Filter and PostProjection.Sort

[AlekseyMartynov]

Thanks @statler for your detailed reply. Now it's clear that member renaming is not sufficient.

Can we assume that your implementation will use Automapper, or at least read its mappings from the Automapper config?

I'm inclined to think that it would be better to integrate with Automapper in a separate library or a plugin. Your code snippets are a good illustration of how this can be done. However, I see that there's a need for built-in projection support, so that developers don't need to manually access/cast/iterate LoadResult.data.

I don't think that we want to make the library smart enough to automatically handle various mapping options and edge cases.

[statler]

I figured the automatic handling of mapping would be out, but it seems to me that the inclusion of a post projection option would be really quite easy and would not break anything in the existing architecture. All that would be required would be;

On the javascript side allow for the specification of additional options e.g.

{
    PostProjection : {
        Filter:"",
        Sort:"",
        Group:""
}

In the C# library, simply running through the expression tree code a second time, just with the LoadOptions changed to only include the PostProjection Filter, Sort and Group. The easiest way to do this would probably be to create a generic overload of Load (Load).

I would do a PR for it, but I can't get my head arount the grouping and where I can insert into the code so that I am always applying the projection to the IQueryable rather than the Group - Also, some of the Expression work is a bit different to how I work trees :(

The mapping code is REALLY simple

        IEnumerable<TDto> result=null;
        var ProjectEntities = service.GetEntitiesForProject().AsEnumerable();
        if (ProjectEntities is IQueryable<T> qryProjectEntities) result= qryProjectEntities.ProjectTo<TDto>(_mapper.ConfigurationProvider);
        else if (ProjectEntities is IEnumerable<T> enumProjectEntities)
        {
            List<TDto> lstProjected = new List<TDto>();
            foreach (T pe in enumProjectEntities)
            {
                lstProjected.Add(_mapper.Map<TDto>(pe));
            }
            result = lstProjected;
        }

[AlekseyMartynov]

it seems to me that the inclusion of a post projection option would be really quite easy

It seems so. However, as you noticed, grouping is a tough subject. Also, interoperability with existing options (Select, PreSelect, etc) is important.

I would do a PR for it

PRs are welcome. If you do, please include unit tests. I think it's essential to test how well Automapper works with SQL translation. Refer to the recent ticket on this topic.

[AlekseyMartynov]

On the javascript side allow for the specification of additional options

Do you need to control projections from the client side? Isn't it sufficient to control mappings on the server?

[statler]

As per the example, the additional options are for specifying Post-projection filter, sort and group.

{
PostProjection : {
    Filter:"",
    Sort:"",
    Group:""
}

This solves several issues;

1. When property names change during the projection. This makes it clear where the sort/filter/group should be configured - after the projection or before it. The original filter/sort/group properties are unambiguously applying to the original, unprojected object. Postprojection filter/sort/group occur after.
2. When the projection introduces new calculated fields. The filter / sort / group can be applied to the calculated field, not just the original object fields
3. Both applications of the filter/sort/group can occur prior to calculations for attributes on the LoadResult

[AlekseyMartynov]

What are the data types of the Filter, Sort, Group properties in the suggested PostProjection object? Empty strings don't describe them well enough.