Linq2Sql - Using a local collection as part of a sub query - "queries with local collections are not supported"

Question

Ok,

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.

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).

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();

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).

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?

Screen shot show you know I'm not crazy.

Thanks in advance for the help

Answer 1

I'm no expert on this, but LinqToSql works by building up an expression tree that is converted into an SQL query at the point of execution. This works fine if all your query can be converted to SQL. However, what you are doing is basically attempting to join your SQL query with a .NET object collection. The trouble is, this won't work as the join can't be translated into an SQL query. You are mixing two different things - LinqToSql and LinqToObjects. Calling ToList() on your LinqToSql enabled it to work as you are then back in the domain of LinqToObjects. Sorry, I'm afraid I don't know any way around this.

PS. Maybe see this question: Linq2Sql -> Searching the database against a local collection of values - Queries with local collections are not supported

Answer 2

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

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.

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));

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
                       };