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 https://github.com/DevExpress/DevExtreme.AspNet.Data/issues/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.

[statler]

Same as Filter, Sort and Group in the base level of the options. When it gets to the server, you would invoke EXACTLY the same expressioncompilers you do for a normal filter, sort , group - you just do it after the projection e.g.

dataSource.postProjection.filter([
    [ "value", ">", 3 ],
    "and",
    [ "value", "<", 7 ]
]);

 dataSource.postProjection.sort([
        { getter: "firstName", desc: true },
        "lastName"
     ])

 dataSource.postProjection.group(["birthYear", "gender"])

[AlekseyMartynov]

I see the following arguments against such an API:

• DevExtreme DataSource and load options are part of the DevExtreme data layer interface. Unlike sorting or paging, the concept of server-side projection is too specific to the particular implementation. For example, it cannot be applied to ArrayStore or ODataStore.

• I think that DTO mappings should not leak to the browser. They are server-side implementation detail. If they are coded via Automapper configuration, it makes no sense to pass them with load parameters. Of course, a specific app may use this, but at the library level, it would be a poor design.

Consider a data grid sorted by two columns. The first column belongs to the original model, and the second column is a projection or a computed property. If I understand correctly, this will imply:

{
    sort: [ { selector: "PropOfModel" } ]
    postProjection: {
        sort: [ { selector: "PropOfDTO" } ]
    }
}

In case of two-pass Load as suggested, the 'post-projected' Sort will reset the first-pass Sort. Unlike Filter, Sort and Group cannot be easily divided and ruled.

[AlekseyMartynov]

Actually, filter can also be problematic. Example - grid's search panel generates or groups that cannot be separated:

[
  ["PropOfModel","contains","abc"],
  "or",
  ["PropOfDTO","contains","abc"]
]

[Arafel-BR]

I also have need for this implementation. While our development team managed to use ProjectTo to with the DataSourceLoader.Load() method to list data for the grid we can't filter nor order by many of the columns.

[AlekseyMartynov]

Hello @Arafel-BR

I also have need for this implementation.

Do you mean the specific 'post-projection' idea discussed above or support for ProjectTo in general?

we can't filter nor order by many of the columns.

Do you have a code sample or a project that illustrates the issue? You can share it here or via Support Center.

[statler]

Hi Aleksey

I have come around to the conclusion that you are correct on the post-projection filtering. Not only;

Actually, filter can also be problematic. Example - grid's search panel generates or groups that cannot be separated:

but also, it will cause paging problems - because the paging would happen before the post-projection and then the subsequent operation would only work on a subset of the data.

That said, it would still be handy to be able to specify an automap projection for the original reasons. All this would need to do would be to apply the ProjectTo method to the end of the expression

E.g.

var qryResult = DataSourceLoader.LoadAndProject<MyDto>(sourceQuery, options, _mapper.ConfigurationProvider);

It would be relatively easy to implement I think. While it introduces an additional dependency, the benefit is immense.

[statler]

Hi Aleksey

Me again. Further to your comment in #378 I have looked into the CustomAccessorCompiler as an option.

The CustomAccessorCompiler is awesome! It will actually resolve the trivial cases like we discuss in #378, but I am still at an impasse with more complex sort scenarios - and with the exact same problem for grouping.

My specific example at the moment is - I have a field called Status in the Dto, that is calculated like this:

public bool IsApproved
{
    get
    {
        return (ApprovalDate != null || ApprovalsCount > 0);
    }
}
public bool IsClosedOut
{
    get
    {
        return CloseOutDate != null;
    }
}
public string Status => IsClosedOut ? "Closed Out" : (IsApproved ? "Approved" : "Open");

It is not even a direct result of the projection. With the filtering, this is simple to resolve with the CustomFilterCompilers.RegisterBinaryExpressionCompiler like this;

CustomFilterCompilers.RegisterBinaryExpressionCompiler(info =>
{
    if (String.IsNullOrEmpty(info.AccessorText))
        return Expression.Constant(true);
    if (info.DataItemExpression.Type == typeof(Ncr))
    {
        if (info.AccessorText == "Status" && info.Operation == "=")
        {
            ExpressionStarter<Ncr> predicate=null;
            if (info.Value.ToString() == "Closed Out") predicate = CompileWhereExpression<Ncr>(info, t => t.CloseOutDate != null);
            if (info.Value.ToString()=="Approved") predicate = CompileWhereExpression<Ncr>(info, t=>(t.CloseOutDate == null) && t.ApprovalDate != null);
            if (info.Value.ToString() == "Open") predicate = CompileWhereExpression<Ncr>(info, t => (t.CloseOutDate == null) && t.ApprovalDate == null);
            if (predicate!=null) return predicate.Body;
            return null;
        }
    }
}

However, because the grouping is done before the projection, I can see no way that I can get the grouping to work. Nor can I see a way to use the CustomAccessorCompiler for the sort.

Would it be at all possible just to either:

1. Make an equivalent for CustomFilterCompilers.RegisterBinaryExpressionCompiler for sorting and grouping? E.g. CustomSortCompilers.RegisterBinaryExpressionCompiler and CustomGroupCompilers.RegisterBinaryExpressionCompiler
2. Replace the CustomFilterCompilers.RegisterBinaryExpressionCompiler with a method that allows customizing the expression for any operation?

I see option 2 as being quite powerful. You could add an additional parameter that is an enum describing what operation the expression is being used in.

Right now, any column that is displayed in a grid that is calculated through projection cannot be grouped, and not sorted - unless relatively trivial.

[AlekseyMartynov]

Nor can I see a way to use the CustomAccessorCompiler for the sort.

CustomAccessorCompilers.Register((target, accessorText) => {
    if(target.Type == typeof(Ncr) && accessorText == "Status") {
        return Expression.Condition(
            Expression.Equal(Expression.PropertyOrField(target, "CloseOutDate"), Expression.Constant(null)),
            Expression.Constant("Closed Out"),
            Expression.Condition(
                Expression.Or(
                    Expression.NotEqual(Expression.PropertyOrField(target, "ApprovalDate"), Expression.Constant(null)),
                    Expression.GreaterThan(Expression.PropertyOrField(target, "ApprovalsCount"), Expression.Constant(0))
                ),
                Expression.Constant("Approved"),
                Expression.Constant("Open")
            )
        );
    }

    return null;
});

Resulting expressions:

.OrderBy(obj => IIF((obj.CloseOutDate == null), "Closed Out", IIF(((obj.ApprovalDate != null) Or (obj.ApprovalsCount > 0)), "Approved", "Open")))

.GroupBy(obj => new AnonType`1(I0 = IIF((obj.CloseOutDate == null), "Closed Out", IIF(((obj.ApprovalDate != null) Or (obj.ApprovalsCount > 0)), "Approved", "Open"))))
.OrderBy(g => g.Key.I0)
.Select(g => new AnonType`2(I0 = g.Count(), I1 = g.Key.I0))

However, I'm not sure whether LINQ providers will be able to translate these into SQL.

[statler]

OK, so that is officially a working answer. It does in fact successfully transpose the LINQ to SQL. Fantastic work - very much appreciated. The SQL is

SELECT [n].[NCRID], [n].[Description], [n].[Disposition], [n].[HRid], [n].[Location], [n].[ModifiedBy], [n].[ModifiedOn], [n].[NCRCost], [n].[NCRNo], [n].[Notes], [n].[OptimisticLockField], [n].[PreventativeAction], [n].[ProjectID], [n].[RaisedBy], [n].[RelatedParties], [n].[RootCauseCategory], [n].[RootCauseDetail], [n].[Severity], [n].[ThirdPartyAppReqd], [n].[UniqueID]
FROM [NCR] AS [n]
WHERE [n].[ProjectID] = 1
ORDER BY CASE
    WHEN [n].[CloseOutDate] IS NOT NULL
    THEN N'Closed Out' ELSE CASE
        WHEN [n].[ApprovalDate] IS NOT NULL
        THEN N'Approved' ELSE N'Open'
    END
END DESC

[statler]

I have ended up writing extension methods that are used as follows

await Devex.DataSourceLoader.LoadDtoAsync<T, TDto>(_context, _mapper, ProjectEntities, loadOptions)

When using this code you need to explicitly deal with any properties that may be present in the Dto but not the base object using CustomAccessorCompiler, but only if they may be filtered, grouped or sorted. If you do implement CustomAccessorCompiler on these properties, all issues with grouping, filtering and sorting just disappear and the AspNet.data projection Just Works. If you need to work with the objects post projection, refer https://github.com/DevExpress/DevExtreme.AspNet.Data/issues/338#issuecomment-573900547

The code for these is below (includes some sync methods too, and overloads for expressions);

using AutoMapper;
using AutoMapper.QueryableExtensions;
using cpDataASP.DevExtreme.AspNet.Data;
using DevExtreme.AspNet.Data;
using DevExtreme.AspNet.Data.ResponseModel;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using AspNet= DevExtreme.AspNet.Data;

namespace cpDataASP.Devex
{
    public class DataSourceLoader
    {
        public static LoadResult LoadDto<T, TDto>(DbContext context, IMapper _mapper, IQueryable<T> source, DataSourceLoadOptions options)
            where T : class
        {
            var qryResult = AspNet.DataSourceLoader.Load(source, GetModelOptions<T, TDto>(options));
            return GetDtoQueryResult<T, TDto>(context, _mapper, options, qryResult);
        }

        public static LoadResult LoadDto<T, TDto>(DbContext context, IMapper _mapper, Expression<Func<T, bool>> preFilter, DataSourceLoadOptions options)
    where T : class
        {
            var qryResult = AspNet.DataSourceLoader.Load(context.Set<T>().Where(preFilter), GetModelOptions<T, TDto>(options));
            return GetDtoQueryResult<T, TDto>(context, _mapper, options, qryResult);
        }

        private static LoadResult GetDtoQueryResult<T, TDto>(DbContext context, IMapper _mapper, DataSourceLoadOptions options, LoadResult qryResult) where T : class
        {
            if (options.Select == null || options.Select.Count() == 0)
            {
                options.Filter = null;
                var dtoQryResult = FilterAsDto<T, TDto>(context, _mapper, qryResult, options);
                dtoQryResult.totalCount = qryResult.totalCount;
                return dtoQryResult;
            }
            else return qryResult;
        }

        static DataSourceLoadOptions GetModelOptions<T, TDto>(DataSourceLoadOptions originalOptions)
        {
            var modelOptions = Clone(originalOptions);
            if (modelOptions.Group != null) modelOptions.Sort = modelOptions.Group;
            modelOptions.Group = null;
            return modelOptions;
        }

        static T Clone<T>(T source)
        {
            var serialized = JsonConvert.SerializeObject(source, Formatting.Indented,
                new JsonSerializerSettings
                {
                    ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                });
            return JsonConvert.DeserializeObject<T>(serialized);
        }

        private static LoadResult FilterAsDto<T, TDto>(DbContext context, IMapper _mapper, LoadResult loadedData, DataSourceLoadOptions loadOptions) where T : class
        {
            Expression<Func<TDto, bool>> lambda = GetDtoByIdPredicate<T, TDto>(context, loadedData);
            var returnData = AspNet.DataSourceLoader.Load(context.Set<T>().ProjectTo<TDto>(_mapper.ConfigurationProvider).Where(lambda), loadOptions);

            return returnData;
        }

        public async static Task<LoadResult> LoadDtoAsync<T, TDto>(DbContext context, IMapper _mapper, IQueryable<T> source, DataSourceLoadOptions options)
            where T : class
        {
            //var qryResult = AspNet.DataSourceLoader.Load(source, GetModelOptions<T, TDto>(options));
            var qryResult = await AspNet.DataSourceLoader.LoadAsync(source, GetModelOptions<T, TDto>(options));
            return await GetDtoQueryResultAsync<T, TDto>(context, _mapper, options, qryResult);
        }

        public async static Task<LoadResult> LoadDtoAsync<T, TDto>(DbContext context, IMapper _mapper, Expression<Func<T, bool>> preFilter, DataSourceLoadOptions options)
    where T : class
        {
            var qryResult = await AspNet.DataSourceLoader.LoadAsync(context.Set<T>().Where(preFilter), GetModelOptions<T, TDto>(options));
            return await GetDtoQueryResultAsync<T, TDto>(context, _mapper, options, qryResult);
        }

        private static async Task<LoadResult> GetDtoQueryResultAsync<T, TDto>(DbContext context, IMapper _mapper, DataSourceLoadOptions options, LoadResult qryResult) where T : class
        {
            if (options.Select == null || options.Select.Count() == 0)
            {
                options.Filter = null;
                options.Take = 0;
                options.Skip = 0;
                var dtoQryResult = await FilterAsDtoAsync<T, TDto>(context, _mapper, qryResult, options);
                dtoQryResult.totalCount = qryResult.totalCount;
                return dtoQryResult;
            }
            else return qryResult;
        }        

        private async static Task<LoadResult> FilterAsDtoAsync<T, TDto>(DbContext context, IMapper _mapper, LoadResult loadedData, DataSourceLoadOptions loadOptions) where T : class
        {
            Expression<Func<TDto, bool>> lambda = GetDtoByIdPredicate<T, TDto>(context, loadedData);
            var returnData = await AspNet.DataSourceLoader.LoadAsync(context.Set<T>().ProjectTo<TDto>(_mapper.ConfigurationProvider).Where(lambda), loadOptions);

            return returnData;
        }

        private static Expression<Func<TDto, bool>> GetDtoByIdPredicate<T, TDto>(DbContext context, LoadResult loadedData) where T : class
        {
            var pkey = context.Model.FindEntityType(typeof(T).FullName).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();

            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);
            return lambda;
        }
    }
}

[Edited 23/1/2020 to fix grouping, skip and take]

[statler]

Aleksey

Just extending on this, I have written code that automatically adds all of the Automapper mappings as CustomAccessors. This now gives us an end-to-end solution for managing projections seamlessly inside the aspnet library. At the moment though, you would have to do this using my extensions. Is there any way you would consider integrating this into the library?

All that would be necessary is to add my extension methods (above post) for the LoadDto & LoadDtoAsync methods, and some derivative of the following code in the customaccessors to provide the automatic mapping of projections.

Essentially what this does it provide access to every field that is explicitly mapped in automapper for use in grouping, sorting and filtering with no additional code. There is also a helper to make customaccessors much easier to write in those instance where an explicit accessor is necessary.

You can create an accessor by adding it to the RegisterBasicAccessors like this;

CustomAccessorLibrary.Add<AreaCode,string>("FirstLetter", t => t.AreaCodeName.FirstOrDefault().ToString().ToUpper());

The full code is

using AutoMapper;
using cpDataORM.Helpers;
using cpDataORM.Models;
using DevExtreme.AspNet.Data.Helpers;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace cpDataASP.Helpers
{
    public class CustomAccessors
    {
        public static void RegisterCustomAccessors()
        {
            AmapProjectionProcessor();
            RegisterBasicAccessors();
            CustomAccessorCompilers.Register((target, accessorText) =>
            {                   
                var accessor = CustomAccessorLibrary.Get(target, target.Type.Name, accessorText);
                if (accessor != null) return accessor;

                return null;
            });
        }

        public static AccessorLibrary CustomAccessorLibrary = new AccessorLibrary();

        private static void AmapProjectionProcessor()
        {
            var maps = new Mapper(new MapperConfiguration(cfg =>
                {
                    cfg.AddProfile<AutoMapperProfileService>();
                    cfg.AddProfile<AutoMapperProfileORM>();
                }
            ));

            var allTypeMaps = maps.ConfigurationProvider.GetAllTypeMaps();
            foreach (TypeMap map in allTypeMaps)
            {
                var propertyMaps = map.PropertyMaps;
                foreach (PropertyMap propertyMap in propertyMaps)
                {
                                    string modelMemberType = propertyMap?.TypeMap?.SourceType?.Name;
                                    string destinationName = propertyMap?.DestinationName;
                                    var exp = propertyMap.CustomMapExpression;
                                    if (modelMemberType!=null && destinationName!=null && exp == null) continue;
                        CustomAccessorLibrary.Add(modelMemberType, destinationName, exp);
                }
            }
        }

        public class AccessorLibrary
        {
            Dictionary<string, Dictionary<string, Accessor>> _dctAccessors;

            HashSet<Accessor> _hsAccessors = new HashSet<Accessor>();
            public AccessorLibrary()
            {
            }

            public void Add(string TypeName, string PropertyName, LambdaExpression ResolveExpression)
            {
                _hsAccessors.Add(new Accessor(TypeName, PropertyName, ResolveExpression));
            }

            public void Add<T>(string PropertyName, LambdaExpression ResolveExpression)
            {
                _hsAccessors.Add(new Accessor(typeof(T).Name, PropertyName, ResolveExpression));
            }

            public void Add<T, U>(string PropertyName, Expression<Func<T, U>> ResolveExpression)
            {
                _hsAccessors.Add(new Accessor(typeof(T).Name, PropertyName, ResolveExpression));
            }

            public Expression Get(Expression target, string TypeName, string PropertyName)
            {
                if (_dctAccessors == null) CompileAccessorDictionary();
                if (_dctAccessors == null) return null;
                if (_dctAccessors.ContainsKey(TypeName))
                {
                    var expressionForType = _dctAccessors[TypeName];
                    if (expressionForType.ContainsKey(PropertyName))
                    {
                        var expression = expressionForType[PropertyName].ResolveExpression;
                        return new ParameterVisitor(expression.Parameters, target as ParameterExpression)
                            .VisitAndConvert(expression.Body, PropertyName);
                    }
                }

                return null;
            }

            void CompileAccessorDictionary()
            {
                _dctAccessors = _hsAccessors
                    .GroupBy(a => a.TypeName)
                    .ToDictionary(typeGroup => typeGroup.Key, typeGroup => typeGroup
                        .GroupBy(t => t.PropertyName)
                        .ToDictionary(propertyGroup => propertyGroup.Key, propertyGroup => propertyGroup.First()));
            }
        }

        public class Accessor
        {
            public string TypeName { get; set; }
            public string PropertyName { get; set; }
            public LambdaExpression ResolveExpression { get; set; }
            public Accessor()
            {
            }
            public Accessor(string typeName, string propertyName, LambdaExpression resolveExpression)
            {
                TypeName = typeName;
                PropertyName = propertyName;
                ResolveExpression = resolveExpression;
            }
        }

        private static void RegisterBasicAccessors()
        {
            //CustomAccessorLibrary.Add<AreaCode,string>("FirstLetter", t => t.AreaCodeName.FirstOrDefault().ToString().ToUpper());
        }
    }
}

