查询列表中每个元素的有效方法 [英] Efficient way to query each element of a list

问题描述

我必须遍历对象的集合(比方说ID)，并对每个对象执行特定的查询.例如:

I have to iterate through a collection of objects (let's say ID's), and execute a specific query for each of these objects. For example:

IEnumerable<int> ids = getIDs(); //[1,2,3,4...]

现在我有以下解决方案:

Right now I have this solution:

DBEntities db = new DBEntities();

var results =
    from a in db.TABLEA
    join b in db.TABLEB on a.id equals b.id
    join c in db.TABLEC on b.oid equals c.oid
    where ids.Contains(c.id)
    select a; 

但是请记住，ID列表比我要搜索的表小.话虽这么说，上面的解决方案似乎效率低下，因为当我想要相反的情况时，我正在针对较小的列表查找表的每个记录.我也不想遍历列表，一次只执行一个元素的查询.

but keep in mind that the list of IDs is smaller than the table where I am searching. That being said, the solution above seems inefficient, since I am looking for each record of my table against a smaller list, when I wanted the opposite. I also do not want to iterate through the list, and execute the query for one element at a time.

理想情况下，我想要这样的东西:

Ideally, I would want something like this:

DBEntities db = new DBEntities();
(some data structure) ids = getIDs();

var results =
    from a in db.TABLEA
    join b in db.TABLEB on a.id equals b.id
    join c in db.TABLEC on b.oid equals c.oid
    join i in ids on c.id equals i.id;

上面的(pseudo-)代码将在单个查询中迭代列表中的元素，在单个查询中进行迭代，并按列表中的每个元素执行过滤器.

The (pseudo-)code above would iterate my elements of the list, in a single query, doing so in a single query and performing my filter by each element of the list.

这是这样做的方法吗?如果是这样，实施此解决方案的适当数据结构是什么?如果没有，我有哪些选择?

Is this the way to do it? If so, what is the appropriate data structure to implement this solution? If not, which alternatives do I have?

推荐答案

如果这是linq2Sql(或Linq2Entites)，则唯一的选择与示例1相同.您不能将具有内存中的列表"的表联接".您必须使用Contains.它将转换为
Where c.id IN(2,3,4,5,...) SQL查询

If this is linq2Sql (or Linq2Entites) your only option is as in your example 1. You can not "join" a table with an in memory list. You have to use Contains. Which will be translated to an
Where c.id IN(2,3,4,5,...) SQL query

这篇关于查询列表中每个元素的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！