Subqueries with EF Core

[ErikPilsits-RJW]

I'm having a few issues with the translation of subqueries.

First one, which works as expected directly in an EF context, instead tries to select the entire table via Remote.Linq.

var result = await oh.FirstOrDefaultAsync(o => o.Orderid == oh.Max(oi => oi.Orderid));

Same thing with a simple (contrived in this case) aggregate.

var result = await oh.Select(o => new { o.Orderid, sum = oh.Sum(oi => 1) }).FirstAsync();

Second one is similar but also has a join, cannot be translated with the following exception.

  var result = await (from order in oh
                       join detail in od
                       on new { order.Orderid, order.Shipid } equals new { detail.Orderid, detail.Shipid }
                       where order.Orderid == oh.Max(o => o.Orderid)
                       select order)
                       .FirstOrDefaultAsync();

System.ArgumentException: Expression of type 'Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1[WmsApi.Infrastructure.SynapseEf.Models.Orderhdr]' cannot be used for constructor parameter of type 'Remote.Linq.IAsyncRemoteQueryable`1[WmsApi.Infrastructure.SynapseEf.Models.Orderhdr]' (Parameter 'arguments[0]')
   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Dynamic.Utils.ExpressionUtils.ValidateArgumentTypes(MethodBase method, ExpressionType nodeKind, ReadOnlyCollection`1& arguments, String methodParamName)
   at System.Linq.Expressions.Expression.New(ConstructorInfo constructor, IEnumerable`1 arguments)
   at Remote.Linq.ExpressionTranslator.RemoteToSystemLinqTranslator.Visit(Expression node)

Removing the where clause executes correctly.

I'm working on a library for my company, and I expect subqueries and aggregates to be commonly needed features. It seems like the server is trying to process these server side, as opposed to being translated by the EF core provider. Are these something that can be supported?

[6bee]

I've created a repro of the issue on dotnetfiddle.net and posted a question on stackoverflow, hopping for some help.

[KarloX2]

Hello! I read thru this issue thread and also read the linked stackoverflow question and the answer I find there. Frankly, this all is hard to understand. I'm a little concerned. It sounds like a show-stopper when working with EF-Core (and that's what I'm planning). Can this be reliably fixed in Remote.Linq and, if so, when will the fix be released?

Thanks!

[6bee]

@KarloX2 The issue basically has been fixed already. I just haven't found time yet to implement a clean solution. While the issue seems specific for EF Core, the fix is implemented in general remote.linq component. I probably provide a patch version as is and leave required refactoring for a proper implementation for vNext.

[KarloX2]

@6bee Hi Christof, that sounds good. I don't want to push, of course, but could you give an approximate time frame? I am preparing a presentation and I would like to propose Remote.Linq as a possibility. It would be better if the bug is fixed by then, otherwise there will be concerns about this solution.

[6bee]

@KarloX2 As written above, the issue has already been resolved (although no new package had been released yet). You may now test the fix using version 7.1.0-alpha-001 available on https://www.myget.org/F/aqua/api/v3/index.json

[KarloX2]

@6bee , thanks. Yes, I would like to try it. But the link you provided returns a json object. I don't know how to retrieve the binaries from it?

[6bee]

You may use the link as nuget package source in VS or in nuget.config or on CLI. See package-source-mapping and restore-packages for more info.

[6bee]

Intended code refactoring is complete and may be tested using version 7.1.0-alpha-002 (nuget feed: https://www.myget.org/F/aqua/api/v3/index.json)

[KarloX2]

@6bee Thanks for providing this preview version.

I've been struggling to get something to work in my demo app, but it's very difficult. You have an overwhelming number of examples, but they are pretty interconnected I suppose. I just can't find the right way to figure out what I need and put the pieces together.

My scenario: I have modeled a gRPC server with RPC calls with .proto. The arguments to the calls can be native types or JSON. I am using EF Core with MS SQL Server and I have corresponding entity classes in an assembly. I want my client to send a LINQ expression via gRPC RPC to the server, which executes it and returns the result as JSON (using System.Json.Text).

But it is very complicated to realize this from the multitude of examples. I can't accomplish this in the time I have. Can you give me some concrete help?

[6bee]

@KarloX2 This seems rather unrelated to this issue ;) There's no out-of-the box support for gRPC. You may want to try using Remote.Linq.protobuf-net to integrate remote linq with protobuf (note that only proto2 is supported). However, for starters I suggest you pick and examine one of the samples. There is one for protobuf-net too. Feel free to open a new issue in case of specific questions.

[6bee]

Fix for this issue has been released as of version 7.1.0