LINQ查询以选择与对数组匹配的行

本文关键字：数组 查询 选择 LINQ | 更新日期: 2023-09-27 18:11:29

现在，我有一个名为TrainingPlan的类，它看起来像这样:

public class TrainingPlan
{
   public int WorkgroupId { get; set; }
   public int AreaId { get; set; }
}

我得到了这些实例的数组，并且需要从数据库加载匹配的训练计划。WorkgroupId和AreaId基本上构成了一个复合键。我现在要做的是像这样循环遍历每个TrainingPlan:

foreach (TrainingPlan plan in plans)
   LoadPlan(pid, plan.AreaId, plan.WorkgroupId);
然后，LoadPlan有一个LINQ查询来加载单个计划: 
var q = from tp in context.TPM_TRAININGPLAN.Include("TPM_TRAININGPLANSOLUTIONS")
        where tp.PROJECTID == pid && tp.AREAID == areaid &&
              tp.WORKGROUPID == workgroupid
              select tp;
return q.FirstOrDefault();

问题:

这可以工作，但是对于大量的计划来说速度很慢。我相信，如果我能一次执行一个LINQ查询来加载每个TPM_TRAININGPLAN，这可能会快得多。

我的问题:

给定一个TrainingPlan对象数组，我如何一次加载每个匹配的WorkgroupId/AreaId组合?这个查询应该转换成类似的SQL语法:

SELECT * FROM TPM_TRAININGPLANS
WHERE (AREAID, WORKGROUPID) IN ((1, 2), (3, 4), (5, 6), (7, 8));

LINQ查询以选择与对数组匹配的行

我使用Contains来运行一个类似于where-in的批量过滤器。我对你的设想做了一个粗略的估计。单个选择查询实际上比Contains运行得更快。我建议在您的终端上运行一个类似的测试，并绑定DB，看看您的结果如何。最好也看看它是如何扩展的。我在visual studio 2012中运行。net 4.0。我阻塞了ToList()调用，以解决潜在的延迟加载问题。

public class TrainingPlan
{
    public int WorkgroupId { get; set; }
    public int AreaId { get; set; }
    public TrainingPlan(int workGroupId, int areaId)
    {
        WorkgroupId = workGroupId;
        AreaId = areaId;
    }    
}
public class TrainingPlanComparer : IEqualityComparer<TrainingPlan>
{
    public bool Equals(TrainingPlan x, TrainingPlan y)
    {
        //Check whether the compared objects reference the same data. 
        if (x.WorkgroupId == y.WorkgroupId && x.AreaId == y.AreaId) 
            return true;
        return false;                        
    }
    public int GetHashCode(TrainingPlan trainingPlan)
    {            
        if (ReferenceEquals(trainingPlan, null)) 
            return 0;
        int wgHash = trainingPlan.WorkgroupId.GetHashCode();
        int aHash = trainingPlan.AreaId.GetHashCode();
        return wgHash ^ aHash;
    }
}

internal class Class1
{
    private static void Main()
    {
        var plans = new List<TrainingPlan>
            {
                new TrainingPlan(1, 2),
                new TrainingPlan(1, 3),
                new TrainingPlan(2, 1),
                new TrainingPlan(2, 2)
            };
        var filter = new List<TrainingPlan>
            {
                new TrainingPlan(1, 2),
                new TrainingPlan(1, 3),
            };
        Stopwatch resultTimer1 = new Stopwatch();
        resultTimer1.Start();
        var results = plans.Where(plan => filter.Contains(plan, new TrainingPlanComparer())).ToList();
        resultTimer1.Stop();
        Console.WriteLine("Elapsed Time for filtered result {0}", resultTimer1.Elapsed);
        Console.WriteLine("Result count: {0}",results.Count());
        foreach (var item in results)
        {
            Console.WriteLine("WorkGroup: {0}, Area: {1}",item.WorkgroupId, item.AreaId);
        }
        resultTimer1.Reset();
        resultTimer1.Start();
        var result1 = plans.Where(p => p.AreaId == filter[0].AreaId && p.WorkgroupId == filter[0].WorkgroupId).ToList();
        var result2 = plans.Where(p => p.AreaId == filter[1].AreaId && p.WorkgroupId == filter[1].WorkgroupId).ToList();
        resultTimer1.Stop();
        Console.WriteLine("Elapsed time for single query result: {0}",resultTimer1.Elapsed);//single query is faster
        Console.ReadLine();
    }
}

在我看来，使用Intersect()可能会以您想要的方式完成此操作。但是，我没有设置一个环境来测试我自己。

var q = (from tp in context.TPM_TRAININGPLAN.Include("TPM_TRAININGPLANSOLUTIONS")
        where pid == tp.PROJECTID
        select tp)
        .Intersect
        (from tp in context.TPM_TRAININGPLAN.Include("TPM_TRAININGPLANSOLUTIONS")
        where plans.Any(p => p.AreaID == tp.AREAID)
        select tp)
        .Intersect
        (from tp in context.TPM_TRAININGPLAN.Include("TPM_TRAININGPLANSOLUTIONS")
        where plans.Any(p => p.WorkgroupId == tp.WORKGROUPID)
        select tp);

我唯一担心的可能是，相交数据库可能会导致它在内存中加载比您想要的更多的记录，但我无法测试以确认是否存在这种情况。

相关文章：