Aggregation with GROUP BY using AspNet Core OData and Entity Framework Core

asp.net-core c# entity-framework-core linq2db odata

Question

Simple OData aggregation fails on Entity Framework Core (SQL Sever) and Asp.Net Core:

odata/file?$apply=groupby((FileType))

Due to inability to translate used GroupBy LINQ expression warning is issued:

Microsoft.EntityFrameworkCore.Query: Warning: The LINQ expression 'GroupBy(new GroupByWrapper() {GroupByContainer = new LastInChain() {Name = "FileType", Value = [$it].FileType}}, [$it])' could not be translated and will be evaluated locally.

And then exception is thrown: 

system.security.verificationexception operation could destabilize the runtime

most relevant stack:

at lambda_method(Closure , File )
   at System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at System.Linq.GroupedEnumerable`3.GetEnumerator()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

Used NuGet packages versions:

  • Microsoft.AspNetCore.OData 7.0.1
  • Microsoft.EntityFrameworkCore.SqlServer 2.1.1
  • Microsoft.AspNetCore 2.1.2

Repo showing the issue: https://github.com/xmichaelx/ODataGroupByTest

Issue on GitHub: https://github.com/OData/WebApi/issues/1578

I'm not sure where the issue lies, whether EF Core should be able to convert LINQ query to valid SQL or OData generates difficult to convert GroupBy expression. Until it's sorted out I'm looking for some workaround.

1
2
8/19/2018 1:20:16 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

Use Linq2Db extensions to EF Core as workaround.

After adding NuGet packages:

  • linq2db 2.2.0
  • linq2db.EntityFrameworkCore 1.0.1

And changing from

    public IActionResult Get()
    {
        return Ok(_db.Files);
    }

to 

    public IActionResult Get()
    {
        return Ok(_db.Files.ToLinqToDB());
    }

everything works.

Branch with fix: https://github.com/xmichaelx/ODataGroupByTest/tree/issue-workaround linq2db.EntityFrameworkCore : https://github.com/linq2db/linq2db.EntityFrameworkCore

EDIT: sum, avg, min, max works, countdistinct does not work

2
8/19/2018 3:35:48 PM

Related Questions

Error when scaffolding using Entity Framework Core in ASP.Net core

I am trying to create models using an existing SQL Server 2016 database. When the run the command prompt as shown in the documentation:...PM> Scaffold-DbContext "'Server=techestatevm.southeastasia.cloudapp.azure.com;Database=Blogging;User Id=newuser;Password=pass;'" Microsoft.EntityFrameworkCore.SqlServer -verbose ...I get this error:...Working dir...
.net-core asp.net-core asp.net-core-mvc ef-database-first entity-framework-core
asked by Vinod

How to Read Record From Database in ASP.MVC Core using Entity Framework Core 1.0

I am working on simple MVC 6 app with .NET Core 1.0 and trying to read data from database using Entity Framework Core 1.0 and getting following error in my LINQ query from where I am trying to read table; The LINQ query is in HomeController class...Error...An unhandled exception occurred while processing the request. SqlException: Invalid object na...
asp.net-core-mvc c# entity-framework-core sql-server-2014
asked by Toxic

Decimal troubles in aspnet core / ef core entity

My website has a form with a number input that takes decimal numbers. This is sent back to the controller in a viewmodel. When validation passes, the viewmodel is converted to an entity like this:...quotation.WidthMeters = model.WidthMeters. ...So here is where the fun starts....My website is in the Dutch culture. We use comma for decimal seperator...
asp.net-core-mvc-2.0 c# entity-framework-core
asked by jao

How to tell Entity Framework that my ID column is auto-incremented (AspNet Core 2.0 + PostgreSQL)?

Code is simple. ...Tag.cs... entity:...public partial class Tag { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public string Name { get; set; } public string Description { get; set; } } ...HomeController.cs... class:...public async Task<IActionResult> Index() { tagRepository.Insert(n...
asp.net-core auto-increment entity-framework-core npgsql
asked by Andrey Kotov

Entity Framework Core filter related entities and get top 2 for each group

I am using Entity Framework Core 2.0.1 and I have the following models...public class Article { [DatabaseGenerated(DatabaseGeneratedOption.Identity)] [Key] public int Id { get; set; } [Required] public string Title { get; set; } public string Slug { get; set; } public int Approved { get; set; } public DateTime Artic...
c# entity-framework entity-framework-core
asked by Endri

Using automapper.collection in aspnet core & ef core doesn't updated edited records in a collection

I'm using asnet boilerplaite aspnet core and ef core. I've initialized automapper.collection in PreInitialize as follows:...Configuration.Modules.AbpAutoMapper().Configurators.Add( cfg =>{ cfg.AddCollectionMappers(); cfg.CreateMap<OrderDTO, Order>().EqualityComparison((odto, o) => odto.ID == o.ID); cfg.CreateMap<OrderIte...
asp.net-core aspnetboilerplate automapper collections entity-framework-core
asked by DLedge

AspNet Core WebApi OData - Many to Many join tables

As is the recommended pattern in EF Core these days when you have a many to many join we do something like this:...Fluent API, many-to-many in Entity Framework Core...Having done that I'm faced with the issue as to how I go about exposing that in the OData model....Since technically the Entity type definition has no key property (as it uses a comp...
asp.net-core entity-framework-core odata relationship
asked by War

How to install entity framework core to asp.net core 3 project using visual studio code

I'm creating a new MVC application using visual studio code and I want to add entity framework core to my project. My application is using the latest .net core 3 but when I tried to install package ...Microsoft.EntityFrameworkCore.SqlServer... its show an error like this ...Unable to resolve 'Microsoft.EntityFrameworkCore.SqlServer (>= 3.0.0)' for ...
asp.net-core c# entity-framework-core visual-studio-code
asked by codeken

Entity Framework Core using select and group by

I want to use ...select... and ...groupby... in my query..._context.UserChart_Tbl .Where(uc => uc.UserID == "6") .GroupBy(g => g.OrgnizationID) .Select(g => g.OrgnizationID) .ToArray(); ...But I get an error in ...select...Error CS1061 'IGrouping' does not contain a definition for 'OrgnizationID' and no accessible e...
asp.net-core c# entity-framework entity-framework-core
asked by topcool

Unable to send array data using ajax with ASP.NET Core and Entity Framework Core

You can pass the list of objects by :...$.ajax({ type: "POST", url: "Reception/PatientTests/SavePendingTest", data: { pendingTestResult: tests }, headers: { "RequestVerificationToken": $('input[name="__RequestVerificationToken"]').val() }, success: function (data) { alert(data); }, error: function (e) { ...
asp.net-core asp.net-core-2.0 asp.net-mvc entity-framework-core
asked by Nan Yu

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow