Hiya,
a bit of a weird request for help/support/suggestions this time,
basically asking if a solution, made my Marc Gravell, for Linq to sql, can be easily adjusted so it will do the same for linq to llblgen.
The problem :
we use ODP.NET, and unfortunately, from time to time, have to match big sets of integers (representing ids) to match with our database tables, and for which Contains(), works pretty well.
However, Oracle has a limitation, that such a set can not contain over 1000 elements, otherwise the database will throw an exception. (argument limitation)
In some cases we can circumvent the problem, but alas, not always.
Of course we can rewrite those queries so they are (if needed) run in batches of (in our case) 1000 at the most, but some are deeply nested as subqueries, which makes rewriting them a pain in the, well, you know :-)
Marc Gravell came up with a rather elegant solution for linq to sql, in which the IQuerable is parsed for Contains, and then the necessary Expression modifications are made to make it run in chuncks, so the query itself (for us merely developers) hardly has to be changed.
Alas, naively applying his code doesn't work in llblgen, but part of me hopes that the actual necessary modification to make the magic work isn't that different.
As I am not (yet) capable enough to work confidently with Expressions, and lack the necesary insight in how llblgen translates its IQuerables, I hopefully turn to you! :-)
So, in short, is there a small modification possible to make this :
DataSource<SomeEntity> data;
List<long> ids;
var res = data.Where(x => ids.Contains(x.SomeId).ToList();
work without ORA-01795 if ids > 1000 elements ?
So that the only rewrite would have to be :
var res = data.InRange(x => x.SomeId, 1000, ids);
Thank you very much in advance,
cheers,
Bart
Marc's solution is : (or http://stackoverflow.com/questions/567963/linq-expression-to-return-property-value/568771#568771)
or :
public static class QueryableChunked
{
public static IEnumerable<T> InRange<T, TValue>(this IQueryable<T> source, Expression<Func<T, TValue>> selector, int blockSize, IEnumerable<TValue> values)
{
MethodInfo method = null;
foreach (MethodInfo tmp in typeof(Enumerable).GetMethods(BindingFlags.Public | BindingFlags.Static))
{
if (tmp.Name == "Contains" && tmp.IsGenericMethodDefinition && tmp.GetParameters().Length == 2)
{
method = tmp.MakeGenericMethod(typeof(TValue));
break;
}
}
if (method == null) throw new InvalidOperationException("Unable to locate Contains");
foreach (TValue[] block in values.GetBlocks(blockSize))
{
var row = System.Linq.Expressions.Expression.Parameter(typeof(T), "row");
var member = System.Linq.Expressions.Expression.Invoke(selector, row);
var keys = System.Linq.Expressions.Expression.Constant(block, typeof(TValue[]));
var predicate = System.Linq.Expressions.Expression.Call(method, keys, member);
var lambda = System.Linq.Expressions.Expression.Lambda<Func<T, bool>>(predicate, row);
foreach (T record in source.Where(lambda))
{
yield return record;
}
}
}
public static IEnumerable<T[]> GetBlocks<T>(this IEnumerable<T> source, int blockSize)
{
List<T> list = new List<T>(blockSize);
foreach (T item in source)
{
list.Add(item);
if (list.Count == blockSize)
{
yield return list.ToArray();
list.Clear();
}
}
if (list.Count > 0)
{
yield return list.ToArray();
}
}
}
