Provide a CustomSortProvider similar to the CustomFilterCompilers

[statler]

In Version 2.3.0 you introduced CustomFilterCompilers to allow us to provide complex filters. This has been very much appreciated as it lets us deal with child collections.

Would it be possible to apply the same logic for complex sorting? This is especially necessary when working with projections. I am hoping it is kind of trivial :)

[AlekseyMartynov]

We introduced CustomFilterCompilers in the context of #277 to allow custom expression with Any(). For example:

.Where(c => c.Collection.Any(itemCondition));

Would you please provide an example of an OrderBy clause that you hope to build with the suggested CustomSortProvider?

[ZeBobo5]

I've got the same problem as I've got a string property which must be sorted as an Interger (like: "1" < "2" < "10" < "20"), but keeped as String in result.

So a CustomSortCompilers could be useful with parameters :

• Type of object
• Accessort name
• SortOrder

[AlekseyMartynov]

@ZeBobo5 Thank you, makes sense.

Though for custom comparisons, it should be sufficient to introduce a capability to associate an instance of a IComparer with a specific class member.

For example, we can add to DataSourceLoadOptionsBase:

loadOptions.UseComparer("Prop1", new MyComparer<...>());

[ZeBobo5]

Yes, it's a good implementation but don't forget to pass Type of the model. Or use : loadOptions.UseComparer<TModel>(m => m.Property, new MyComparer<...>());

[statler]

My case is a snowball from other issues I have worked around with the library and discussed earlier - projections. It also affects grouping, but that is another matter.

Basically, I have a table - lets say product, but I project it to a productDto before I return it (because this is just good practice, and because I need to shape the data, excluding some massive text field for registers). The benefit of the Dto projection is that it reduces the data by to less than 1% its original size. The problem is that it must happen before the datasourceloader filtering, sorting etc. My solution to this is to run it as two queries - one on the product base object that ONLY returns the ID of the selected objects. Then I run a separate query that updates the loader.data with the productDto projection. This way I never query the large data from the server.

The problem with this is that the consumer only sees the Dto, so they expect to be able to operate on those properties. For example, if the product table has a catName that is a projection of category.categoryName. The user will specify something like

{orderby:[{
  "Selector": "catName",
  "Desc": true
}]}

The initial sort needs to be on category.categoryName to get the right Ids. Then after completing my second query, I need to sort the result on catName. At the moment I have a static dictionary that does a substitution based on type.

I have yet to figure out the grouping. It would similarly benefit from an interceptor.

Here is my projection code, including the substitution.

    public static LoadResult FilterAsDto<T, TDto>(this cpContext context, IMapper _mapper, IQueryable<T> sourceQuery, DataSourceLoadOptions loadOptions) where T : class
    {
        var qryResult = DataSourceLoader.Load(sourceQuery, GetModelOptions<T, TDto>(loadOptions));
        if (loadOptions.Select == null || loadOptions.Select.Count() == 0) return FilterAsDto<T, TDto>(context, _mapper, qryResult, loadOptions);
        else return qryResult;
    }

    static DataSourceLoadOptions GetModelOptions<T, TDto>(DataSourceLoadOptions originalOptions)
    {
        var modelOptions = originalOptions.Clone();
        if (modelOptions.Sort == null) return modelOptions;
        foreach (SortingInfo si in modelOptions.Sort)
        {
            si.Selector = CustomSortTranslator.GetModelPropertyName<T, TDto>(si.Selector);
        }
        return modelOptions;
    }

    private static LoadResult FilterAsDto<T, TDto>(cpContext context, IMapper _mapper, LoadResult loadedData, DataSourceLoadOptions loadOptions) 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();

        if (loadedData.data is IEnumerable<Group>) return loadedData;
        else
        {
            var OriginalSummary = loadedData.summary;

            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.RequireTotalCount = loadOptions.RequireTotalCount;
            defOptions.Sort = loadOptions.Sort;
            var returnData = DataSourceLoader.Load(context.Set<T>().ProjectTo<TDto>(_mapper.ConfigurationProvider).Where(lambda), defOptions);

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

            return returnData;
        }
    }

[AlekseyMartynov]

@ZeBobo5 New ticket to track IComparer support: https://github.com/DevExpress/DevExtreme.AspNet.Data/issues/384

[AlekseyMartynov]

@statler

Looks like the same story as in #367, with the addition of updating SortingInfo.Selector before the second DataSourceLoader.Load. If I understand correctly, the CustomSortTranslator.GetModelPropertyName function returns Category.CategoryName for CatName.

If this approach works properly for your app, then it doesn't seem like you need any new sorting API.

[statler]

If #367 is implemented, then no, this would not be required. As it stands though, #367 isn't implemented and if this is something that is more achievable, then it would be a good first step

[AlekseyMartynov]

Ok, I'll close this as a dup of #367. Would you have other additions, please post them to the original ticket comments.

[statler]

Hi Aleksey

Can we please reopen this? As #367 is not addressing this, it becomes a pretty big issue for me in managing projections - even when I am using my own workarounds.

Where a user specifies a sort that operates on a projected column, I will need to catch this before the projection, and create an equivalent sort method. For example:

If I have a DateClosed column on the data source, and a projected column Status which is set to "Closed" or "Open" depending on whether the DateClosed column has a value or is null, I need to intercept the Sort the same way I can with the filter. I will detect a request to sort by Status, and then depending on direction, compile a sort filter something like

if (property =="Open") x=>x.Orderby(x=>DateClosed ==null);
else x=>x.Orderby(x=>DateClosed !=null);

[AlekseyMartynov]

In this specific case of DateClosed vs Status, isn't it sufficient to replace SortingInfo.Selector with DateClosed?

