LINQ join using Expression Tree

[Darren]

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.

 

[Wen Yuan Wang [MSFT]]

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.

 

[Darren]

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,

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.

 

[Wen Yuan Wang [MSFT]]

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.

 

[Wen Yuan Wang [MSFT]]

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.

 

[Wen Yuan Wang [MSFT]]

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.

 

[Darren]

Thanks. I think that will do what I want.

 

[Wen Yuan Wang [MSFT]]

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.

 

[ionut nedelea]

What if You need to join on composite keys? How would you do that?

Thanks

 

[sloan]

Select * from dbo.Employee e
join dbo.JobTitle jt
on e.EmployeeID = jt.EmployeeID AND e.StateID = jt.StateID


Here is a faux example since you did not provide any DDL.

 

[sloan]

And you might want to post to a sqlserver newsgroup.

Your description of your issue was very vague.

 

[Arne Vajhøj]

What if You need to join on composite keys? How would you do that?

Join on all fields.

Or redesign to a single column surrogate key.

In my experience multi column keys often becomes
a burden.

Arne

 

[sloan]

Here is a "natural key" example I did up. Just to show how quickly it can
get out of control.



Basically, I wanted to track.
How many hours an employee worked....during a certain week, on a certain
day. And divide it up by jobtitle.(in case 1 person has more than 1
jobtitle)

(
Example: Mary, during the second week of the year, on Monday.... worked 2
hours as a Manager and 6 hours as a Typist.
John, during the third week of the year, on Friday.... worked 8 hours as a
Developer.
)

(Basically, a time tracker for employees)

Anyway. Needless to say this is just a demo.....and shows how composite
keys when propogated "down the chain" as FK's gets really, really messy.







/*

Use [master]

G-O

DROP Database NaturalKeyDemo

G-O

Create Database NaturalKeyDemo

G-O

*/

Use [NaturalKeyDemo]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[WorkDay]') AND type in (N'U'))

DROP TABLE [dbo].[WorkDay]

GO







IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].WorkWeek') AND type in (N'U'))

DROP TABLE [dbo].WorkWeek

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EmployeeJobTitleLink]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)

BEGIN

DROP TABLE [dbo].[EmployeeJobTitleLink]

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[JobTitle]') AND type in (N'U'))

DROP TABLE [dbo].[JobTitle]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))

DROP TABLE [dbo].[Employee]

GO



IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))

DROP TABLE [dbo].[Department]

GO







CREATE TABLE dbo.Department (

DepartmentID int not null primary key clustered ,

DepartmentName varchar(24) not null unique

)



CREATE TABLE dbo.Employee (

[EmployeeID] int not null primary key clustered ,


DepartmentID [int] NOT NULL FOREIGN KEY (DepartmentID) REFERENCES
dbo.Department(DepartmentID),

LastName varchar(24) not null ,

FirstName varchar(24) not null,

CreateDate [smalldatetime] NOT NULL DEFAULT (getdate()),

HireDate smalldatetime not null ,

)



CREATE TABLE dbo.JobTitle (

JobTitleID int not null primary key clustered ,

JobTitleName varchar(24) not null unique

)





if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EmployeeJobTitleLink]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)

BEGIN

DROP TABLE [dbo].[EmployeeJobTitleLink]

END

GO



CREATE TABLE [dbo].[EmployeeJobTitleLink] (

EmployeeID int not null FOREIGN KEY ([EmployeeID]) REFERENCES
dbo.[Employee]([EmployeeID]),

JobTitleID int not null ,

CONSTRAINT Emp_JT_Link_UNIQUE UNIQUE (EmployeeID , JobTitleID)

)

GO

ALTER TABLE dbo.[EmployeeJobTitleLink]

ADD CONSTRAINT EmployeeJobTitleLinkToJobTitleFK

FOREIGN KEY ( JobTitleID )

REFERENCES dbo.JobTitle ( JobTitleID )

ON UPDATE CASCADE





IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[WorkWeek]') AND type in (N'U'))

DROP TABLE [dbo].[WorkWeek]

GO





CREATE TABLE [dbo].[WorkWeek]

(

--[WorkWeekID] [uniqueidentifier] NOT NULL DEFAULT newsequentialid() ,


[EmployeeID] int NOT NULL ,

JobTitleID int not null ,

YearOf int not null ,

WeekNumber int not null ,

--HoursWorked int not null default 0 ,

[CreatedDate] [datetime] NOT NULL DEFAULT getdate()





--CONSTRAINT [PKC_WorkWeek_WorkWeekUUID] PRIMARY KEY CLUSTERED (
[WorkWeekUUID] ASC )

)

GO

ALTER TABLE dbo.WorkWeek

ADD CONSTRAINT PK_WorkWeek_Composite

PRIMARY KEY CLUSTERED (EmployeeID, JobTitleID , YearOf , WeekNumber)

GO





ALTER TABLE dbo.WorkWeek

ADD CONSTRAINT WorkWeekToEmployeeJobTitleLinkFK

FOREIGN KEY (EmployeeID, JobTitleID)

REFERENCES dbo.[EmployeeJobTitleLink] (EmployeeID, JobTitleID)

ON UPDATE CASCADE





set nocount on





