LINQ2SQL - 使用本地集合作为一个子查询的一部分 - "与当地馆藏查询，不支持" [英] Linq2Sql - Using a local collection as part of a sub query - "queries with local collections are not supported"

问题描述

好吧，

我最后一次张贴了这个（上周），我没有正确说明问题。我创建了这个问题的一个快速的样品。查询本地收藏工作正常与您正在使用它作为基本查询的一部分。我发现的问题是使用它与一个子查询的一部分。例如。

Last time I posted this (last week), I didn't describe the problem correctly. I have created a quick sample of this problem. Querying local collections work fine with you are using it as part of the base query. The problem I am finding is using it with part of a sub query. For example.

这是相当难不给你们一个数据库图表或代码图来描述，但我会尽我所能。我试图用一个查询到数据库来执行我的代码。我不希望有打破它和发送多个命令。做这样的说法有一定的优势，其中包括避免可能出现的问题，我会向本月底讲解。

This is fairly hard to describe without giving you guys a database diagram, or code diagram, but I will try my best. I am trying to execute my code with one query to the db. I don't want to have to break it down and send multiple commands. Doing it that way has some advantages, including a avoiding a possible problem which I will explain towards the end of this.

我加入一些表有关系。当然的属性（DataEventAttributes）表描述所特有的在主表（DataEvents）特定行的属性。

I am joining some tables that have a relationship. The attributes (DataEventAttributes) table of course describes attributes that are unique to a specific row in the main table (DataEvents).

当我查询它没有任何地方收集，东西做工精细，速度极快对我的20G的数据库。不过，如果我扔值的地方收集到的子查询得到的结果的一部分，我会得到与当地馆藏查询，不支持

When I query it without any local collection, thing work fine and extremely fast against my 20 gig database. However, if I throw a local collection of values into part of the sub query that gets the results, I will get the "Queries with local collections are not supported"

这是相当难受，在我的代码复制，所以我会评论它，以及我可以做你可以按照我在做什么。

This was fairly hard for me to reproduce in my code, so I will comment it as well as I can do you can follow what I am doing.

// gets the initial query and join. We actually only care about the ID in the end, but we use the joined data
        // to determine if a row needs to be pulled.
        var initialQuery = from dataEvent in DataEvent.GetByQueryExpression(context)
                                  join attribute in DataEventAttribute.GetByQueryExpression(context) on dataEvent.DataEventID
                                      equals attribute.DataEventID
                           select new
                           {
                               ID = dataEvent.DataEventID,
                               PluginID = dataEvent.DataOwnerID,
                               TimeStamp = dataEvent.DataTimeStamp,
                               DataEventKeyID = attribute.DataEventKeyID,
                               ValueString = attribute.ValueString,
                               ValueDecimal = attribute.ValueDecimal
                           };

        // list of some ids that we need to confirm exist in the initial query before the final query
        var someSetOfIDs = new List<int>() {1, 2, 3, 4, 5};

        // This is the local collection thats filtering out some results before I rebuild the entire result set in the final query
        // If you comment this line out, the finalQuery will execute just fine.
        // with this in place, the "Queries with local collections are not supported" error will come about.
        initialQuery = initialQuery.Where(x => x.DataEventKeyID == 1 && someSetOfIDs.Contains((int) x.ValueDecimal));

        // reusable query for the sub queries in the results -- not part of the problem, just part of the example
        var attributeBaseQuery = from attribute in DataEventAttribute.GetByQueryExpression(context) select attribute;

        // Builds the final result With the IDs from the initial query 
        // the group by is to remove any duplicates that may be in the collection.
        // the select key is getting the ID that i needed
        // the select ID is the ID of the first item that was grouped.
        // the contains compares the local dataEvent object with the ID table (checking to see if it exists)
        // the result is just an example of one item I can be pulling out of the database with the new type
        var finalQuery = from dataEvent in DataEvent.GetByQueryExpression(context)
                         where initialQuery.GroupBy(x => x).Select(x => x.Key).Select(x => x.ID).Contains(dataEvent.DataEventID)
                         select new
                                    {
                                        BasicData =
                                         attributeBaseQuery.Where(
                                         attrValue =>
                                         attrValue.DataEventID == dataEvent.DataEventID &&
                                         attrValue.DataEventKeyID == (short) DataEventTypesEnum.BasicData).FirstOrDefault().
                                         ValueString
                                    };

        var finalResult = finalQuery.Take(100).ToList();

在一个解决方案，我发现是做。选择后.ToList（）（X = > x.ID）在finalQuery，但副作用有两个底片。一个时，它首先将运行查询，并从数据库中获取的ID ..然后它必须通过这些结果返回给SQL Server作为参数给finalQuery。第二个主要的（显示塞）是，如果有从.ToList（）许多成果，SQL服务器将引发一些奇怪的错误消息，谷歌搜索显示，有很多参数传递（这将是有意义的，因为参数计数可能是数千人10-100s）。

The one solution I have found is to do a .ToList() after the .Select(x => x.ID) in the finalQuery, but the side effect has two negatives. One, it runs that query first, and gets the IDs from the database.. then it has to pass those results back to the sql server as parameters to the finalQuery. The second major (show stopper), is that if there are to many results from the .ToList(), SQL server will throw some strange error message and Google searches show that there are to many parameters being passed (which would make sense, because the parameter count could be in the 10-100s of thousands).

