search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.71k stars 3.17k forks source link

Query: Support First over subquery which returns enumerable result #27867

Open janseris opened 2 years ago

janseris commented 2 years ago

Hi I have a problem with a query. Executing a query which seems OK produces ArgumentException with a message which doesn't tell anything about the error. I spent approximately 1 hour localising the error and it is in the query itself.

The problem is that I need to use the broken query because I explicitly need only WORKFLOW from the first SADA_WORKFLOW to force 1 to 1 relation constraint (note: I am using DB-First), not from all SADA_WORKFLOW enities! The second query which works, does not force the 1 to 1 relation.

Is there, please, any way to achieve desired results from the query?

Data model: image

Dictionary: Workflow = workflow configuration for a system module for a project (finite automaton) Sada workflow = a reusable/shared collection of workflows Stavebni Objekt = building Projekt = project Konfigurace projektu = project configuration Typ x = a type/kind of x

Please see the following comparison:

The difference is:

image

Sample project and database here: The project assumes that the database is loaded as EFCore6QueryArgumentException on .\SQLEXPRESS (local named SQL Server Express instance).

issue.zip

Running the "broken" query produces this exception:

System.ArgumentException: ' Argument type 
'System.Collections.Generic.ICollection`1[Tempus.Vystavba.Data.Models.WORKFLOW]'
does not match the corresponding member type
'Tempus.Vystavba.Data.Models.WORKFLOW' Arg_ParamName_Name'

Repro video:

https://user-images.githubusercontent.com/64279914/164792695-83111d50-3eb2-4dec-a11f-cde1047c71b2.mp4

Exception details:

System.ArgumentException
  HResult=0x80070057
  Message= Argument type 'System.Collections.Generic.ICollection`1[Tempus.Vystavba.Data.Models.WORKFLOW]' does not match the corresponding member type 'Tempus.Vystavba.Data.Models.WORKFLOW' Arg_ParamName_Name
  Source=System.Linq.Expressions
  StackTrace:
   at System.Linq.Expressions.Expression.ValidateNewArgs(ConstructorInfo constructor, ReadOnlyCollection`1& arguments, ReadOnlyCollection`1& members)
   at System.Linq.Expressions.Expression.New(ConstructorInfo constructor, IEnumerable`1 arguments, IEnumerable`1 members)
   at System.Linq.Expressions.NewExpression.Update(IEnumerable`1 arguments)
   at System.Linq.Expressions.ExpressionVisitor.VisitNew(NewExpression node)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessSelectMany(NavigationExpansionExpression source, LambdaExpression collectionSelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Single[TSource](IQueryable`1 source)
   at Tempus.Vystavba.Data.DAOs.Test.StavDAO.GetInicialniStavKontrolaStavbyBroken(Int32 stavebniObjektID) in C:\Users\janse\Desktop\issue\Tempus.Vystavba.Backend\Tempus.Vystavba.Data\StavDAO.cs:line 24
   at Program.<Main>$(String[] args) in C:\Users\janse\Desktop\issue\Tempus.Vystavba.Backend\Tempus.Vystavba.Services.Test\Program.cs:line 13

System info: Microsoft Visual Studio Community 2022 (64-bit) - Preview Version 17.2.0 Preview 4.0 Windows 10 Home 21H1 Host (useful for support): Version: 6.0.4 Commit: be98e88c76 Microsoft.EntityFrameworkCore.SqlServer 6.0.4 EFCorePowerTools 2.5.918

smitpatel commented 2 years ago

Problematic query:

ss.Set<Level1>()
  .Where(l1 => l1.Id == 1)
  .SelectMany(e => e.OneToMany_Optional1)
  .Where(l2 => l2.Date == new DateTime(2012, 12, 12))
  .SelectMany(e => e.OneToMany_Optional2.First().OneToMany_Optional3)
  .Where(l4 => l4.Name == "L4 01")

issue is First followed by collection in from clause Work-around

ss.Set<Level1>()
  .Where(l1 => l1.Id == 1)
  .SelectMany(e => e.OneToMany_Optional1)
  .Where(l2 => l2.Date == new DateTime(2012, 12, 12))
  .SelectMany(e => e.OneToMany_Optional2.First().OneToMany_Optional3.Where(l4 => l4.Name == "L4 01"))

Moving Where after SelectMany inside makes it work.