orderby on DataGrid/GetListAsync - Blazor UI #1057 | Support

Leonardo.Willrich created 3 years ago

ABP Framework version: v4.2.2
UI type: Blazor
DB provider: EF Core
Tiered (MVC) or Identity Server Separated (Angular): yes / no
Exception message and stack trace:
Steps to reproduce the issue:

Hi,

I'd like to know how to perform orderby passing a string as parameter when I have two tables connect by join in the method GetListAsync in a CrudAppService class.

In the Tutorial, Item 10. Book and Author Relation, in the method GetListAsync we have this comment:

orderby input.Sorting //TODO: Can not sort like that!

In AspNetZero project I have an extension for OrderBy and PageBy that make life easy. But, I haven't found those extensions for Abp.IO Blazor UI project.

Here is my code:

public override async Task<PagedResultDto<SupplyNetworkDto>> GetListAsync(PagedAndSortedResultRequestDto input)
{
    //Set a default sorting, if not provided
    if (input.Sorting.IsNullOrEmpty())
    {
        input.Sorting = nameof(SupplyNetworkDto.Name);
    }

    //Get the IQueryable<Book> from the repository
    var queryable = await Repository.GetQueryableAsync();

    //Prepare a query to join books and authors
    var query = from supplyNetwork in queryable
                join networkType in _networkTypeRepository on supplyNetwork.NetworkTypeId equals networkType.Id
                orderby input.Sorting // It is not working as per example in ABP.IO Documentation Example
                select new { supplyNetwork, networkType };

    query = query
        .Skip(input.SkipCount)
        .Take(input.MaxResultCount);

    //Execute the query and get a list
    var queryResult = await AsyncExecuter.ToListAsync(query);

    //Convert the query result to a list of BookDto objects
    var dtos = queryResult.Select(x =>
    {
        var dto = ObjectMapper.Map<SupplyNetwork, SupplyNetworkDto>(x.supplyNetwork);
        dto.NetworkTypeName = x.networkType.Name;
        return dto;
    }).ToList();

    //Get the total count with another query
    var totalCount = await Repository.GetCountAsync();

    return new PagedResultDto<SupplyNetworkDto>(
        totalCount,
        dtos
    );
}

10 Answer(s)

0
mladen.macanovic created 3 years ago
As far as I know the OrderBy only works with single list sources, which is usualy used for repository pattern.

You could try with diferent approach maybe:

https://stackoverflow.com/questions/32061770/call-orderby-with-a-field-name-as-a-string

https://stackoverflow.com/questions/41244/dynamic-linq-orderby-on-ienumerablet-iqueryablet?rq=1
0

Leonardo.Willrich created 3 years ago

What would you implement to resolve the Tutorial sort issue?

Is there some plan to implement those extension in the framework in future as AspNetZero has done? For example:

Leonardo.Willrich created 3 years ago

I've worked out my issue with this code:

var list = Repository.Join(_networkTypeRepository, A => A.NetworkTypeId, B => B.Id, (supplyNetwork, networkType) => new { supplyNetwork, networkType }).ToList();
            var dtos = list.Select(x => {
                var dto = ObjectMapper.Map<SupplyNetwork, SupplyNetworkDto>(x.supplyNetwork);
                dto.NetworkTypeName = x.networkType.Name;
                return dto;
            }).AsQueryable().OrderBy(input.Sorting).ToList();
            dtos = dtos.Skip(input.SkipCount).Take(input.MaxResultCount).ToList();

It will work because this CRUD will always have few records. But, it seems inefficient when there are too many records because it will bring all record from the database to the server and then perform the pagination stuff (Skip / Take). I have to do that to be able to convert my entity into my dto class and then filter using the Dto fields, including the relationships.

What would be recommended for this scenario?

alper created 3 years ago

Support Team Director

@Leonardo this is out of ABP Framework context. It's an EF Core issue. I can advise you this; 1- You know how many sortable fields you have. 2- Write a switch case / if statement to convert from string ordery by to statement order by

eg:

var query = from supplyNetwork in queryable
           join networkType in _networkTypeRepository on supplyNetwork.NetworkTypeId equals networkType.Id;
                
if (input.Sorting == "ABC")
           query = query.orderby(x=>x.Abc) ;
else if (input.Sorting == "ZYZ")
           query = query.orderby(x=>x.Xyz) ;
           
