title: EF 7/Core: Getting count of records in child table
body: <p>I tried asking posing this question/problem in the issues section of the EF Core GitHub but didn't get any response, so I have to assume I was asking in the wrong place...</p>
  
  <p>I'm working with the EntityFramework.Core and EntityFramework.MicrosoftSqlServer 7.0.0-rc1-final packages, and I'm trying to do something through code that looks like the following SQL:</p>
  
  <pre><code>SELECT COUNT(*)
  FROM [UploadPackage] [up]
  INNER JOIN [RawClip] [rc] on [up].[Id] = [rc].[PackageId]
  WHERE [up].[UserId] IN (1,2,3)
  </code></pre>
  
  <p>I've tried the following:</p>
  
  <p>Option 1:</p>
  
  <pre><code>DbContext.UploadPackages.Include(up =&gt; up.Clips).Where(up =&gt; userIds.Contains(up.UserId)).SelectMany(u =&gt; u.Clips).CountAsync()
  </code></pre>
  
  <p>Option 2:</p>
  
  <pre><code>DbContext.RawClips.Include(rc =&gt; rc.Package).Where(rc =&gt; userIds.Contains(rc.Package.UserId)).CountAsync()
  </code></pre>
  
  <p>The relationships are configured using the Fluent API like so:</p>
  
  <pre><code>modelBuilder.Entity&lt;UploadPackage&gt;().HasMany(up =&gt; up.Clips).WithOne(rc =&gt; rc.Package);
  modelBuilder.Entity&lt;RawClip&gt;().HasOne(rc =&gt; rc.Package).WithMany(up =&gt; up.Clips);
  </code></pre>
  
  <p>Option 1 generates the following SQL:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>As you can see, it's joining the RawClip table to itself. On one side of the join, it's giving the table the alias "[up]" which seems to be a reference to UploadPackage, even though the table it's aliasing is actually the RawClip table. This ultimately throws an error because of the where clause trying to filter on the column "[up].[UserId]", which exists on the UploadPackage table, but not the RawClip table.</p>
  
  <p>Option 2 fails without generating any SQL. Here's the error with stack trace:</p>
  
  <pre><code>InvalidOperationException: Sequence contains more than one element
  System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
  Microsoft.Data.Entity.Query.EntityQueryModelVisitor.&lt;&gt;c__DisplayClass79_0`1.&lt;BindMemberExpression&gt;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.&lt;&gt;c__DisplayClass19_0`1.&lt;CompileAsyncQuery&gt;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)
  </code></pre>
  
  <p>Am I doing something wrong? Is there a better way to achieve what I'm trying to do? It seems like a fairly simple use case, so I imagine there has to be some way to make this work...</p>