All geek questions in one place

Linq2Sql - using a local collection as part of an auxiliary query - "queries with local collections are not supported"

Good,

The last time I posted this (last week), I did not describe the problem correctly. I created a sample of this problem. Querying local collections works fine if you use it as part of a basic query. The problem I find is its use with part of the subquery. For example.

It's pretty hard to describe without giving you guys a database or code diagram, but I will try my best. I am trying to execute my code with a single request in db. I do not want to tear it up and send some commands. This has some advantages, including avoiding a possible problem, which I will explain to the end of this.

I join some tables that are related. Of course, an attribute table (DataEventAttributes) describes attributes that are unique to a specific row in the main table (DataEvents).

When I request it without any local collection, everything works fine and very fast against my 20 gigabyte database. However, if I put the local set of values ​​in the part of the subquery that receives the results, I get "Queries with local collections are not supported"

It was quite difficult for me to reproduce in my code, so I will comment on it, and I can also make sure that you can follow what I do.

// 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() .Select(x = > x.ID) finalQuery, . -, . sql finalQuery. (show stopper) - , .ToList(), SQL- - , Google , ( , 10-100 ).

, , , , , , , . SQL- , .

, , , - , , , , .

, ?

, .

+3
2

AFAIK, LINQ to SQL. :

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

, , .

2. someSetOfIDs, 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:

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

, , . , ( ):

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

, LinqToSql , , SQL- . , SQL. , , SQL- .NET. , , SQL-. - LinqToSql LinqToObjects. ToList() LinqToSql , LinqToObjects. , , , .

PS. , : Linq2Sql → -

0

Source: https://habr.com/ru/post/1715011/

More articles:


All Articles