[AlekseyMartynov]

Your results are impressive!

The CustomAccessorCompilers helper has proven to be really helpful, so we can now safely remove the EditorBrowsable attribute.

---

When I take the most recent code snippet, the following members are missing:

• AutoMapperProfileService, AutoMapperProfileORM - optional auxiliary objects?
• VisitAndConvert - custom extension?

---

Is there any way you would consider integrating this into the library?

I'd prefer a separate GitHub project with a separate NuGet package. By the analogy with these contrib-style projects.

[statler]

Thanks Aleksey

1. AutoMapperProfileService and AutoMapperProfileORM are just my automapper configurations. These are a standard way of creating the maps for automapper. Example below
2. VisitAndConvert is a method of the ExpressionVisitor. Code for the ParameterVisitor which inherits this class, and overrides VisitParameter follows.

3. I don't mind how it is delivered. My interest is in having this code available for others as it has taken ages to figure it out and hopefully now no-one else has to. This all works fine in my code, and I am on a deadline for the foreseeable future, so it is unlikely I will have time to create the project or manage it - I am hoping this is something someone in Devex finds valuable enough to manage?

   using System;
   using System.Collections.ObjectModel;
   using System.Linq.Expressions;
   
   namespace cpDataASP.Helpers
   {
       public class ParameterVisitor : ExpressionVisitor
       {
           private readonly ReadOnlyCollection<ParameterExpression> _from;
           private readonly ParameterExpression _to;
           public ParameterVisitor(
               ReadOnlyCollection<ParameterExpression> from,
               ParameterExpression to)
           {
               if (from == null) throw new ArgumentNullException("from");
               if (to == null) throw new ArgumentNullException("to");
               this._from = from;
               this._to = to;
           }
           protected override Expression VisitParameter(ParameterExpression node)
           {
               for (int i = 0; i < _from.Count; i++)
               {
                   if (node == _from[i]) return _to;
               }
               return node;
           }
       }
   }

Example automapper code

    using AutoMapper;
    using cpDataASP.ControllerModels;
    using cpDataORM.Models;

    namespace cpDataASP.Helpers
    {
        public class AutoMapperProfileService : Profile
        {
            public AutoMapperProfileService()
            { 
                CreateMap<LotImportDto, Lot>();
                CreateMap<ContractNotice, ContractNoticeListDto>()
                    .ForMember(dest => dest.RequestByName, opt => opt.MapFrom(src => src.RequestBy == null ? "" : src.RequestBy.FirstName + " " + src.RequestBy.LastName)).IncludeAllDerived()
                    .ForMember(dest => dest.CnToIDs, opt => opt.MapFrom(src => src.CnTos.Where(x => x.NoticeToId != null).Select(x => x.NoticeToId.Value).ToList())).IncludeAllDerived()
                    .ForMember(dest => dest.CnToNames, opt => opt.MapFrom(src => src.CnTos.Select(x => x.NoticeTo == null ? x.NoticeEmail : x.NoticeTo.FirstName + " " + x.NoticeTo.LastName).ToList())).IncludeAllDerived()
                    .ForMember(dest => dest.NumberOfResponses, opt => opt.MapFrom(src => src.CnResponses.Count())).IncludeAllDerived()
                    .ForMember(dest => dest.NumberOfActionedResponses, opt => opt.MapFrom(src => src.CnResponses.Count(x => x.DateActioned != null))).IncludeAllDerived();

            }
        }
    }

