How to reuse linq expression for "Where" when using multiple source tables

Question

Let the following take place:

public interface IOne { UInt64 Id { get; } Int16 Size { get; } } public interface ITwo { UInt64 OneId { get; } Int16 Color { get; } } 

As explained here , the way to reuse a linq expression is to write something like this:

 public static Expression<Func<IOne, bool>> MyWhereExpression( int value ){ return (o) => (o.Size > value); } int v = 5; IQueryable<IOne> records = from one in s.Query<IOne>() .Where(MyWhereExpression(v)) select one; 

When I want to do the same with two tables, I run into a problem.

Expression:

 public static Expression<Func<IOne, ITwo, bool>> MyWhereExpression2(int color ) { return (one,two) => (one.Id == two.OneId) && (two.Color > color ); } 

Linq 1:

 int color = 100; IQueryable<IOne> records = from one in s.Query<IOne>() from two in s.Query<ITwo>() .Where(MyWhereExpression2(color)) select one; 

This does not work. If you use only 2nd.

Linq 2:

 int color = 100; IQueryable<IOne> records = (from one in s.Query<IOne>() from two in s.Query<ITwo>() select new { one, two }) .Where(MyWhereExpression2(color)); 

The result is

Argument 2: cannot be converted from 'Expression <System.Func <IOne, ITwo, bool →' to 'System.Func <AnonymousType # 1, int, bool>'

I understand the error message in AnonymousType, but I cannot figure out how to write the request.

The reason I want to use an expression and not just write

 where (one.Id == two.OneId) && (two.Color > color ) 

directly in the linq query is that I want to reuse this expression in multiple linq queries.

Answer 1

Answering my own question ...

After some experimentation (including Nick Guerrera’s original answer), I took a different approach — instead of trying to reuse the expression, I reuse all linq. However, he still required the creation of a container structure.

 struct Pair { public IOne One { get; set; } public ITwo Two { get; set; } public Pair(IOne one, ITwo two) : this() { One = one; Two = two; } } public IQueryable<Pair> Get(ISession s, int color) { return from one in s.Query<IOne>() from two in s.Query<ITwo>() where (one.Id == two.OneId) && (two.Color > color) select new Pair(one, two); } 

Now i can call

 Get(s, color).Count(); 

and

 var records = (from data in Get(s, color) select data).Take(2000); 

and etc.

Answer 2

There may currently be a more elegant solution that eludes me, but you can just use Tuple<IOne, ITwo> instead of an anonymous type:

 static Expression<Func<Tuple<IOne, ITwo>, bool>> MyWhereExpression2(int color) { return t => (t.Item1.Id == t.Item2.OneId) && (t.Item2.Color > color); } int color = 100; IQueryable<IOne> records = (from one in s.Query<IOne>() from two in s.Query<ITwo>() select Tuple.Create(one, two)) .Where(MyWhereExpression2(color)) .Select(t => t.Item1); 

UPDATE: I probably answered too quickly, as this will not work with Linq to SQL, since the call to Tuple.Create cannot be translated into SQL. For working with Linq to SQL, the only solution that I can see at the moment is to create a named type:

 class Pair { public IOne One { get; set; } public ITwo Two { get; set; } } static Expression<Func<Pair, bool>> MyWhereExpression2(int color) { return p => (p.One.Id == p.Two.OneId) && (p.Two.Color > color); } int color = 100; IQueryable<IOne> records = (from one in s.Query<IOne>() from two in s.Query<ITwo>() select new Pair { One = one, Two = two }) .Where(MyWhereExpression2(color)) .Select(p => p.One); 

Answer 3

First of all, why do you have two tables with the same set of fields? Ideally, the database design should avoid the same field names in different tables. Design should use inheritance. Common fields should move to the base class, and EF allows you to create a table on a hierarchy, which should solve your problem.

If you create your model with a table in a hierarchy, you do not need an interface, and your linq queries can use common filter expressions.

It is impossible to achieve what you ask if you do not sit down and write a complex reflection-based method that will clone your expression from one type to another.