EF 7 / Core:計算子表中的記錄數

c# entity-framework entity-framework-core sql-server

我試圖在EF Core GitHub的問題部分提出這個問題/問題,但沒有得到任何回复,所以我不得不假設我在錯誤的地方提問...

我正在使用EntityFramework.Core和EntityFramework.MicrosoftSqlServer 7.0.0-rc1-final軟件包,我正在嘗試通過類似於以下SQL的代碼執行某些操作:

SELECT COUNT(*)
FROM [UploadPackage] [up]
INNER JOIN [RawClip] [rc] on [up].[Id] = [rc].[PackageId]
WHERE [up].[UserId] IN (1,2,3)

我嘗試過以下方法:

選項1:

DbContext.UploadPackages.Include(up => up.Clips).Where(up => userIds.Contains(up.UserId)).SelectMany(u => u.Clips).CountAsync()

選項2:

DbContext.RawClips.Include(rc => rc.Package).Where(rc => userIds.Contains(rc.Package.UserId)).CountAsync()

使用Fluent API配置關係,如下所示:

modelBuilder.Entity<UploadPackage>().HasMany(up => up.Clips).WithOne(rc => rc.Package);
modelBuilder.Entity<RawClip>().HasOne(rc => rc.Package).WithMany(up => up.Clips);

選項1生成以下SQL:

SELECT [up].[Id], [up].[AssetId], [up].[FileName], [up].[FileSize], [up].[PackageId], [up].[Path], [up].[ProxyUrl], [up].[StatusDetails], [up].[StatusId], [up].[ThumbnailUrl], [up].[UploadCompletedDateTime], [up].[UploadStartedDateTime], [r].[Id], [r].[AssetId], [r].[FileName], [r].[FileSize], [r].[PackageId], [r].[Path], [r].[ProxyUrl], [r].[StatusDetails], [r].[StatusId], [r].[ThumbnailUrl], [r].[UploadCompletedDateTime], [r].[UploadStartedDateTime], [up].[Id]
FROM [RawClip] AS [up]
CROSS JOIN [RawClip] AS [r]
WHERE [up].[UserId] IN (2, 3, 4, 1)

正如您所看到的,它正在將RawClip表連接到自身。在連接的一側,它為表提供別名“[up]”,它似乎是對UploadPackage的引用,即使它的別名表實際上是RawClip表。這最終會引發錯誤,因為where子句試圖過濾“[up]。[UserId]”列,它存在於UploadPackage表中,但不存在於RawClip表中。

選項2失敗而不生成任何SQL。這是堆棧跟踪的錯誤:

InvalidOperationException: Sequence contains more than one element
System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.<>c__DisplayClass79_0`1.<BindMemberExpression>b__0(IEnumerable`1 ps, IQuerySource qs)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.BindMemberExpressionCore[TResult](MemberExpression memberExpression, IQuerySource querySource, Func`3 memberBinder)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.BindMemberExpression[TResult](MemberExpression memberExpression, IQuerySource querySource, Func`3 memberBinder)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.BindMemberExpression(MemberExpression memberExpression, Action`2 memberBinder)
Microsoft.Data.Entity.Query.ExpressionVisitors.Internal.RequiresMaterializationExpressionVisitor.VisitMember(MemberExpression memberExpression)
System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
Microsoft.Data.Entity.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression expression)
Remotion.Linq.Clauses.ResultOperators.ContainsResultOperator.TransformExpressions(Func`2 transformation)
Remotion.Linq.QueryModel.TransformExpressions(Func`2 transformation)
Microsoft.Data.Entity.Query.ExpressionVisitors.Internal.RequiresMaterializationExpressionVisitor.VisitSubQuery(SubQueryExpression subQueryExpression)
Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor)
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
2000 
Microsoft.Data.Entity.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression expression)
Remotion.Linq.Clauses.WhereClause.TransformExpressions(Func`2 transformation)
Remotion.Linq.QueryModel.TransformExpressions(Func`2 transformation)
Microsoft.Data.Entity.Query.ExpressionVisitors.Internal.RequiresMaterializationExpressionVisitor.FindQuerySourcesRequiringMaterialization(QueryModel queryModel)
Microsoft.Data.Entity.Query.QueryCompilationContext.FindQuerySourcesRequiringMaterialization(EntityQueryModelVisitor queryModelVisitor, QueryModel queryModel)
Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
Microsoft.Data.Entity.Storage.Database.CompileAsyncQuery[TResult](QueryModel queryModel)
Microsoft.Data.Entity.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileAsyncQuery>b__0()
Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func`1 compiler)
Microsoft.Data.Entity.Query.Internal.QueryCompiler.CompileAsyncQuery[TResult](Expression query)
Microsoft.Data.Entity.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
Microsoft.Data.Entity.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
Microsoft.Data.Entity.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

難道我做錯了什麼?有沒有更好的方法來實現我想要做的事情?這似乎是一個相當簡單的用例,所以我想必須有一些方法來完成這項工作......

一般承認的答案

我最終收到了關於我在EF Core GitHub上發布的問題的回复:

https://github.com/aspnet/EntityFramework/issues/5413

此問題是RC1中的一個錯誤,現在已在RC2中修復。我已經遷移到RC2,現在看起來都很好。


熱門答案

嘗試這個:

DbContext.UploadPackages.Where(up => userIds.Contains(up.UserId)).SelectMany(u => u.Clips).CountAsync();

如果您熱切地說出您想要的導航屬性,則不應包括在內



Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow