LINQ extension method help sought

Question

Hi all,

this is by far my toughest question yet and I'm hoping someone has stumbled upon this issue before and found an elegant answer. Basically, I've got a few linq extension methods (which just happen to be in subsonic but would be applicable in any linq derivative) that are working perfectly (extensions for .WhereIn() and .WhereNotIn()). these methods operate to transform the linq to the sql equivalents of in(). Now the code below works perfectly when supplying known typed parameters (i.e. an array or params array):

public static IQueryable<T> WhereIn<T, TValue>(
    this IQueryable<T> query, 
    Expression<Func<T, TValue>> selector, 
    params TValue[] collection) where T : class
{
    if (selector == null) throw new ArgumentNullException("selector");
    if (collection == null) throw new ArgumentNullException("collection");
    ParameterExpression p = selector.Parameters.Single();

    if (!collection.Any()) return query;

    IEnumerable<Expression> equals = collection.Select(value =>
       (Expression)Expression.Equal(selector.Body,
            Expression.Constant(value, typeof(TValue))));

    Expression body = equals.Aggregate(Expression.Or);

    return query.Where(Expression.Lambda<Func<T, bool>>(body, p));
}

usage:

var args = new [] { 1, 2, 3 };
var bookings = _repository.Find(r => r.id > 0).WhereIn(x => x.BookingTypeID, args);
// OR we could just as easily plug args in as 1,2,3 as it's defined as params
var bookings2 = _repository.Find(r => r.id > 0).WhereIn(x => x.BookingTypeID, 1,2,3,90);

However, now for the complicated part. I'd like to be able to pass an IQueryable object into an overload version of the above that accepts a second linq object as the parameter in order to achieve the equivalent of select * from table1 where table1.id in(select id from table2). here is the method signature that actually compiles ok but has the all important logic missing:

public static IQueryable<T> WhereIn<T, TValue, T2, TValue2>(
    this IQueryable<T> query, 
    Expression<Func<T, TValue>> selector, 
    T2 entity2, 
    Expression<Func<T2, TValue2>> selector2) where T : class
{
    if (selector == null) throw new ArgumentNullException("selector");
    if (selector2 == null) throw new ArgumentNullException("selector2");
    ParameterExpression p = selector.Parameters.Single();
    ParameterExpression p2 = selector2.Parameters.Single();

    /* this is the missing section */

    /* i'd like to see the final select generated as 
     *
     * select * from T where T.selector in(select T2.selector2 from T2)
     */


    return  null; 
    // this is just to allow it to compile - proper return value pending
}

usage:

var bookings = _repository.Find(r => r.BookingID>0)
                .WhereIn(x => x.BookingTypeID, new BookingType(), y => y.BookingTypeID);

am i barking up an non existent (expression) tree here :-) - or is this pretty do-able.

all the best - here's hoping.

jim

Answer 1

Why would you not just use a join?

var query = from x in table1
            join y in table2 on x.Id equals y.Id
            select x;

Or if there might be multiple y values for each x:

var query = from x in table1
            join z in table2.Select(y => y.Id).Distinct() on x.Id equals z
            select x;

I would expect queries like that to be well optimized in SQL databases.

Or if you really want to use Where:

var query = table1.Where(x => table2.Select(y => y.Id).Contains(x.Id));

I may be missing something bigger... or it could be that translating the above queries into extension methods is what you're looking for :)

Answer 2

i eventually opted for an extension method to achieve this but still isn't 100% sucessful.

I'll drop the actual full working code here at some point later, once i've integrated it with all my other options.