EF Core: The LINQ expression could not be translated for a nested List with Generic StartsWith() Expression

Question

I'm trying to build kind of a generic "StartsWith" Expression in a nested List that called 'OtherListEntities'. The managed entity looks like this:

public class MyEntity
    {
        [System.ComponentModel.DataAnnotations.Key] // key just for the sake of having a key defined, not relevant for the question
        public string TopLevelString { get; set; }
        public IList<AnotherEntity> OtherListEntities { get; set; }
    }
    public class AnotherEntity
    {
        [System.ComponentModel.DataAnnotations.Key]  // key just for the sake of having a key defined, not relevant for the question
        public string NestedString { get; set; }
    }

I put it into a context like this:

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }
    public MyDbContext(DbContextOptions<MyDbContext> options) {}
    protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlite("Data Source=sqlitedemo.db");
}

And try to use this context within a test class:

public partial class MyTestClass
    {
        private List<MyEntity> testExampleList = new List<MyEntity>()
        {
            new MyEntity()
            {
                TopLevelString = "ABC",
                OtherListEntities = new List<AnotherEntity>()
                {
                    new AnotherEntity(){ NestedString = "ASD"},
                    new AnotherEntity(){ NestedString = "DEF"},
                    new AnotherEntity(){ NestedString = "GHI"},
                }
            },
            new MyEntity()
            {
                TopLevelString = "XYZ",
                OtherListEntities = new List<AnotherEntity>()
                {
                    new AnotherEntity(){ NestedString = "asd"},
                    new AnotherEntity(){ NestedString = "XXX"},
                    new AnotherEntity(){ NestedString = "FHJ"},
                }
            }
        };

        MyDbContext context;
        public MyTestClass()
        {
            var options = new DbContextOptions<MyDbContext>();
            this.context = new MyDbContext(options);

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            this.context.MyEntities.AddRange(testExampleList);
            this.context.SaveChanges();
        }
    }

Here's the thing I'd like to do as a plain Where filter:

 public partial class MyTestClass
    {
        [Fact]
        public void TestFilteringWithoutOwnExpression()
        {
            Assert.Equal(1, context.MyEntities.Where(x => x.TopLevelString.StartsWith("A")).Count()); // works fine
            Assert.Equal(1, context.MyEntities.Where(x => x.OtherListEntities.Where(e => e.NestedString.StartsWith("XXX")).Any()).Count());
        }
    }

Since there's some other magic supposed to happen before the actual where clause is applied, I tried to wrap it into an own expression like this:

public partial class MyTestClass
    {
        [Fact]
        public void TestFilteringWithExpression()
        {
            Assert.Equal(1, context.MyEntities.MyWhere<MyEntity>(x => x.TopLevelString, "A").Count()); // works
        }
        [Fact]
        public void TestFilteringWithExpressionAny()
        {
            Assert.Equal(1, context.MyEntities.Where(x => x.OtherListEntities.AsQueryable().MyAny(e => e.NestedString, "XXX")).Count()); // doesn't work, why?
        }
    }

with MyWhere() and MyAny() being defined in an extension class:

public static class IQueryableExtension
    {
        public static IQueryable<TEntity> MyWhere<TEntity>(this IQueryable<TEntity> query, Expression<Func<TEntity, string>> stringSelector, string searchString)
        {
            ParameterExpression entityParameter = stringSelector.Parameters.First(); //Expression.Parameter(typeof(TEntity), stringSelector.Parameters.First().Name);
            MemberExpression memberExpr = (MemberExpression)(stringSelector.Body);
            var searchConstant = Expression.Constant(searchString, typeof(string));

            var filterExpression = Expression.Lambda<Func<TEntity, bool>>(
                                             Expression.Call(
                                                memberExpr,
                                                typeof(string).GetMethod(nameof(string.StartsWith), new Type[] { typeof(string) }),
                                                searchConstant),
                                             entityParameter);
            query = query.Where(filterExpression);
            return query;
        }
        public static bool MyAny<TEntity>(this IQueryable<TEntity> query, Expression<Func<TEntity, string>> stringSelector, string searchString)
        {
            return query.MyWhere(stringSelector, searchString).Any();
        }
    }

The code fails with an InvalidOperationException:

The LINQ expression 'DbSet .Where(m => DbSet .Where(a => EF.Property(m, "TopLevelString") != null && EF.Property(m, "TopLevelString") == EF.Property(a, "MyEntityTopLevelString")) .MyAny( query: e => e.NestedString, stringSelector: "XXX"))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

How do I setup a generic and translatable StartsWith Expression in a nested list?

You can not use "custom" methods inside EF core query unless you've [explained](https://www.thinktecture.com/en/entity-framework-core/custom-functions-using-imethodcalltranslator-in-2-1/) how to translate it to SQL. Or you need to generate whole expression for `Where` clause. — Guru Stron, Jul 15 '20 at 09:02
@GuruStron as I menthend it on unnittest MyWhere() works fine for TopLevelString, but a nested List property needs to have .any() to compare its items with searchString! — Hamid Hajiparvaneh, Jul 15 '20 at 09:48
Because you are not using it inside the expression, so EF does not need to translate `MyWhere` into SQL, only `filterExpression`. — Guru Stron, Jul 15 '20 at 09:57

score 0 · Answer 1 · answered Jul 15 '20 at 09:47

If all you need is a count of your MyEntities that have a AnotherEntity that starts with XXX, you could simply write:

Assert.Equal(1, context.MyEntities.SelectMany(x => x.OtherListEntities).Where(o => o.NestedString.StartsWith("XXX")).Count();

To understand why your extension method doesn't work see this answer.

EF Core: The LINQ expression could not be translated for a nested List with Generic StartsWith() Expression

1 Answers1