Fetching items where nested collection contains item(s) form another collection

.net c# entity-framework entity-framework-core linq

Question

I'm trying to fetch item where it's nested collection contains item or multiple items from another collection. If I first enumerate the collection and then execute an in-memory .Where() filter, I get the expected collection.

var filterTagIds = new[] { 1, 2, 3 };

var works = base.DbSet
    .Include(p => p.Company)
        .ThenInclude(p => p.CompanyTags)
    .ToList()
    .Where(p => p.Company.CompanyTags.Any(q => filterTagIds.Contains(q.TagId)))
    .ToList();

However if I try to do it without the enumeration (I would like to do it on the DB level), I get an exception.

var doesNotWork = base.DbSet
    .Include(p => p.Company)
        .ThenInclude(p => p.CompanyTag)
    .Where(p => p.Company.CompanyTags.Any(q => filterTagIds.Contains(q.TagId)))
    .ToList();

This is the exception that I'm getting:

{"variable 't0' of type 'Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier`2[Microsoft.Data.Entity.Storage.ValueBuffer,Microsoft.Data.Entity.Storage.ValueBuffer]' referenced from scope '', but it is not defined"}
    Data: {System.Collections.ListDictionaryInternal}
    HResult: -2146233079
    HelpLink: null
    InnerException: null
    Message: "variable 't0' of type 'Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier`2[Microsoft.Data.Entity.Storage.ValueBuffer,Microsoft.Data.Entity.Storage.ValueBuffer]' referenced from scope '', but it is not defined"
    Source: "System.Core"
    StackTrace: "   at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)\r\n   at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)\r\n   at System.Linq.Expressions.ParameterExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)\r\n   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.Compiler.VariableBinder.VisitUnary(UnaryExpression node)\r\n   at System.Linq.Expressions.UnaryEx
pression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)\r\n   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)\r\n   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expressions.MethodCallExp
ression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)\r\n   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)\r\n   at System.Linq.Expressions.Expres
sion`1.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)\r\n   at System.Linq.Expression
s.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)\r\n   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)\r\n   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at System.Linq.Expressions.Compiler.VariableBinder.Bind(LambdaExpression lambda)\r\n   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)\r\n   at System.Linq.Expressions.Expression`1.Compile()\r\n   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateExecutorLambda[TResults]()\r\n   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)\r\n   at Microsoft.Data.Entity.Storage.Database.CompileQuery[TResult](QueryModel quer
yModel)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.<>c__DisplayClass18_0`1.<CompileQuery>b__0()\r\n   at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.CompileQuery[TResult](Expression query)\r\n   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.Execute[TResult](Expression query)\r\n   at Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)\r\n   at Remotion.Linq.QueryableBase`1.GetEnumerator()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at MyProject.DAL.Repository.LocationRepository.GetCompanyLocations(CompanyMarkersFilter filter) in D:\\projects\\MyProject\\src\\Komp
anio.DAL\\Repository\\LocationRepository.cs:line 24"
    TargetSite: {Void Reference(System.Linq.Expressions.ParameterExpression, System.Linq.Expressions.Compiler.VariableStorageKind)}

Is what I'm looking for doable in EF? Perhaps it's just an issue with EF-Core? Or am I doing something wrong? Thanks.

1
2
2/7/2016 2:57:47 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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

Known issue, set to be fixed with release version of EF.

2
2/23/2016 3:38:07 PM

Related Questions

How to select item with specific item in iCollection property?

You are calling ...ToList()... 2 times. When you call ToList in your second line, It executes the query expression and load the results to the lps variable. You are doing the filter on the inmemory data in the next line....You can avoid the first ...ToList()... call which executes the query expression and merge the filtering code to same line....va...
c# entity-framework entity-framework-6
asked by Shyju

deleting some items from a child collection in ef core

With ...RemoveRange..., where you will save time is in the overhead around removing the objects from the child collection, especially around change tracking which will run once rather than once for each object with ...Remove.......Either way, you will only make one trip to the database when you call ...SaveChanges.......So, in your example, ...Remo...
c# entity-framework-core
asked by Matthew Steven Monkan

Entity Framework Include Nest collection and nested properties

I have 4 classes...public class Customer { public string CustomerName { get; set; } public ICollection<Order> Orders{ get; set; } } public class Order { public string OrderNumber { get; set; } public ICollection<OrderLine> OrderLines { get; set; } public OrderType Type { get; set; } } public class OrderLine { public string...
c# entity-framework entity-framework-core
asked by user2859298

Entity Framework Core, deleting items from nested collection

That is because the rows in the database are not marked for deletion....Only new or changed items are updated. 'Missing' items from a collection are not considered to be deleted....So what you'll need to do is mark the items for deletion yourself. Something like this:...public void Update(Invoice record) { var missingRows = dB.InvoiceRows.Where...
c# entity-framework entity-framework-core
asked by Ruard van Elburg

Query items inside collection inside collection

I assume I somehow need to get "1 level deeper" than just the IndividualMatch that I've included there....That's correct....How do I accomplish this?...The answer depends on what ...Entity Framework... ae you targeting - EF6 or EF Core, because they use different mechanisms for including multiple levels of related data. That's why it's important to...
asp.net c# entity-framework entity-framework-core linq
asked by Ivan Stoev

EFCore 2.0 - Include & ThenInclude : child collection -- grandchild collection

Just ...type... the navigation property name:....ThenInclude(c => c.GrandChildren) ...It's a current Intellisense issue specifically mentioned in the ...Including multiple levels ... section of the EF Core documentation:...Note...Current versions of Visual Studio offer incorrect code completion options and can cause correct expressions to be flagge...
c# entity-framework-core
asked by Ivan Stoev

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

Have you tried the Entity Framework Core repo? ...https://github.com/AutoMapper/AutoMapper.Collection.EFCore/tree/development
asp.net-core aspnetboilerplate automapper collections entity-framework-core
asked by Guo Huang

How to model a collection of items where one of them is the active item?

Using EFCore, the following would work:...public class Customer { [Key] public int ID {get; set;} //other properties //Navigation Property public virtual ICollection<Application> Applications{ get; set; } } public class Application { [Key] public int ID {get; set;} [ForeignKey("Customer")] public int CustomerID...
c# domain-driven-design entity-framework-core
asked by el_M

Adding item to collection navigation property in EF Core

Please write your code as follows:...public void AddProjectManager(int projectID, AppUser user) { Project proj = context.Projects.Include(p => p.ProjectManagers) .FirstOrDefault(p => p.ProjectID == projectID); if(!proj.ProjectManagers.Any(pm => pm.Id = user.Id)) // <-- Here check that `AppUser` already...
asp.net-core c# entity-framework-core model-view-controller
asked by TanvirArjel

Include property of each item in a collection in EF Core

It looks like... this is how you're supposed to use ...ThenInclude...:...var mainObj = _db.MyEntityA.AsNoTracking() .Include(e => e.MyEntityB) .Include(e => e.CollectionOfEntityC) .ThenInclude(ce => ce.MyEntityD);
.net-core c# entity-framework-core
asked by StriplingWarrior

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