query = select new { supplyNetwork, networkType };

alper created 3 years ago

Support Team Director

the below code is from a working project:

IQueryable<Guid> recordIdQuery;

var orderField = sorting.Split(' ').First().ToLower();
switch (orderField)
{
	case "creationtime":
		recordIdQuery = query.Select(x => new {x.Book.Id, x.Book.CreationTime})
			.Distinct()
			.OrderBy(sorting)
			.Select(q => q.Id);
		break;
	case "publishDate":
		recordIdQuery = query.Select(x => new {x.Book.Id, x.Book.PublishDate})
			.Distinct()
			.OrderBy(sorting)
			.Select(q => q.Id);
		break;
	case "pageSize":
		recordIdQuery = query.Select(x => new {x.Book.Id, x.Book.PageSize})
			.Distinct()
			.OrderBy(sorting)
			.Select(q => q.Id);
		break;
	default:
		throw new ArgumentException("Unknown order field: " + orderField);
}

orderedBookIds = await recordIdQuery
	.PageBy(skipCount, maxResultCount)
	.ToListAsync(GetCancellationToken(cancellationToken));

0

Leonardo.Willrich created 3 years ago

Hi @Alper,

Thank you for your answer. That is a solution, but, it is not dynamic at all. If in future a new field is added to the grid, there will be a need to add a new condition in the switch clause.

AspNetZero implements that extension for EF which makes life easier. I just don't want to implement those extensions and then in the next versions ABP.IO Commercial framework release the same extension, it would be waste of time from my side as a framework client.

alper created 3 years ago

Support Team Director

ABP Framework is using the following library for Dynamic LinQ Queries

https://www.nuget.org/packages/System.Linq.Dynamic.Core

In the old versions of EF (like EF6 it was supported) but in EF Core you cannot write a query and use a string sortable field.

On the other hand, you can write a query like below:

using System.Linq.Dynamic.Core;


  var dbSet = await GetDbSetAsync();
            return await dbSet
                .WhereIf(
                    !filter.IsNullOrWhiteSpace(),
                    author => author.Name.Contains(filter)
                 )
                .OrderBy(sorting)
                .Skip(skipCount)
                .Take(maxResultCount)
                .ToListAsync();

this notation is frequently used in our samples => https://docs.abp.io/en/abp/latest/Tutorials/Part-7?UI=MVC&DB=EF

but again afaik you cannot write such a query:

var query = from supplyNetwork in queryable
                join networkType in _networkTypeRepository on supplyNetwork.NetworkTypeId equals networkType.Id
                orderby input.Sorting // it is not supported by EF Core
                select new { supplyNetwork, networkType }

you can use it like;

query = query.OrderBy(input.Sorting);

PS: don't forget to import using System.Linq.Dynamic.Core

Leonardo.Willrich created 3 years ago

@alper, that is a great solution, but it doesn't work 100%. I've been using a similar solution for my AspNetZero projects, but with just one difference.

Issue: When there is a aggregation/association with another class and there is a .Include() in the query, it will not find the field by that string because the right string should be "objectname.field", for example:

Student { public int Id {get; set;} public string Name {get; set;} public int GenderId {get; set;} public virtual Gender Gender {get; set;} }

Gender { public int Id {get; set;} public string Name {get; set;} }

Then, the query will be:

Student.Include(x => x.Gender).OrderBy("Gender.Name");

But, when the fields comes from the grid in the filter, it will come only "Name" or "GenderName", it will depends on my Dto class.

To sort that out in AspNetZero using JQuery DataGrid, there is a property called "Data". When I set that property, it is passed as parameter when filtering. For example:

var dataTable = _$assetFlagTable.DataTable({
            scrollY: "calc(100vh - 505px)",
            scrollCollapse: false,
            paging: true,
            serverSide: true,
            processing: true,
            listAction: {
                ajaxFunction: _assetFlagService.getAll,
                inputFilter: function () {
                    return {
                        filter: $('#AssetFlagTableFilter').val(),
                        nameFilter: $('#NameFilterId').val(),
                        actionFilter: $('#ActionFilterId').val(),
                        flagTypeFilter: $('#FlagTypeFilterId').val(),
                        isActiveFilter: getFilterIsActive()
                    };
                }
            },
            columnDefs: [
                {
                    width: 120,
                    targets: 0,
                    data: null,
                    orderable: false,
                    autoWidth: false,
                    defaultContent: '',
                    rowAction: {
                        cssClass: 'btn btn-brand dropdown-toggle',
                        text: '<i class="fa fa-cog"></i> ' + app.localize('Actions') + ' <span class="caret"></span>',
                        items: [
                            {
                                text: app.localize('View'),
                                action: function (data) {

                                    console.log('View AssetFlag', data);

                                    _viewAseetFlagModal.open({ data: data.record });
                                }
                            },
                            {
                                text: app.localize('Edit'),
                                visible: function () {
                                    return _permissions.edit;
                                },
                                action: function (data) {

                                    console.log('Edit AssetFlag', data);

                                    _createOrEditModal.open({ id: data.record.assetFlag.id });
                                }
                            },
                            {
                                text: app.localize('Delete'),
                                visible: function () {
                                    return _permissions.delete;
                                },
                                action: function (data) {
                                    deleteAssetFlag(data.record.assetFlag);
                                }
                            }]
                    }
                },
                {
                    targets: 1,
                    data: "assetFlag.flagName",  // -->> HERE is the property to be used in the OrderBy method in the back-end.
                    name: "flagName",
                    render: function (data, type, row) {
                        return data.length > 100 ? data.substr(0, 100) + '…' : data;
                    }
                },
                {
                    targets: 2,
                    data: "assetFlag.flagAction",
                    name: "flagAction",
                    render: function (data, type, row) {
                        return data.length > 100 ? data.substr(0, 100) + '…' : data;
                    }
                },
                {
                    targets: 3,
                    data: "assetFlag.flagType",
                    name: "flagType",
                    render: function (data, type, row) {
                        return data.length > 100 ? data.substr(0, 100) + '…' : data;
                    }
                },
                {
                    targets: 4,
                    data: "assetFlag.isActive",
                    name: "isActive",
                    render: function (check) {
                        if (check) {
                            return '<div class="text-center"><i class="fa fa-check-circle m--font-success" title="True"></i></div>';
                        }
                        return '<div class="text-center"><i class="fa fa-times-circle" title="False"></i></div>';
                    }
                }
            ]
        });

My back-end function to populate the grid:

public async Task<PagedResultDto<GetAssetFlagForView>> GetAll(GetAllAssetFlagsInput input)
        {
            try
            {
                var filteredAssetFlag = _assetFlagRepository.GetAll()
                            .WhereIf(!string.IsNullOrWhiteSpace(input.Filter), e => e.FlagName.Contains(input.Filter) || e.FlagAction.Contains(input.Filter))
                            .WhereIf(!string.IsNullOrWhiteSpace(input.NameFilter), e => e.FlagName.Contains(input.NameFilter))
                            .WhereIf(!string.IsNullOrWhiteSpace(input.ActionFilter), e => e.FlagAction.Contains(input.ActionFilter))
                            .WhereIf(!string.IsNullOrWhiteSpace(input.FlagTypeFilter), e => e.FlagType.Contains(input.FlagTypeFilter))
                            .WhereIf(input.IsActiveFilter.HasValue, e => e.IsActive == input.IsActiveFilter.Value);
                filteredAssetFlag = filteredAssetFlag.OrderBy(input.Sorting ?? "flagname asc");
                var query = (from o in filteredAssetFlag
                             select new GetAssetFlagForView()
                             {
                                 AssetFlag = ObjectMapper.Map<AssetFlagDto>(o)
                             });

                var totalCount = await query.CountAsync();

                var AssetFlag = await query
                    .PageBy(input)
                    .ToListAsync();

                return new PagedResultDto<GetAssetFlagForView>(
                    totalCount,
                    AssetFlag
                );
            }catch(Exception ex)
            {
                throw ex;
            }
        }

That works perfectly, using the Abp extension and DataGrid with the property Data for each column.

0

alper created 3 years ago
Support Team Director

we use Blazorise DataGrid in the Blazor template. And you can also use Nested fields in datagrid. https://blazorise.com/docs/extensions/datagrid/#nested-fields

as a summary, this doesn't seem to be a framework issue and we are talking about EF Core functionality.
0

ServiceBot created 3 years ago
Support Team Automatic process manager

This question has been automatically marked as stale because it has not had recent activity.

Have an answer to this question? Log in and write your answer.