时间戳

首页 > 解决方案 > C# EF 数组在 LINQ to DB 中相交

c# - C# EF 数组在 LINQ to DB 中相交

问题描述

我有一个代码优先应用程序和一个表“通知”，其中“标签”列将标签存储在一个字符串中，用“;”分隔。在上下文中，我转换为 IEnumerable，反之亦然。插入和获取数据时一切正常，但在一项服务中，我动态构建过滤器，通过添加谓词，一个一个，并将最终谓词列表添加到查询。现在，我有一种情况，我想按标签过滤，例如，我想要所有带有标签“Tag1”和“Tag2”的通知。我尝试使用 Contains 和 Intersect，但由于无法翻译 LINQ 表达式，我经常遇到异常。有任何想法吗？谢谢。

语境：

    builder.Entity<Notification>().Property(x => x.Tags).HasConversion
                    (x => string.Join(';', x),
                    x => x.Split(';', StringSplitOptions.RemoveEmptyEntries)
);

服务：

var filter = PredicateBuilder.True<UserNotification>();
IEnumerable<string> tagsFilter = new List<string>() { "Tag1","Tag2" };
filter = filter.And(x => x.Notification.Tags != null); // this line works

// both these lines fail (they are here as alternatives, should give the same result)
filter = filter.And(x => x.Notification.Tags.Any(r => tagsFilter.Contains(r)));
filter = filter.And(x => x.Notification.Tags.Intersect(tagsFilter).Any());

错误是（在“Where”子句中“：

System.InvalidOperationException: The LINQ expression 'DbSet<UserNotification>
    .Join(
        outer: DbSet<Notification>, 
        inner: u => EF.Property<Nullable<long>>(u, "NotificationId"), 
        outerKeySelector: n => EF.Property<Nullable<long>>(n, "Id"), 
        innerKeySelector: (o, i) => new TransparentIdentifier<UserNotification, Notification>(
            Outer = o, 
            Inner = i
        ))
    .Where(u => True && __statuses_0
        .Contains(u.Outer.NotificationStatus) && __types_1
        .Contains(u.Inner.Type) && u.Inner.Tags != null && u.Inner.Tags
        .Any(r => __tags2_2.Contains(r)))' 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.

标签： c#entity-frameworklinqentity-framework-core

解决方案

您不能在服务器端使用 Contains() 或 Intersect()，因为 LINQ 无法在 SQL 中从字符串转换为 IEnumerable。改为使用按字符串字段过滤：

    var filter = PredicateBuilder.True<UserNotification>();
    IEnumerable<string> tagsFilter = new List<string>() { "Tag1","Tag2" };
    filter = filter.And(x => x.Notification.Tags != null); // this line works
    
    foreach (var tag in tagsFilter) {
        // search for tag with heading and trailing ',' to distinct tags 'ham' and 'hamburger'
        filter = filter.And(x => ("," + x.Notification.Tags + ",").Contains("," + tag + ","));
    }

推荐阅读