c# - EF Core：无法为具有通用 StartsWith() 表达式的嵌套列表翻译 LINQ 表达式

Question

我正在尝试在名为“ OtherListEntities ”的嵌套列表中构建一种通用的“StartsWith”表达式。托管实体如下所示：

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; }
    }

我把它放在这样的上下文中：

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");
}

并尝试在测试类中使用此上下文：

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();
        }
    }

这是我想做的简单的 Where 过滤器：

 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());
        }
    }

由于在应用实际的 where 子句之前应该发生一些其他的魔法，我试图将它包装成一个自己的表达式，如下所示：

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?
        }
    }

MyWhere() 和 MyAny() 在扩展类中定义：

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();
        }
    }

代码失败并出现 InvalidOperationException：

LINQ 表达式 '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"))' 无法翻译。以可翻译的形式重写查询，或通过插入对 AsEnumerable()、AsAsyncEnumerable()、ToList() 或 ToListAsync() 的调用显式切换到客户端评估。有关详细信息，请参阅 https://go.microsoft.com/fwlink/?linkid=2101038。

如何在嵌套列表中设置通用且可翻译的 StartsWith 表达式？

标签： c#entity-frameworklinq.net-coresql-to-linq-conversion

Answer 1

如果您MyEntities只需要一个AnotherEntity以 开头的计数XXX，您可以简单地编写：

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

要了解为什么您的扩展方法不起作用，请参阅此答案。