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
 using (var ctx = new TestDBEntities())
{
    //Get the Employee to Update
    EmpDept q = (from e in ctx.EmpDepts where e.EmpId == 2 select e).First();
 
    //Get the Dept to update to 
    Dept dd = (from d in ctx.Depts where d.DeptId == 3 select d).First();
 
    //Finally Update
    q.Dept = dd;
 
    ctx.SaveChanges();
}
 

Which would execute 3 queries in the database.

T-SQL
 --Query 1 : Get Emp to Update
SELECT TOP (1) [Extent1].[EmpId] AS [EmpId], [Extent1].[EmpName] AS [EmpName], [Extent1].[DeptId] AS [DeptId]
FROM [dbo].[EmpDept] AS [Extent1] WHERE 2 = [Extent1].[EmpId]
 
--Query 2 : Get the Dept
SELECT TOP (1) [Extent1].[DeptId] AS [DeptId], [Extent1].[DeptName] AS [DeptName]
FROM [dbo].[Dept] AS [Extent1] WHERE 3 = [Extent1].[DeptId]
 
--Query 3 : Finally Update it
exec sp_executesql N'update [dbo].[EmpDept] set [DeptId] = @0 where (([EmpId] = @1) and ([DeptId] = @2))
',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
 using (var ctx = new TestDBEntities())
{
    EmpDept q = (from e in ctx.EmpDepts where e.EmpId == 2 select e).First();
 
    q.DeptId = 2;
 
    ctx.SaveChanges();
}
 

And also the generated SQL would be only 2,

T-SQL
 --Query 1 : Get Emp to Update
SELECT TOP (1) [Extent1].[EmpId] AS [EmpId], [Extent1].[EmpName] AS [EmpName], [Extent1].[DeptId] AS [DeptId]
FROM [dbo].[EmpDept] AS [Extent1] WHERE 2 = [Extent1].[EmpId]
 
--Query 2 : Finally Update it
exec sp_executesql N'update [dbo].[EmpDept] set [DeptId] = @0 where (([EmpId] = @1) and ([DeptId] = @2))
',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!!!

Entity Framework CodeFirst Calling Find does Top 2

Scott Guthrie Discusses Windows Azure SDK 2.0

GIDS 2013 Windows Phone 8 Developer Tips and Tricks Samples and Readings

Windows Store Apps power of WinJS

Great Indian Developer Summit 2013 : Windows Store App Development Hands on Lab

ADO.NET

EF4: What’s in it there in Foreign Key