This is a migrated thread and some comments may be shown as answers.

ToDataSource throws exception using EF Core 3.0 with groups

9 Answers 1784 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
محمد
Top achievements
Rank 1
محمد asked on 30 Oct 2019, 09:23 AM

the exception is:

"Processing of the LINQ expression 'GroupByShaperExpression:
KeySelector: t.Year,
ElementSelector:EntityShaperExpression:
    EntityType: ProjectBudget
    ValueBufferExpression:
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information."

 

this happens when the DataSourceRequest contains Group, and the IQueriable items are empty

 

9 Answers, 1 is accepted

Sort by
0
Aleksandar
Telerik team
answered on 04 Nov 2019, 09:24 AM

Hello,

Thank you for reporting this issue. Based on the provided information, however, I was not able to reproduce the error reported. Would it be possible to modify the attached sample solution, so the issue is reproducible, or send a runable example, where the behavior is isolated? This way I could investigate the issue further?

Looking forward to your reply.

Regards,
Aleksandar
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
n/a
Top achievements
Rank 1
answered on 04 Dec 2019, 09:47 AM
Same issue here ToDataSource with group is not working in EF Core 3.1
0
Aleksandar
Telerik team
answered on 06 Dec 2019, 02:17 PM

Hello Ivan,

The behavior can be related to a limitation in the EntityFramework version as suggested in the exception message. You could find details on Microsoft's documentation:

The issue occurs upon query execution that is not supported by the database. EF Core 2 used client-side evaluations, but with EF Core 3 they are no longer supported. Groping with Entity Framework Core 3.0 is discussed further in this GitHub issue:

Having said that to resolve the issue you could try calling ToList on the items collection before calling ToDataSourceResult and see how that affects the behavior. Alternatively, you could refactor the query generation to meet the Entity Framework Core 3.0 requirements.

Regards,
Aleksandar
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Heiko
Top achievements
Rank 1
Iron
Veteran
answered on 12 Mar 2020, 04:46 PM

I found this thread because I have a similar problem using WPF and QueryableCollectionView. I clearly see the responsibility on the side of Telerik!! It seems that the way Telerik developers are building queries does not take into account how the query evaluation has changed in EF Core 3.x.

See also this other thread of mine: https://www.telerik.com/forums/queryablecollectionview-and-ef-core-3-x

0
Aleksandar
Telerik team
answered on 17 Mar 2020, 01:56 PM

Hello Heiko,

The DataSourceResult uses LINQ internally. The valuation of expressions has been changed with the introduction of EF Core 3.x. The Kendo UI library is dependent on a lot of built-in functionalities that come with the ASP.NET Core framework. 

The handling of the groups in the EF Core has been vastly commented in the GitHub repository below, owned by the creators of EF Core. More information on what are the changes introduced with the newer version and the impact on the data processing could be found in the issue below:

https://github.com/dotnet/efcore/issues/17068

Discussion on the GroupBy functionality has been moved to the following item:

https://github.com/dotnet/efcore/issues/17653

As you can see, the required is yet not supported in EF Core 3. That is why the ToList() workaround is still a valid option. Indeed, for large data sets, it is not a high-performance approach. In such scenarios creating a custom implementation for fetching the data, where only the relevant data is requested and grouped could be an alternative approach.

Regards,
Aleksandar
Progress Telerik

Get quickly onboarded and successful with Telerik UI for ASP.NET Core with the dedicated Virtual Classroom technical training, available to all active customers.
0
Heiko
Top achievements
Rank 1
Iron
Veteran
answered on 18 Mar 2020, 02:57 PM

Hello Aleksandar!

I cannot follow the argument "not yet supported in EF Core 3" and I can't see that in the discussion on github either. As far as I can see this will never be supported, simply because the underlying mechanisms how EF Core works have fundamentally changed. Maybe I have missed it, but there is no mentioning that for example grouping will move back to client side.

For years DataSourceResult is the heart of everything working with ASP.NET and EF Core (and QueryableCollectionView on WPF side also). Paging, sorting, grouping, filtering is done through these classes. Of course I can do it myself, but then why should I spent money on a tool? To work around a bug that will remain in the long run because the cause is not fixed does not make sense.

Regards
Heiko

0
Sanat
Top achievements
Rank 1
answered on 19 Mar 2020, 12:12 PM

Hello Aleksandar,

 

As a workaround,

Is it supported by Telerik to use Sorting, filtering, Paging on IQuerable datasource, obtain DataSourceResult and rerun ToDataSourceResult with grouping request?

 using (var db = new BloggingContext())
                {
                    IQueryable<Blog> blogs = db.Blogs.AsQueryable();
                    Debug.Assert(blogs.Count() > 0);

                    DataSourceRequest sortFilterPageRequest = prepareSortFilterPageRequest();
                    DataSourceResult sortFilterPageResult = blogs.ToDataSourceResult(sortFilterPageRequest);
                                        
                    var matchingBlogs = sortFilterPageResult.Data.OfType<Blog>();

                    DataSourceRequest groupingRequest = prepareGroupingRequest();
                    DataSourceResult finalResult = matchingBlogs.ToDataSourceResult(groupingRequest);

                    Console.WriteLine($"Total blogs in database:{blogs.Count()}");                    
                }

 

Thanks,

Maulik.

0
Aleksandar
Telerik team
answered on 23 Mar 2020, 12:33 PM

Hello Sanat,

The ToDataSourceResult() method has several overloads and basically requires an IQueriable or an IEnumerable as a source of data and a DataSourceRequest object, so that filtering, sorting, paging and grouping are applied. The DataSourceRequest type has the following properties:

public class DataSourceRequest
{
    public DataSourceRequest();
 
    public int Page { get; set; }
    public int PageSize { get; set; }
    public IList<SortDescriptor> Sorts { get; set; }
    public IList<IFilterDescriptor> Filters { get; set; }
    public IList<GroupDescriptor> Groups { get; set; }
    public IList<AggregateDescriptor> Aggregates { get; set; }
}

Having said that, for scenarios where a query cannot be evaluated on the client-side and an exception is thrown you could implement custom Ajax binding where sorting, filtering and paging are performed on an IQuerable object, data is then materialized and grouping is performed on the materialized data. This data object could be passed as the Data property of a DataSourceResult object, that is returned to the View.

public ActionResult Orders_Read([DataSourceRequest]DataSourceRequest request)
{
    // Get the data (code omitted).
    IQueryable<Order> orders = new NorthwindEntities().Orders;
    // Apply filtering (code omitted).
    // Apply sorting (code omitted).
    // Apply paging (code omitted).
    // Materialize the IQueriable object and apply grouping
    // Initialize the DataSourceResult.
    var result = new DataSourceResult()
    {
        Data = orders, // Process data (paging and sorting applied).
        Total = total // The total number of records.
    };

return Json(result); }

For an example on implementing Custom Ajax Binding refer to the tab CustomAjaxBindingController.cs on the following link:

https://demos.telerik.com/aspnet-mvc/grid/customajaxbinding

Additional details on Custom Ajax Databinding could be found here.

Regards,
Aleksandar
Progress Telerik

Get quickly onboarded and successful with Telerik UI for ASP.NET Core with the dedicated Virtual Classroom technical training, available to all active customers.
2
Carlos
Top achievements
Rank 2
Iron
Iron
Iron
answered on 24 May 2021, 10:30 AM | edited on 24 May 2021, 10:31 AM

Hello,

I have the same issue with the ToDataSource with grouping.

I found this solution or workaround:

public DataSourceResult Get([DataSourceRequest] DataSourceRequest request)
{
    // Exclude groups for the request
    IList<GroupDescriptor> groups = request.Groups;
    request.Groups = null;

    // Execute the query without groups (Repository.get() return a IQueryable of the desired DTO)
    DataSourceResult result = Repository.Get().ToDataSourceResult(request);

    // Add the groups again & execute
    request.Groups = groups;
    return result.Data.ToDataSourceResult(request);
}

1. We execute the query without groups but paginated and filtered and we obtain a List of DTOs that have the field by which we want to group 
2. Now we can group without throwing an exception and with a lower performance loss than toList() because the elements are already paginated and filtered

Any problemas with this?

Thanks!

Aleksandar
Telerik team
commented on 27 May 2021, 06:16 AM

Based on the provided information I do not see an issue with the approach used.
Axe
Top achievements
Rank 1
commented on 20 May 2022, 05:30 PM | edited

Thank you Carlos!

I think this is a better way than Custom Ajax Binding, as we don't have to customize for every case.

Though there're 3 minor addons:

1.  request.Groups needs to add to request.Sorts, for 1st ToDataSourceResult();

2. request.Page must be reassigned to 1 (and clear all but Groups), for 2nd ToDataSourceResult();

3. result.Total must be reassigned to the total from 1st result, before return.

Sure, it'd be the best if Kendo supports similar ways out of box.

Carlos
Top achievements
Rank 2
Iron
Iron
Iron
commented on 09 Sep 2022, 11:25 AM | edited