DateClosed ↑ | Status ↑
NULL         | Open
yesterday    | Closed

DateClosed ↓  | Status ↓
yesterday     | Closed
NULL          | Open

You can also invert SortingInfo.Desc if you need Closed to go first.

The code snippet is not clear to me. The if part is a condition on an element while OrderBy is an operation on the entire collection.

---

Here's my take on how this can be done without any sorting interventions:

[JsonConverter(typeof(Newtonsoft.Json.Converters.StringEnumConverter))]
public enum OrderStatus {
    [EnumMember(Value = "Open")]
    Open,
    [EnumMember(Value = "Closed")]
    Closed
}

public class OrderDTO {
    public int ID { get; set; }
    public OrderStatus Status { get; set; }
    public decimal Freight { get; set; }
}

[HttpGet("orders")]
public async Task<IActionResult> Orders(DataSourceLoadOptions loadOptions) {
    var tempConfig = new MapperConfiguration(cfg => cfg.CreateMap<Order, OrderDTO>()
        .ForMember(d => d.ID, opt => opt.MapFrom(c => c.OrderId))
        .ForMember(d => d.Status, opt => opt.MapFrom(c => c.ShippedDate == null ? OrderStatus.Open : OrderStatus.Closed))
        .ForMember(d => d.Freight, opt => opt.MapFrom(c => c.Freight))
    );

    var tempMapper = tempConfig.CreateMapper();

    loadOptions.RemoteGrouping = false; // against 'could not be translated and will be evaluated locally'

    var source = _nwind.Orders.ProjectTo<OrderDTO>(tempMapper.ConfigurationProvider);
    return Json(await DataSourceLoader.LoadAsync(source, loadOptions));
}

Translated by EF Core info:

SELECT COALESCE([dtoOrder].[Freight], 0.0) AS [Freight], [dtoOrder].[OrderID] AS [ID], CASE
  WHEN [dtoOrder].[ShippedDate] IS NULL
  THEN 0 ELSE 1
END AS [Status]
FROM [Orders] AS [dtoOrder]
ORDER BY [Status], [ID]

Instead of StringEnumConverter, you can use a lookup column.

[statler]

The code snippet is not clear to me. The if part is a condition on an element while OrderBy is an operation on the entire collection.

Sorry typo - pseudocode should be

if (property =="Status") 
if (direction=="ascending") x=>x.Orderby(x=>DateClosed ==null);
else x=>x.Orderby(x=>DateClosed !=null);

but yes, you are right, you could do what you are suggesting in this simple case. That is a lot of plumbing for one property on one entity though - and like you point out, this is a trivial example. I have far more sophisticated ones that would need a dynamic enum which I don't think is even possible.

The exact same model used for the filter, but used for the sort would fix this without any issues.

[AlekseyMartynov]

The exact same model used for the filter, but used for the sort would fix this without any issues.

I completely forgot that we have another (currently hidden) feature CustomAccessorCompilers. You can use it to handle missing members. Refer to the example. Isn't this what you were looking for?

[frankiDotNet]

Hello,

I have a similar problem. I am using DevExtreme.AspNet.Data lib also for my custom filter controls, because it does a good job. Now I have a calss like this:

public class Person{
    public int Id {get; set;}
    public string name {get; set;}
    public List<Card> Cards {get; set;}
}

public class Card{
    public int Id {get; set;}
    public int CardType {get; set;}
    public string CardNumber {get; set;}
}

And my Filter control can create a filter for all persons that have a card.

With the 'RegisterBinaryExpressionCompiler' I can create easily an Any() filter. But my control has also the possibility to sort. So if I would like to sort, the resulting query looks like this:

persons.OrderBy(p => p.Cards);

Mirrored to the database this creates a cartesian product. The right orderby clause would be:

persons.OrderBy(p => p.Cards.Any());

So at this point I would need a way to create a custom sorting expression like it is done for the filter.

[AlekseyMartynov]

@Franki1986

Try a custom accessor:

public class Startup {

    public void Configure(IApplicationBuilder app) {

        DevExtreme.AspNet.Data.Helpers.CustomAccessorCompilers.Register((expr, accessorText) => {
            if(expr.Type == typeof(Category) && accessorText == "ProductCount") {
                var products = Expression.Property(expr, "Products");
                return Expression.Call(typeof(Enumerable), "Count", products.Type.GetGenericArguments(), products);
            }

            return null;
        });

        // ...
    }
}

DataSourceLoader.Load(
    _nwind.Categories.Include(c => c.Products),
    new DataSourceLoadOptions {
        Sort = new[] {
            new SortingInfo { Selector = "ProductCount" }
        }
    }
);

The DevExtreme.AspNet.Data.Helpers.CustomAccessorCompilers class is currently hidden. We'll publish it in a next release.

[frankiDotNet]

If I use the syntax '[Propertyname]', or in words, if the accessorText is in brackets, it will be seen as collection type. So:

DevExtreme.AspNet.Data.Helpers.CustomAccessorCompilers.Register((expr, accessorText) => {
            if (accessorText.StartsWith("[") && accessorText.EndsWith("]"))
            {
               accessorText = accessorText.Replace("[", "").Replace("]", "");
               var collection = Expression.Property(expr, accessorText);
               return Expression.Call(typeof(Enumerable), "Any", collection.Type.GetGenericArguments(), collection);
            }

            return null;
         });

          var loadOptions = new DevExtreme.AspNet.Data.DataSourceLoadOptionsBase();                  
          loadOptions.Filter = new[] { "[Cards]", "=", "true" };
          loadOptions.Sort = new SortingInfo[]{new SortingInfo(){Selector = "[Cards]"}};
          var result = DataSourceLoader.Load(db.Persons, loadOptions);

Works!! Thanks!!