Insert into dbo.Department ( DepartmentID , DepartmentName)

values (101 , 'Dept101')

Insert into dbo.Department ( DepartmentID , DepartmentName)

values (102 , 'Dept102')



Insert Into dbo.Employee ( EmployeeID , DepartmentID , LastName , FirstName
, HireDate )

values ( 1001 , 101 , 'LN_1001' , 'FN_1001', getdate() )

Insert Into dbo.Employee ( EmployeeID , DepartmentID , LastName , FirstName
, HireDate )

values ( 1002 , 101 , 'LN_1002' , 'FN_1002', getdate() )

Insert Into dbo.Employee ( EmployeeID , DepartmentID , LastName , FirstName
, HireDate )

values ( 1011 , 102 , 'LN_1011' , 'FN_1011', getdate() )

Insert Into dbo.Employee ( EmployeeID , DepartmentID , LastName , FirstName
, HireDate )

values ( 1012 , 102 , 'LN_1012' , 'FN_1012', getdate() )



Insert into dbo.JobTitle ( JobTitleID , JobTitleName )

values (501 , 'Receptionist')

Insert into dbo.JobTitle ( JobTitleID , JobTitleName)

values (502 , 'Janitor')

Insert into dbo.JobTitle ( JobTitleID , JobTitleName)

values (503 , 'HelpDeskWorker')

Insert into dbo.JobTitle ( JobTitleID , JobTitleName)

values (504 , 'SecurityGuard')





INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1001 , 501 )

INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1001 , 502 )

INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1002 , 502 )

INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1002 , 503 )

INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1011 , 503 )

INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1011 , 504 )

INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1012 , 501 )

INSERT INTO [dbo].[EmployeeJobTitleLink] ( EmployeeID , JobTitleID )

values ( 1012 , 504 )





INSERT INTO [dbo].[WorkWeek] ( [EmployeeID] , JobTitleID , YearOf ,
WeekNumber )--, HoursWorked )

values ( 1001 , 501 , 2007 , 50 )--, 31 )

INSERT INTO [dbo].[WorkWeek] ( [EmployeeID] , JobTitleID , YearOf ,
WeekNumber )--, HoursWorked )

values ( 1001 , 502 , 2007 , 51 )--, 32 )



select * from dbo.[WorkWeek]

Update [EmployeeJobTitleLink] Set JobTitleID = 504 where EmployeeID = 1001
and JobTitleID = 501

select * from dbo.[WorkWeek]





IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[WorkDay]') AND type in (N'U'))

DROP TABLE [dbo].[WorkDay]

GO





CREATE TABLE [dbo].[WorkDay]

(

-- Start FK "Copy"

EmployeeIDCopy int NOT NULL ,

JobTitleIDCopy int not null ,

YearOfCopy int not null ,

WeekNumberCopy int not null ,

WeekDayNumber int not null , --Represents Sunday thru Saturday

DayHoursWorked int not null default 0 ,



)

GO

ALTER TABLE dbo.WorkDay

ADD CONSTRAINT PK_WorkDay_Composite

PRIMARY KEY CLUSTERED (EmployeeIDCopy, JobTitleIDCopy , YearOfCopy ,
WeekNumberCopy , WeekDayNumber , DayHoursWorked )

GO



ALTER TABLE dbo.WorkDay

ADD CONSTRAINT WorkDayToWorkWeekFK

FOREIGN KEY (EmployeeIDCopy , JobTitleIDCopy , YearOfCopy , WeekNumberCopy )

REFERENCES dbo.WorkWeek (EmployeeID, JobTitleID , YearOf , WeekNumber )

ON UPDATE CASCADE





INSERT INTO [dbo].WorkDay ( [EmployeeIDCopy] , JobTitleIDCopy , YearOfCopy ,
WeekNumberCopy , WeekDayNumber , DayHoursWorked )

values ( 1001 , 502 , 2007 , 51 , 2 , 7 )

INSERT INTO [dbo].WorkDay ( [EmployeeIDCopy] , JobTitleIDCopy , YearOfCopy ,
WeekNumberCopy , WeekDayNumber , DayHoursWorked )

values ( 1001 , 502 , 2007 , 51 , 3 , 8 )

INSERT INTO [dbo].WorkDay ( [EmployeeIDCopy] , JobTitleIDCopy , YearOfCopy ,
WeekNumberCopy , WeekDayNumber , DayHoursWorked )

values ( 1001 , 504 , 2007 , 50 , 3 , 7 )

INSERT INTO [dbo].WorkDay ( [EmployeeIDCopy] , JobTitleIDCopy , YearOfCopy ,
WeekNumberCopy , WeekDayNumber , DayHoursWorked )

values ( 1001 , 504 , 2007 , 50 , 4 , 7 )



select * from dbo.WorkDay

Update [EmployeeJobTitleLink] Set JobTitleID = 503 where EmployeeID = 1001
and JobTitleID = 504





select * from dbo.WorkDay





Update JobTitle set JobTitleID = 7777 where JobTitleID = 503

select 0 as Spacer

select * from dbo.JobTitle

select * from dbo.[EmployeeJobTitleLink]

select * from dbo.[WorkWeek]

select * from dbo.WorkDay