[AlekseyMartynov]

@statler

I am hoping this is something someone in Devex finds valuable enough to manage?

Although I cannot promise that we'll arrange this code into a repository or a package, we at DevExpress appreciate your efforts. Your code stays safe in this ticket, our support engineers are aware of it, and they will direct users with similar inquiries here.

[Blackleones]

As @Arafel-BR even I also need this kind of feature. Maybe as a plugin library if DevExpress doesn't want to make this beautiful library smarter.

I am going to explain my problem:

I'm trying to create a project following the "Clean Architecture" so I've four layers: Domain, Infrastructure, Application, User interface (currently Blazor) where:

Infrastructure has a dependency on Domain and Application.

• Application has a dependency on Domain.
• Domain has no dependency.
• User interface has a dependency on Application

This architecture gives me the possibility to work inside the domain layer using domain entities, domain language and so on. The application layer has the goal of receiving command/query with parameters, translate the request to the domain languages and finally translate the result in a specific DTO for the request's result. This means that the User interface layer works with the Application layer and I can modify the Domain Layer (for example changing a domain entity) without break the User interface.

Currently, if I use DevExtreme.AspNet.Data I have to take a decision on which roads I want to walk:

The easiest road:

break the Clean Architecture and give the User Interface a dependency on the Domain layer so I can use all the functionalities of DatasourceLoader like applying filtering/grouping/so on directly on the SQL query. Side effect: I can remove the Application layer because now I don't have anymore an abstraction between the User interface and the Domain layer. This is a huge side effect because what if I have to add another user interface, like a mobile (android/ios) and let it communicate with my system through controllers? I will have to replicate a lot of code. But the worst thing is that I can't translate the result in a specific DTO why I can't pass a DTO as loadOptions. Yeah, I could map the DTO loadOptions but the reason for choosing this road is avoiding this step.

