Exception on join with 'constants'

[edwin]

Because .Contains is limited to 1000 values (because it translates to select ... from ... where ... in (1,2,3,...1000) and the list of values is limited by Oracle to 1000), I want to implement a hack, which I saw Microsoft(R) SharePoint uses. Instead of using a where, I want to join with a list of values, like this:

Code: Select all

select A.*
  from TableA as A
       join 
            (
              select 1 as Value
              union all
              select 2 as Value
              union all
              select 3 as Value
              union all
              ...
              union all
              select 9999 as Value
            ) as B
            on A.SomeField = B.Value

For this, I created the following extension method:

Code: Select all

        /// 
        /// Returns the records of the given iq which have the tableField set to the one of the values of the given selection."selectionFieldSelector"
        /// 
        /// 
        /// 
        /// 
        /// The records of which to return a subset
        /// The field to filter on
        /// The filter values
        /// The selector for the field of the filter values
        /// 
        public static IQueryable InPro(this IQueryable iq
                                          , Expression> tableField
                                          , IList selection
                                          , Expression> selectionFieldSelector)
            where T : class
            where U : class
            //where V: class
        {
            IQueryable result = iq;
            List ids = selection.Select(selectionFieldSelector.Compile()).ToList();
            if (ids.Count == 0) return result.Where(x => false);
            result = result.Join(ids, tableField, y => y, (a,b) => a);
            return result;
        }

which I call in:

Code: Select all

        private List AuxFillRegelsVertrekdeuren(List routedagen)
        {
            return ((AppDataContext)_controller.GetDataContext()).Vertrekdeurs
                .InPro(x=>x.RouteDag, routedagen, y=>y.Id)
                .Cast().ToList();
        }

But unfortunately this throws an Exception:

System.NotSupportedException: Constant cannot be sequences.
bij Devart.Data.Linq.Provider.Query.u.a(SqlNode A_0)
bij Devart.Data.Linq.Provider.Query.u.a(Expression A_0)
bij Devart.Data.Linq.Provider.Query.u.a(Expression A_0, Expression A_1, LambdaExpression A_2, LambdaExpression A_3, LambdaExpression A_4)
bij Devart.Data.Linq.Provider.Query.u.b(MethodCallExpression A_0)
bij Devart.Data.Linq.Provider.Query.u.j(Expression A_0)
bij Devart.Data.Linq.Provider.Query.u.a(Expression A_0)
bij Devart.Data.Linq.Provider.Query.u.d(Expression A_0, Expression A_1)
bij Devart.Data.Linq.Provider.Query.u.b(MethodCallExpression A_0)
bij Devart.Data.Linq.Provider.Query.u.j(Expression A_0)
bij Devart.Data.Linq.Provider.Query.u.a(Expression A_0, Type A_1)
bij Devart.Data.Linq.Provider.Query.u.b(MethodCallExpression A_0)
bij Devart.Data.Linq.Provider.Query.u.j(Expression A_0)
bij Devart.Data.Linq.Provider.Query.u.i(Expression A_0)
bij Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)
bij Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Compile(Expression query)
bij Devart.Data.Linq.DataQuery`1.i()
bij System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
bij System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
bij Jumbo.Juist.Models.Overzicht.AuxFillRegelsVertrekdeuren(List`1 routedagen) in D:\Projects\JUIST\Juist\Juist\Models\Overzicht.cs:regel 458
[...]

Is this by design or a bug? And why shouldn't this just work, because it can easily be converted to the SQL which I showed.

A less then ideal alternative I use today is partitioning by 1000 and using .Skip(n*1000).Take(1000). Any better alternatives?

Kind regards,

Edwin.

[StanislavK]

LinqConnect does not allow using joins with local collections, only database result sets can be joined. For example, LINQ to SQL behaves in the same way.

We recommend to split the collection into several parts instead. In particular, this approach should be more efficient than a join with multiple unions. If you are encountering any problems with this, please describe them.

Also, we will consider performing this partitioning automatically. We will post here when this functionality is completed.

[edwin]

Thanks for your reply.

For those facing the same problem, here is how we solved it:

