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
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,
Which would execute 3 queries in the database.
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”
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,
And also the generated SQL would be only 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.