LinqToSql Select to a class then do more queries

Question

I have a LINQ query running with multiple joins and I want to pass it around as an IQueryable<T> and apply additional filters in other methods.

The problem is that I can't work out how to pass around a var data type and keep it strongly typed, and if I try to put it in my own class (EG: .Select((a,b) => new MyClass(a,b))) I get errors when I try to add later Where clauses because my class has no translations into SQL. Is there any way I can do one of the following:

1. Make my class map to SQL?
2. Make the var data-type implement an interface (So I can pass it round as though it's that)?
3. Something I haven't though of that'll solve my issue?

Example:

public void Main()
{
    using (DBDataContext context = new DBDataContext())
    {
      var result = context.TableAs.Join(
         context.TableBs,
         a => a.BID,
         b => b.ID,
        (a,b) => new {A = a, B = b}
      );
      result = addNeedValue(result, 4);
   }
}

private ???? addNeedValue(???? result, int value)
{
    return result.Where(r => r.A.Value == value);
}

PS: I know in my example I can flatten out the function easily, but in the real thing it'd be an absolute mess if I tried.

Answer 1

You are returning what's known as an 'anonymous type' from your join - it's not a specific database type, but is instead just an object containing A and B. Because the type is anonymous, I don't think you're going to be able to write a function that returns that type - or a collection of that type.

If it were a type that SQL / LINQ-to-SQL knew about then you'd probably be able to do this.

One way to get LINQ-to-SQL to recognize the type might be to create a stored procedure which performs the join and selects the relevant columns. If you add that stored procedure to your DBML file, LINQ-to-SQL will understand the type of each 'row' in the result. You should then be able to have your function return an IQueryable<ResultSetRow>.

Answer 2

All you have to do is de-anonymize your type that you're constructing as a result of the query - new { A = a, B = b } Just create a class with those properties and name it appropriately. Then your query will have a type of IQueryable<your type>

Like so:

public class MyClass 
{ 
    public int A { get; set; }
    public int B { get; set; }
}

public void Main()
{
    using (DBDataContext context = new DBDataContext())
    {
      var result = context.TableAs.Join(
         context.TableBs,
         a => a.BID,
         b => b.ID,
        (a,b) => new MyClass {A = a, B = b}
      );
      result = addNeedValue(result, 4);
   }
}

private IQueryable<MyClass> addNeedValue(IQueryable<MyClass> result, int value)
{
    return result.Where(r => r.A.Value == value);
}

Answer 3

I definitely don't recommend it, but you can probably use dynamic here, if you're on .NET 4.0.

Answer 4

Here's two different approaches; the first applies the filter before doing the Join, since the joined queries don't have to just be basic tables. The second approach applies the filter after the join, using an intermediary projection (and changing it to return the predicate, rather than applying it internally).

This has been tested successfully on .NET 3.5 and .NET 4; note that in 3.5 (SP1) the Expression.Invoke (for the second example) won't work on EF, but is fine for LINQ-to-SQL.

If you want to run the example, I've used Northwind (just because that is what I had locally):

using System;
using System.Linq;
using System.Linq.Expressions;
using ConsoleApplication1; // my data-context's namespace
static class Program
{
    public static void Main()
    {
        using (var context = new TestDataContext())
        {
            context.Log = Console.Out; // to check it has worked
            IQueryable<Order> lhs = context.Orders;
            IQueryable<Order_Detail> rhs = context.Order_Details;
            // how ever many predicates etc here
            rhs = addBeforeJoin(rhs, 4);

            var result = lhs.Join(rhs,
                   a => a.OrderID,
                   b => b.OrderID,
                  (a, b) => new { A = a, B = b }
            );
            // or add after
            result = result.Where(row => row.B, addAfterJoin(100));
            Console.WriteLine(result.Count());
        }
    }

    private static IQueryable<Order_Detail> addBeforeJoin(IQueryable<Order_Detail> query, int value)
    {
        return query.Where(r => r.Quantity >= value);
    }
    private static Expression<Func<Order_Detail, bool>> addAfterJoin(int value)
    {
        return r => r.Quantity <= value;
    }
    private static IQueryable<TSource> Where<TSource, TProjection>(
        this IQueryable<TSource> source,
        Expression<Func<TSource, TProjection>> selector,
        Expression<Func<TProjection, bool>> predicate)
    {
        return source.Where(
            Expression.Lambda<Func<TSource, bool>>(
            Expression.Invoke(predicate, selector.Body),
            selector.Parameters));
    }

}