Code: Select all

        /// 
        /// Returns the records of the given table with the given Id's. 
        /// Replacement for Contains()
        /// 
        /// The 'table' to filter
        /// A selectorexpression to select the field 
        /// to filter on
        /// A list of records used to filter by
        /// A selector to select a field
        /// of the selection
        /// 
        public static List In(this IQueryable table
                                       , Expression> tableFieldSelector
                                       , IList selection
                                       , Func selectionFieldSelector)
            where T : class
            //where U: class            
        {
            List result = new List();
            for (int i = 0; i  ids = selection.Skip(i * 1000).Take(1000)
                                     .Select(selectionFieldSelector).ToList();

                var results = AuxInComplicatedCode(table
                                , tableFieldSelector, ids);

                result.AddRange(results.ToList());
            }
            
            return result;
        }

        /// 
        /// Auxillary function, creates a query expression tree
        /// 
        private static IQueryable AuxInComplicatedCode(IQueryable table
             , Expression> tableFieldSelector
             , List ids) where T : class
        {
            ParameterExpression pe_x = Expression.Parameter(typeof(T), "x");
            ConstantExpression ce_ids = Expression.Constant(ids);
            MemberExpression me_field = Expression.PropertyOrField(pe_x
                  , ObjectUtils.GetMemberName(tableFieldSelector));
            MethodInfo mi_contains = typeof(List).GetMethod("Contains");
            MethodCallExpression mce_contains = Expression.Call(ce_ids
                             , mi_contains, me_field);

            MethodCallExpression whereCallExpression = Expression.Call(
                typeof(Queryable)
                , "Where"
                , new Type[] { table.ElementType }
                , table.Expression
                , Expression.Lambda>(mce_contains
                                  , new ParameterExpression[] { pe_x }));
            var results = table.Provider.CreateQuery(whereCallExpression);
            return results;
        }

        public static string GetMemberName(Expression> action)
        {
            var lambda = (LambdaExpression)action;
            if (lambda.Body is UnaryExpression)
            {
                var unary = (UnaryExpression)lambda.Body;
                var operand = unary.Operand;
                if (ExpressionType.MemberAccess == operand.NodeType)
                {
                    var memberExpr = (MemberExpression)operand;
                    return memberExpr.Member.Name;
                }
                else if (ExpressionType.Call == operand.NodeType)
                {
                    var methodExpr = (MethodCallExpression)operand;
                    return methodExpr.Method.Name;
                }
            }
            else
            {
                var memberExpr = (MemberExpression)lambda.Body;
                return memberExpr.Member.Name;
            }

            throw new InvalidOperationException();
        }

(Some code is borrowed from some examples of building Expression Trees).

Kind regards,

Edwin.

[StanislavK]

Thank you for sharing your solution.

As an alternative, you can prepare the WHERE clause with several 'or' conditions, e.g., in the following way:

Code: Select all

public static List In(
  this IQueryable table,
  Expression> tableFieldSelector,
  IList selection,
  Func selectionFieldSelector)
where T : class
{
  Expression fullCondition = null;
  ParameterExpression pe_x = Expression.Parameter(typeof(T), "x");
  for (int i = 0; i  ids = selection.Skip(i * 1000).Take(1000)
      .Select(selectionFieldSelector).ToList();

    var contains = PrepareContains(table, tableFieldSelector, ids, pe_x);
    fullCondition = i == 0 ? (Expression)contains : (Expression)Expression.Or(fullCondition, contains);
  }

  MethodCallExpression whereCallExpression = Expression.Call(
    typeof(Queryable),
    "Where",
    new Type[] { table.ElementType },
    table.Expression,
    Expression.Lambda>(fullCondition, new ParameterExpression[] {pe_x}
  ));

  var result = table.Provider.CreateQuery(whereCallExpression);

  return result.ToList();
}

private static MethodCallExpression PrepareContains(
  IQueryable table, 
  Expression> tableFieldSelector,
  List ids,
  ParameterExpression pe_x) where T : class {

  ConstantExpression ce_ids = Expression.Constant(ids);
  MemberExpression me_field = 
    Expression.PropertyOrField(pe_x, GetMemberName(tableFieldSelector));
  MethodInfo mi_contains = typeof(List).GetMethod("Contains");
  return Expression.Call(ce_ids, mi_contains, me_field);
}

[edwin]

Thanks, this is a more elegant solution.

I never thought this would be possible. Strange that an IN-list is limited to 1000 values, but you can use multiple lists, leading to the same result. Doesn't seem like a technical limitation then.

Altough probably not an issue for us, isn't there a limit for the maximum query size? Your solution could in theory lead to a query of several megabytes, altough I suspect that in all practical situations the limit won't be reaced.

[StanislavK]

According to the following matrices, the limitation is 64 kBs in Oracle 9.2, and there is no limitation since Oracle 10.1:
http://download.oracle.com/docs/cd/B105 ... htm#288033
http://download.oracle.com/docs/cd/B141 ... its003.htm

For more information on this, please refer to the Oracle documentation.

[StanislavK]

We have implemented splitting the 'IN' argument array into parts of 1000 elements in the SQL generated by LinqConnect. This change is available in the latest 3.0.5 build of LinqConnect, which can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial only) or from Registered Users' Area (for users with active subscription only).

For more information about the fixes and improvements available in LinqConnect 3.0.5, please refer to
http://www.devart.com/forums/viewtopic.php?t=22168