具有匿名类型的有效linq包含

本文关键字：有效 linq 包含 类型 | 更新日期: 2023-09-27 18:07:11

我有一个匿名对象的集合，创建如下：

var srcCategories = srcSet.Categories.Select(c => new
{
     ApplicationId = c.IsGLobal ? (long?)null : c.App.Id,
     c.Name
});

请注意，这个集合不是来自我的数据上下文；它是从外部系统的输入中生成的。我需要将ApplicationId和Name与数据库中的实体进行映射。到目前为止，这是我成功实现它的唯一途径：

var trgCategoryIds =
    (from c in core.Domain.Categories.AsEnumerable()
     let ci = new { c.ApplicationId, c.Name }
     where srcCategories.Contains(ci)
     select c.Id)
    .ToArray();

但这需要我首先将整个Categories表拉入内存。我正在寻找一种更有效的方法来做到这一点，最好是在单个查询中。我已经尝试了以下所有选项，没有一个可以转换为sql:

// Removed .AsEnumerable()
var trgCategoryIds =
    (from c in core.Domain.Categories 
     let ci = new { c.ApplicationId, c.Name }
     where srcCategories.Contains(ci)
     select c.Id)
     .ToArray();
// Use .Any() instead of .Contains()
var trgCategoryIds =
    (from c in core.Domain.Categories
     where srcCategories.Any(s => s.ApplicationId == c.ApplicationId && s.Name == s.Name)
     select c.Id)
    .ToArray();
// Use Tuples instead of anon types
var srcCategories = srcSet.Categories.Select(c => Tuple.Create(...));
var trgCategoryIds =
    (from c in core.Domain.Categories
     let ci = Tuple.Create(c.ApplicationId, c.Name)
     where srcCategories.Contains(ci)
     select c.Id)
    .ToArray();

具有匿名类型的有效linq包含

您想要做的事情实际上是不可能的，因为首先没有简单的SQL。实际上，你想要：

select * from Catagories where (ApplicationID = 1 and Name = "Foo") 
                            or (ApplicationID = 2 and Name = "Bar") 
                            or (ApplicationID = 2345 and Name = "Fizbuzz")
                            or ...

据我所知，实体框架无法自动创建那种类型的查询。它可以通过将Contains()转换为IN (...)来处理单个测试，但当不能处理join时，就没有简单的SQL来处理扩展的IN。但是，您可以使用谓词生成器库来构造这种类型的OR查询。页面上的第二个示例应该正是您所需要的。

适用于您的用途：

var predicate = PredicateBuilder.False<Category>();
  foreach (var cat in srcCategories)
  {
    var temp = cat;
    predicate = predicate.Or (p => p.ApplicationId == temp.ApplicationId && p.Name == temp.Name);
  }
  return core.Domain.Categories.AsExpandable().Where (predicate);
}

如果两个Categories集合来自不同的数据库上下文，则无法将其中一个集合拉入内存。

如果它们共享一个数据库上下文，那么您要做的就是简单地连接两个表：

var query =
    from domainCat in srcCategories
    join sourceCat in srcSet.Categories
    on new { domainCat.ApplicationId, domainCat.Name } equals
        new { sourceCat.ApplicationId, sourceCat.Name }
    select sourceCat.Id;

用ks作为格式化类别(Id+"-"+Name(来改编以下代码怎么样。

using System;
using System.Linq;
using System.Data.Entity;
using System.Collections.Generic;
using System.Data.Entity.ModelConfiguration;
using System.Data.Objects.SqlClient;
namespace testef {
    //Model
    public class CObj {
        public CObj() {
        }
        public Int32 Id { get; set; }
        public String SomeCol { get; set; }
    }
    //Configuration for CObj
    public class CObjConfiguration : EntityTypeConfiguration<CObj> {
        public CObjConfiguration() {
            HasKey(x => x.Id);
        }
    }
    public class TestEFContext : DbContext {
        public IDbSet<CObj> objects { get; set; }
        public TestEFContext(String cs)
            : base(cs) {
            Database.SetInitializer<TestEFContext>(new DropCreateDatabaseAlways<TestEFContext>());
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Configurations.Add(new CObjConfiguration());
        }
    }
    class Program {
        static void Main(String[] args) {
            String cs = @"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True";                
            using (TestEFContext c = new TestEFContext(cs)) {
                c.objects.Add(new CObj { Id = 1, SomeCol = "c"});
                c.SaveChanges();
            }
            IEnumerable<String> ks = new List<String> { String.Format("{0,10}-c", 1) };
            foreach (var k in ks) {
                Console.WriteLine(k);
            }
            using (TestEFContext c = new TestEFContext(cs)) {
                var vs = from o in c.objects
                         where ks.Contains(SqlFunctions.StringConvert((Decimal?)o.Id, 10) + "-" + o.SomeCol)
                         select o;
                foreach (var v in vs) {
                    Console.WriteLine(v.Id);
                }
            }
        }
    }
}

var trgCategoryIds =
    (from c in core.Domain.Categories.AsEnumerable()
     where sourceCategories.Any(sc=> sc.ApplicationId == c.ApplicationId 
                     && sc.Name == c.Name)
     select c.Id)
    .ToArray();
相关文章：