所以，这么说，我试图找出如何建立一个查询，我可以调整标准动态，然后重建我的结果集的所有匹配满足子查询条件的ID属性。在通过工作室的SQL服务器，这工作正常，但集合问题有我在抢劫！

So, that said, I am trying to figure out how to build a query that I can adjust the criteria dynamically, and then rebuild my result sets with all the attributes that match the ID that meets the criteria of the sub query. In SQL server via the studio, this works fine, but the collection issue has me on a holdup.

我已经尝试了许多不同的方法，但似乎只有这样才能重现这个是有一个使用本地集合的查询，然后使用该查询作为使用第一个查询过滤结果的另一个查询的一部分。

I have tried many different ways, but it seems the only way to reproduce this is to have a query that uses a local collection, and then use that query as part of another query that filters the results using the first query.

任何想法我怎么能做到这一点。

Any ideas how I can do this?

的的屏幕截图显示你知道我是不是疯了。

谢谢提前的帮助

推荐答案

据我所知，这是不可能使用内存中集合在LINQ to SQL查询。我能想到的两个可能的变通的：

AFAIK, it's not possible to use in-memory collections in LINQ to SQL queries. I can think of two possible work-arounds:

选项1：执行每个ID查询：

Option 1: Perform a query for each ID:

    var someSetOfIDs = new List<int>() {1, 2, 3, 4, 5};

    // queryPerID will have type IEnumerable<IQueryable<'a>>
    var queryPerID = from id in someSetOfIDs
                     select (
                       from dataEvent in DataEvent.GetByQueryExpression(context)
                       join attribute in DataEventAttribute.GetByQueryExpression(context)
                         on dataEvent.DataEventID
                                  equals attribute.DataEventID
                       where attribute.DataEventKeyID == 1
                               && (int)attribute.ValueDecimal == id // Changed from Contains
                       select new
                       {
                           ID = dataEvent.DataEventID,
                           PluginID = dataEvent.DataOwnerID,
                           TimeStamp = dataEvent.DataTimeStamp,
                           DataEventKeyID = attribute.DataEventKeyID,
                           ValueString = attribute.ValueString,
                           ValueDecimal = attribute.ValueDecimal
                       });

    // For each of those queries, we an equivalent final queryable
    var res = from initialQuery in queryPerID
              select (
                  from dataEvent in DataEvent.GetByQueryExpression(context)
                  where initialQuery.GroupBy(x => x).Select(x => x.Key.ID).Contains(dataEvent.DataEventID)
                  select new
                  {
                      BasicData =
                          attributeBaseQuery.Where(
                          attrValue =>
                              attrValue.DataEventID == dataEvent.DataEventID &&
                              attrValue.DataEventKeyID == (short) DataEventTypesEnum.BasicData).FirstOrDefault().
                              ValueString
                  }) into finalQuery
              from x in finalQuery
              select x;

    var finalResult = finalQuery.Take(100).ToList();

我不知道，如果连编译，但它应该是八九不离十。

I'm not sure if that even compiles, but it should be pretty close.

选项2：建立从 someSetOfIDs A谓词表达式转嫁到SQL

Option 2: Build a predicate expression from someSetOfIDs to pass on to SQL.

        var someSetOfIDs = new List<decimal>() { 1, 2, 3, 4, 5 };

        Expression<Func<DataEventAttribute, bool>> seed = x => false;
        var predicate = someSetOfIDs.Aggregate(seed,
            (e, i) => Expression.Lambda<Func<DataEventAttribute, bool>>(
                Expression.OrElse(
                    Expression.Equal(
                        Expression.Property(
                            e.Parameters[0],
                            "ValueDecimal"),
                        Expression.Constant(i)),
                    e.Body),
                e.Parameters));

从本质上讲，我们已经建立了一个WHERE子句：

Essentially we've built a where clause:

x => ((x.ValueDecimal = 5) || ((x.ValueDecimal = 4) || ((x.ValueDecimal = 3) ||
((x.ValueDecimal = 2) || ((x.ValueDecimal = 1) || False)))))

要注意，这种方法是行不通的这一点很重要匿名类型，所以你将不得不使用谓词上可查询一个叫类型。这不是一个问题，如果你重新组织一个位（并可能会产生更好的查询计划，实际上）：

It's important to note that this approach won't work with anonymous types, so you would have to use the predicate on a queryable with a named type. This isn't an issue if you reorganize a bit (and might produce a better query plan, actually):

    var attributes = DataEventAttribute.GetByQueryExpression(context)
                     .Where(a => a.DataEventKeyID ==1)
                     .Where(predicate);

    var initialQuery = from dataEvent in DataEvent.GetByQueryExpression(context)
                       join attribute in attributes
                       select new
                       {
                           ID = dataEvent.DataEventID,
                           PluginID = dataEvent.DataOwnerID,
                           TimeStamp = dataEvent.DataTimeStamp,
                           DataEventKeyID = attribute.DataEventKeyID,
                           ValueString = attribute.ValueString,
                           ValueDecimal = attribute.ValueDecimal
                       };

这篇关于LINQ2SQL - 使用本地集合作为一个子查询的一部分 - "与当地馆藏查询，不支持"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！