使用linq连接两个表，并填写它们的字典 [英] Join two tables using linq, and fill a Dictionary of them

问题描述

我一直在研究如何联接两个表(Data和DataValues，一对一)并填充类型为的字典.

I've been searching how to join two tables (Data and DataValues, one to many) and fill a dictionary of type .

Data的记录可能是数千个(例如500,000或更多)，每个Data可能具有10到20个DataValue，这使得它的查询量大得多，因此性能在这里真的很重要.

The records of Data(s) might be thousands (e.g. 500,000 or more) and each Data may have 10 to 20 DataValues which makes it a much heavier query, so the performance is really important here.

这是我编写的代码:

// Passed via the arguments, for example, sensorIDs would contain:
int[] sensorIDs = { 0, 1, 2, 3, 4, 5, 6, 17, 18 };
Dictionary<Data, List<DataValue>> dict = new Dictionary<Data, List<DataValue>>();

foreach (Data Data in dt.Datas)
{
    var dValues = from d in dt.Datas
                        join dV in dt.DataValues on d.DataID equals dV.DataID
                        where (SensorIDs.Contains(dV.SensorID))
                        select dV;
    dict.Add(Data, dValues.ToList<DataValue>());
}

但是这种方法存在严重的性能问题，执行时间很长. 不知道我是否需要使用SQL视图.有什么建议吗?

But this approach has a significant performance issue and takes a long time to execute. Not sure if I need to use SQL Views. any suggestions?

推荐答案

您正在查询太多次.您可以在一个查询中执行此操作.

You're querying way too many times. You can do this in one query.

var dict = (from d in dt.Datas
            join dV in dt.DataValues on d.DataID equals dv.DataID
            where SensorIDs.Contains(dv.SensorID)
            select new { d, dV }).ToDictionary(o => o.d, o => o.dV.ToList());

在foreach循环中，您正在获取所有Data，对于每个Data，您都在做相同的事情.

In your foreach loop, you are fetching all Data and for each of them, you are doing the same thing.

现在还不太清楚，但是我认为您只想加入SensorIDs数组中的DataValue.在这种情况下:

Now that wasn't very clear, but I think you want to join only the DataValues that are in the SensorIDs array. In this case:

var dict = (from d in dt.Datas
            let dV = (from dataValue in dt.DataValues
                      where SensorIDs.Contains(dataValue.SensorID) &&
                            dataValue.DataID = d.DataID
                      select dataValue)
            select new { d, dV }).ToDictionary(o => o.d, o => o.dV.ToList());

这篇关于使用linq连接两个表，并填写它们的字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！