EF4: What’s in it there in Foreign Key

There are a lot of buzz around the Foreign Key support in ADO.NET Entity Framework v4. Question often comes to our mind that what is so special here? While discussing with my fellow Consultant Vijay, he gave me a nice example. Let me share here,

Let’s assume that you have two tables Dept and EmpDept. Connected with DeptId which is Primary in Dept table and Foreign key in EmpDept table. When we simply use SQL query we can easily change the DeptId of an employee by sending one single Update statement like

UPDATE EmpDept SET DeptId = 2 WHERE EmpId = 1

This simple thing was totally missing in the previous version of EF (in Visual Studio 2008). We had to pull out the whole new Dept to update an existing EmpDept.

If we have the database table like,

From it if we have built the model like, (as it was in Visual Studio 2008)

Notice, here you do not have the DeptId as static property in the EmpDept entity. Thus the pain. So to simply shift one employee from one dept to another you had to write something like,

When no Property for FK

1. using (var ctx = new TestDBEntities())
2. {
3.     //Get the Employee to Update
4.     EmpDept q = (from e in ctx.EmpDepts where e.EmpId == 2 select e).First();
5.  
6.     //Get the Dept to update to
7.     Dept dd = (from d in ctx.Depts where d.DeptId == 3 select d).First();
8.  
9.     //Finally Update
10.     q.Dept = dd;
11.  
12.     ctx.SaveChanges();
13. }

Which would execute 3 queries in the database.

T-SQL

1. --Query 1 : Get Emp to Update
2. SELECT TOP (1) [Extent1].[EmpId] AS [EmpId], [Extent1].[EmpName] AS [EmpName], [Extent1].[DeptId] AS [DeptId]
3. FROM [dbo].[EmpDept] AS [Extent1] WHERE 2 = [Extent1].[EmpId]
4.  
5. --Query 2 : Get the Dept
6. SELECT TOP (1) [Extent1].[DeptId] AS [DeptId], [Extent1].[DeptName] AS [DeptName]
7. FROM [dbo].[Dept] AS [Extent1] WHERE 3 = [Extent1].[DeptId]
8.  
9. --Query 3 : Finally Update it
10. exec sp_executesql N'update [dbo].[EmpDept] set [DeptId] = @0 where (([EmpId] = @1) and ([DeptId] = @2))
11. ',N'@0 int,@1 int,@2 int',@0=3,@1=2,@2=2

Now, in EF4 you get the foreign key as static column. So things becomes much more simpler both from the front end code perspective and backend execution. The new model looks like as below if we select the “Include foreign key columns in the model”

as,

Now notice here the new static property in EmpDept as DeptId. If you want to achieve the same goal here, things would become much more simpler,

When FK is static property

1. using (var ctx = new TestDBEntities())
2. {
3.     EmpDept q = (from e in ctx.EmpDepts where e.EmpId == 2 select e).First();
4.  
5.     q.DeptId = 2;
6.  
7.     ctx.SaveChanges();
8. }

And also the generated SQL would be only 2,

T-SQL

1. --Query 1 : Get Emp to Update
2. SELECT TOP (1) [Extent1].[EmpId] AS [EmpId], [Extent1].[EmpName] AS [EmpName], [Extent1].[DeptId] AS [DeptId]
3. FROM [dbo].[EmpDept] AS [Extent1] WHERE 2 = [Extent1].[EmpId]
4.  
5. --Query 2 : Finally Update it
6. exec sp_executesql N'update [dbo].[EmpDept] set [DeptId] = @0 where (([EmpId] = @1) and ([DeptId] = @2))
7. ',N'@0 int,@1 int,@2 int',@0=3,@1=2,@2=2

I personally still feel that being ORM we are paying the additional database roundtrips here. And if you want to reduce it again to simple one either you use Stored Procedure and create Function or execute RAW T-SQL using ExecuteStoreQuery method of Context.

Namoskar!!!