The road that I would like:

maintaining the Clean Architecture, so maintaining an abstract layer between the User interface and the domain layer. Side effect: DataSourceLoader now works on a data source that I've previously prepared and translated using a DTO model. Using this way I lost all the benefit of applying filtering/grouping/so on directly on the query. This will be a huge problem when my database will grow.

@AlekseyMartynov, @statler I would like to ask you if there is already a plugin library.

Note: I am using AutoMapper too.

[AlekseyMartynov]

@Blackleones

Using this way I lost all the benefit of applying filtering/grouping/so on directly on the query. This will be a huge problem when my database will grow.

Automapper allows you to keep these benefits:

• Map via AutoMapper.QueryableExtensions.ProjectTo
• In the App layer, use IQueryable<DTO>, not IEnumerable<DTO> or IList<DTO>
• Keep the mappings simple, declare them via Expressions

class OrderDTO {
    public int ID { get; set; }
    public DateTime? Date { get; set; }
}

class AppImpl {
    NorthwindContext _nwind;

    public AppImpl(NorthwindContext nwind) {
        _nwind = nwind;
    }

    IMapper _mapper = new MapperConfiguration(cfg => cfg
        .CreateMap<Order, OrderDTO>()
        .ForMember(vm => vm.ID, m => m.MapFrom(o => o.OrderId))
        .ForMember(vm => vm.Date, m => m.MapFrom(o => o.OrderDate))
    ).CreateMapper();

    public IQueryable<OrderDTO> GetDataForView123() {
        return _nwind.Orders.ProjectTo<OrderDTO>(_mapper.ConfigurationProvider);
    }
}

var loadResult = DataSourceLoader.Load(app.GetDataForView123(), new DataSourceLoadOptions {
    Filter = new[] { "Date", ">", "2011-11-11" },
    Sort = new[] { new SortingInfo { Selector = "Date" } },
    Take = 10
});

SQL:

exec sp_executesql N'SELECT TOP(@__p_0) [dtoOrder].[OrderDate] AS [Date], [dtoOrder].[OrderID] AS [ID]
FROM [Orders] AS [dtoOrder]
WHERE [dtoOrder].[OrderDate] > ''2011-11-11T00:00:00.000''
ORDER BY [Date], [ID]',N'@__p_0 int',@__p_0=10

[Blackleones]

@AlekseyMartynov this is a smart solution. I've tried it and it works!

So the idea behind this code is:

1. map the SQL fields with the DTO
2. apply filters over DTO fields
3. execute the query

right?

[AlekseyMartynov]

Correct. For a more detailed description, check the relevant Automapper docs.

[krptodr]

I was so happy to have read this issue. I can't express how happy I am to see a work-around. Great work to @statler for the brains and grunt work, and thank you @AlekseyMartynov for providing direction where it was necessary and remaining active.

I would like to ask if it's possible to have a complete DevExpress example created, illustrating this workaround in the same fashion as the other examples?

[statler]

This is now available out of the box with the fork at https://github.com/statler/DevExtreme.AspNet.Data