Hello Alex,

I have found a case where this solution is not sufficient: if you have aggregates and groups you can't correctly calculate their aggregates in the groups.
Since it performs the calculation of the group aggregates on the elements you have loaded and not on the total of the DB.
This is the final version that has this limitation, otherwise it works correctly:

 DataSourceRequest requestGroups = new()
 {
        Groups = request.Groups,
        GroupPaging = request.GroupPaging,
        IncludeSubGroupCount = request.IncludeSubGroupCount,
        Aggregates = request.Aggregates
};
// Need to add the groups as sorts in order to load correctly
request.Groups.Each(e =>
 {
        request.Sorts.Insert(0, e);
});
request.Groups = null;
request.GroupPaging = false;
request.IncludeSubGroupCount = false;

// The repository returns a IQueryable
DataSourceResult dataSourceResultWithoutGroup = Repository.Get()
        .ToDataSourceResult(request);

DataSourceResult dataSourceResultWithGroups = dataSourceResultWithoutGroup.Data // Apply again with the groups
         .ToDataSourceResult(requestGroups);

result = dataSourceResultWithoutGroup;
result.Data = dataSourceResultWithGroups.Data;

return result;

 

So my final code to avoid this issue is:

 


        [HttpPost("[controller]/[action]")]
        public virtual ResponseDTO<DataSourceResult> GetGrid([FromBody] GridRequestDTO gridRequest)
        {
            DataSourceResult result;

            DataSourceRequest request = gridRequest.DataSource;
            CustomGroupFilterRequestDTO customGroupFilterRequest = gridRequest.CustomGroupFilters;

            // https://www.telerik.com/forums/todatasource-throws-exception-using-ef-core-3-0-with-groups
            // https://github.com/dotnet/efcore/issues/26748

            // We have groups but not aggregates, we can optimice the query:
            if ((request.Groups != null && request.Groups.Any()) &&
                (request.Aggregates == null || !request.Aggregates.Any()))
            {
                // Create a DataSourceRequest only with the groups
                DataSourceRequest requestGroups = new()
                {
                    Groups = request.Groups,
                    GroupPaging = request.GroupPaging,
                    IncludeSubGroupCount = request.IncludeSubGroupCount,
                };
                request.Groups.Each(group =>
                {
                    request.Sorts.Insert(0, group);
                });
                request.Groups = null;
                request.GroupPaging = false;
                request.IncludeSubGroupCount = false;

                // Apply the query without groups
                DataSourceResult dataSourceResultWithoutGroup = Repository
                 .Get()
                 .ToDataSourceResult(request);

                //  Apply the groups
                DataSourceResult dataSourceResultWithGroups = dataSourceResultWithoutGroup.Data // Apply again with the groups
                 .ToDataSourceResult(requestGroups);

                result = dataSourceResultWithoutGroup;
                result.Data = dataSourceResultWithGroups.Data;
            }
            // We have groups and aggregates
            else if ((request.Groups != null && request.Groups.Any()) &&
                (request.Aggregates != null && request.Aggregates.Any()))
            {
                // Apply the filters first to avoid load all the table.
                DataSourceRequest requestFilters = new DataSourceRequest()
                {
                    Filters = request.Filters,
                };
                request.Filters = null;

                result = Repository
                    .Get()
                    .ToDataSourceResult(requestFilters)
                    .Data
                    .ToDataSourceResult(request);
            }
            else
            {
                result = Repository
                    .Get()
                    .ToDataSourceResult(request);
            }

            return new ResponseDTO<DataSourceResult>
            {
                Status = ResponseStatusEnum.Success,
                Data = result
            };
        }
Any suggestions or problems that my code may be having that I didn't realize?

Thanks!
Aleksandar
Telerik team
commented on 14 Sep 2022, 10:55 AM

Hi Carlos,

Without testing the implementation I cannot confirm whether or not there is some scenario or edge case that might not be covered. For details on how the DataSourceResult is generated and the logic executed depending on whether the request contains aggregates, grouping and/or group paging is enabled I can suggest downloading the source code from your account and inspecting the QueryableExtensions.cs and the private CreateDataSourceResult method that generates the DataSourceResult.

Tags
General Discussions
Asked by
محمد
Top achievements
Rank 1
Answers by
Aleksandar
Telerik team
n/a
Top achievements
Rank 1
Heiko
Top achievements
Rank 1
Iron
Veteran
Sanat
Top achievements
Rank 1
Carlos
Top achievements
Rank 2
Iron
Iron
Iron
Share this question
or