LINQ join using Expression Tree

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darren - 27 Feb 2008 23:26 GMT

I'm designing a query editor and have been able to create expressions
on a single table but I am having trouble creating a join dynamically.

Using the adventureworks database as an example,

// let the compiler do it
var q1 = from a in db.Addresses
where a.StateProvinceID == 1
select a;

// same thing but as an expression tree
ParameterExpression p1 = Expression.Parameter(typeof(Address), "a");

// create the condition
Expression p2 = MemberExpression.Property(p1, "StateProvinceID");
Expression i1 = ConstantExpression.Constant(1, typeof(Int32));
Expression op1 = BinaryExpression.Equal(p2, i1);

var args = new ParameterExpression[] { p1 };

// We always want to return a list of addresses even if there are other
// tables in the condition
Expression<Func<Address, bool>> l = Expression.Lambda<Func<Address, bool>>(op1, args);
var results = db.Addresses.Where<Address>(l);

Now how would I do the same as above but using the joined tables?

var q2 = from a in db.Addresses
join o in db.SalesOrderHeaders on a.AddressID equals o.BillToAddressID
select a;

Is there a way to create the Join method at runtime? In my example the join delegate
would have the signature Func<Address,SalesOrderHeader,int,Address>() but I wouldn't
know until runtime which tables would be joined with Address.

TIA.

Reply to this Message

Wen Yuan Wang [MSFT] - 28 Feb 2008 11:20 GMT

Hello TIA,

It seems you need a LINQ join using Expression Tree, correct? If this is
the case, in my opnion, I'd like to write the code as below.

var query=from a in dcdc1.Table_2s
join p in dcdc1.Table_1s on a.c1 equals p.c1
select a;

var q2 = dcdc1.Table_2s.Join(dcdc1.Table_1s, a => a.c1, p => p.c1, (a, p)
=> a);

ParameterExpression a1 = Expression.Parameter(typeof(Table_2),
"a");
Expression a2 = MemberExpression.Property(a1, "c1");
var args1 = new ParameterExpression[] { a1 };
Expression<Func<Table_2,int>> l1 =
Expression.Lambda<Func<Table_2,int>>(a2, args1);

ParameterExpression p1 = Expression.Parameter(typeof(Table_1),
"p");
Expression p2 = MemberExpression.Property(p1, "c1");
var args2 = new ParameterExpression[] { p1 };
Expression<Func<Table_1,int>> l2 =
Expression.Lambda<Func<Table_1,int>>(p2, args2);

ParameterExpression ap1 = Expression.Parameter(typeof(Table_2),
"a");
ParameterExpression ap2 = Expression.Parameter(typeof(Table_1),
"p");
Expression ap3 = ap1;
var args3 = new ParameterExpression[] {ap1,ap2 };
Expression<Func<Table_2, Table_1, Table_2>> l3 =
Expression.Lambda<Func<Table_2, Table_1, Table_2>>(ap3, args3);

var results = dcdc1.Table_2s.Join(dcdc1.Table_1s, l1, l2, l3);

Does this helps? If you have any more concern, please feel free to let me
know. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
======================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Darren - 28 Feb 2008 15:20 GMT

That is almost what I need. The problem with that code is the
lambda expression at the end.

> Expression<Func<Table_2, Table_1, Table_2>> l3 =
> Expression.Lambda<Func<Table_2, Table_1, Table_2>>(ap3, args3);

It needs to know the table type at compile time. I need to be able to create that
lambda function at runtime since I don't know what table it needs to join to.

Thanks.

> Hello TIA,
>
[quoted text clipped - 43 lines]
> ======================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Wen Yuan Wang [MSFT] - 29 Feb 2008 10:17 GMT

Hello Darrren,

I'm afraid we can define Table Type in
Expression<Fun<TableType,TableType,TableType>> at runtime. In .net, it
should be strong typed. We need to perform more research on this issue . I
will reply here as soon as possible.
If you have any more concerns on it, please feel free to post here.

Thanks for your understanding!
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Wen Yuan Wang [MSFT] - 29 Feb 2008 10:18 GMT

Hello Darrren,

I'm afraid we can *NOT* define Table Type in
Expression<Fun<TableType,TableType,TableType>> at runtime. In .net, it
should be strong typed. We need to perform more research on this issue . I
will reply here as soon as possible.
If you have any more concerns on it, please feel free to post here.

Thanks for your understanding!
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Wen Yuan Wang [MSFT] - 03 Mar 2008 11:02 GMT

Hello Darren,
Thanks for your waiting.

Everthing in .net is object. I think you can define the table as Object
type. Thereby, you needn't know which tables would be joined with Address.
For example: I want to join Table_1 with Table_2. I defined Table_1 as
Object. Then the signature will be
Expression<Func<Table_2,Object,Table_2>>
l3=Expression.Lambda<Func<Table_2,Object,Table_2>>(ap3, args3);

var query=from a in dcdc1.Table_2s
join p in dcdc1.Table_1s on a.c1 equals p.c1
select a;

var q2 = dcdc1.Table_2s.Join(dcdc1.Table_1s, a => a.c1, p =>
p.c1, (a, p) => a);

ParameterExpression a1 = Expression.Parameter(typeof(Table_2),
"a");
Expression a2 = MemberExpression.Property(a1, "c1");
var args1 = new ParameterExpression[] { a1 };
Expression<Func<Table_2, int>> l1 =
Expression.Lambda<Func<Table_2, int>>(a2, args1);

ParameterExpression p1 = Expression.Parameter(typeof(Object),
"p");
Expression pi = MemberExpression.Convert(p1,
System.Type.GetType("ConsoleApplication3.Table_1"));
Expression p2 = MemberExpression.Property(pi, "c1");
var args2 = new ParameterExpression[] { p1 };
Expression<Func<Object, int>> l2 =
Expression.Lambda<Func<Object, int>>(p2, args2);

ParameterExpression ap1 = Expression.Parameter(typeof(Table_2),
"a");
ParameterExpression ap2 = Expression.Parameter(typeof(Object),
"p");
Expression ap3 = ap1;
var args3 = new ParameterExpression[] { ap1, ap2 };

Expression<Func<Table_2,Object,Table_2>>
l3=Expression.Lambda<Func<Table_2,Object,Table_2>>(ap3, args3);

Hope this helps. Let me know if you have any more concern, we are glad to
assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Darren - 03 Mar 2008 15:35 GMT

Thanks. I think that will do what I want.

> Hello Darren,
> Thanks for your waiting.
[quoted text clipped - 47 lines]
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Wen Yuan Wang [MSFT] - 04 Mar 2008 08:10 GMT

You are welcome, Darren.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Rate this thread:

LINQ join using Expression Tree

Free Magazines