I have a simple EF7 code-first model : Countries have States, States have Cities. Relations and inverse relations are defined so that navigation fields and collections can be traversed both ways.
I want to run a text search on all the cities : split the search words to search for each word one after another, have this search to target city names, state names and country names, and make this search inclusive (if I look for 'france germany francisco', cities from France, cities from Germany, and San Francisco will be included in search results).
To achieve that, I am building my search predicate with LinqKit's PredicateBuilder.
When I test the predicate building and running code upon a simple in-memory collection of entities, everything works as expected.
When I run it against the Cities DbSet in an Entity Framework 7 context, I have this weird exception (System.InvalidCastException 'System.Linq.Expressions.FieldExpression' to type 'System.Linq.Expressions.ParameterExpression').
I first thought it to be related with LinqKit, but it is actually related to using PredicateBuilder with EF7 when looking for fields involving 2 levels relations:
// simple snippet, full code is below.
// the part that causes the exception is when accessing c.State.Country.Name
predicate = predicate.Or(c =>
c.StateId > 0 &&
c.State.CountryId > 0 &&
c.State.Country.Name.Contains(word));
On the predicate building code, you will notice I build it at 3 levels :
- Target the text search on city names (ok with in-memory and DbSet contexts)
- Target the text search on state names (ok with in-memory and DbSet contexts)
- Target the text search on country names (ok with in-memory context but crashes on DbSet context).
The predicate building process obviously starts with a false init, then OR's with each word and each field target. Before posting here, I tried the following attempts, as read in previous questions related to something similar :
- Call LinqKit's
Expand()at various steps of the expression building (hint: it didn't help !) - Previously store the crashing expression in a local variable, and use that
Expressionvariable instead of the anonymous lambda (not any better) - Only use the expression involving the level 2 traversal (the one that crashes) and it still crashed
- Remove the
Name.Containspart of the level 2 traversal, but keeping the level 2 traversal for Id's. In this case it does not crash (but of course the text lookup is not done).
Here is a full reproduction code (creates a localDb database, seeds it, then runs the crashing code).
It is a .Net 5 console package project, and you will need to add the following NuGet the packages to the project.json file :
"dependencies": {
"LinqKit": "1.1.3.1",
"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.Core": "7.0.0-rc1-final",
"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final"
}
C# code of the console app :
using System;
using System.Collections.Generic;
using System.Linq;
using LinqKit;
using Microsoft.Data.Entity;
namespace LinqKitIssue
{
public abstract class BaseEntity
{
public int Id { get; set; }
public string Name { get; set; }
}
public class Country : BaseEntity
{
public virtual ICollection<State> States { get; set; } = new List<State>();
}
public class State : BaseEntity
{
public int CountryId { get; set; }
public virtual Country Country { get; set; }
public virtual ICollection<City> Cities { get; set; } = new List<City>();
}
public class City : BaseEntity
{
public int StateId { get; set; }
public virtual State State { get; set; }
public override string ToString() => $"{Name}, {State?.Name} ({State?.Country?.Name})";
}
// setup DbContext
public class MyContext : DbContext
{
public DbSet<City> Cities { get; set; }
public DbSet<State> States { get; set; }
public DbSet<Country> Countries { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
@"Server=(localdb)\mssqllocaldb;Database=LinqKitIssue;Trusted_Connection=True;MultipleActiveResultSets=true");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Country>().HasMany(c => c.States).WithOne(c => c.Country).HasForeignKey(s => s.CountryId);
modelBuilder.Entity<State>().HasMany(c => c.Cities).WithOne(c => c.State).HasForeignKey(c => c.StateId);
modelBuilder.Entity<City>().HasOne(f => f.State);
}
}
public class Program
{
public static void Main(string[] args)
{
// Seed with all cities
SeedData.Seed();
// search parameters
var searchInput = "los las british france";
var searchWords = searchInput.Split(' ');
// will be looking for each word, inclusively
var predicate = PredicateBuilder.False<City>();
// iterate to look for each word
foreach (var word in searchWords)
{
// Level 0 : look for the word at each level : city (ok with in-mem and ef)
predicate = predicate.Or(c => c.Name.Contains(word));
// Level 1 : then state (ok with in-mem and ef)
predicate = predicate.Or(c =>
c.StateId > 0 &&
c.State.Name.Contains(word));
// Level 2 : then country (ok with in-mem, crashes with ef)
predicate = predicate.Or(c =>
c.StateId > 0 &&
c.State.CountryId > 0 &&
c.State.Country.Name.Contains(word));
}
// apply
Console.WriteLine($"Search results for : '{searchInput}'");
using (var ctx = new MyContext())
{
var query = ctx.Cities.AsQueryable();
// includes
query = query.Include(c => c.State).ThenInclude(s => s.Country);
// search
var searchResults = query.Where(predicate).ToList();
searchResults.ForEach(Console.WriteLine);
}
}
}
public static class SeedData
{
public static void Seed()
{
using (var ctx = new MyContext())
{
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
// countries
var us = new Country { Name = "united states" };
var canada = new Country { Name = "canada" };
ctx.Countries.AddRange(us, canada);
ctx.SaveChanges();
// states
var california = new State { Name = "california", Country = us };
var nevada = new State { Name = "nevada", Country = us };
var quebec = new State { Name = "quebec", Country = canada };
var bc = new State { Name = "british columbia", Country = canada };
ctx.States.AddRange(california, nevada, quebec, bc);
ctx.SaveChanges();
// Cities
var sf = new City { Name = "san francisco", State = california };
var la = new City { Name = "los angeles", State = california };
var lv = new City { Name = "las vegas", State = nevada };
var mt = new City { Name = "montreal", State = quebec };
var vc = new City { Name = "vancouver", State = bc };
ctx.Cities.AddRange(sf, la, lv, mt, vc);
ctx.SaveChanges();
}
}
}
}
Below is the full stack trace (sorry for being in french, but I don't feel like translating it, and the french words are tranparent compared to english ones !).
System.InvalidCastException: Impossible d'effectuer un cast d'un objet de type 'System.Linq.Expressions.FieldExpression' en type 'System.Linq.Expressions.ParameterExpression'.
à Microsoft.Data.Entity.Query.ExpressionVisitors.Internal.IncludeExpressionVisitor.VisitMethodCall(MethodCallExpression expression)
à System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
à Microsoft.Data.Entity.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression expression)
à System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)
à System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
à Microsoft.Data.Entity.Query.ExpressionVisitors.Internal.IncludeExpressionVisitor.VisitMethodCall(MethodCallExpression expression)
à System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
à Microsoft.Data.Entity.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression expression)
à Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.IncludeNavigations(IncludeSpecification includeSpecification, Type resultType, LambdaExpression accessorLambda, Boolean querySourceRequiresTracking)
à Microsoft.Data.Entity.Query.EntityQueryModelVisitor.IncludeNavigations(QueryModel queryModel, IReadOnlyCollection`1 includeSpecifications)
à Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.IncludeNavigations(QueryModel queryModel, IReadOnlyCollection`1 includeSpecifications)
à Microsoft.Data.Entity.Query.EntityQueryModelVisitor.IncludeNavigations(QueryModel queryModel)
à Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
à Microsoft.Data.Entity.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
--- Fin de la trace de la pile à partir de l'emplacement précédent au niveau duquel l'exception a été levée ---
à Microsoft.Data.Entity.Query.Internal.QueryCompiler.<>c__DisplayClass18_0`1.<CompileQuery>b__0()
à Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
à Microsoft.Data.Entity.Query.Internal.QueryCompiler.CompileQuery[TResult](Expression query)
à Microsoft.Data.Entity.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
à Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
à Remotion.Linq.QueryableBase`1.GetEnumerator()
à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
à LinqKitIssue.Program.Main(String[] args) dans d:\documents\visual studio 2015\Projects\LinqKitIssue\src\LinqKitIssue\Program.cs:ligne 98
--- Fin de la trace de la pile à partir de l'emplacement précédent au niveau duquel l'exception a été levée ---
à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
à Microsoft.Dnx.Runtime.Common.EntryPointExecutor.Execute(Assembly assembly, String[] args, IServiceProvider serviceProvider)
à Microsoft.Dnx.ApplicationHost.Program.<>c__DisplayClass3_0.<ExecuteMain>b__0()
à System.Threading.Tasks.Task`1.InnerInvoke()
à System.Threading.Tasks.Task.Execute()
