Why Does the Entity Framework make so Many Roundtrips to the Database?

Question

Hello, I am rewriting my application to use the entity framework. What I am confused about is the code I am writing looks like it is making unnecessary tripts the the sql server. For example, I have a question answer site similar to SO. When I add an answer to a question -- here is the code I use:

var qu = context.question.where(c => c.questionID == 11).First();  //Database call here
var answer = new answer();
answer.title = "title here";
answer.desc = "desc here";
answer.question = qu;
context.SaveChanges();   //Database call here

In the code above there are 2 database calls right? If so, why can't I add an answer to a question directly? such as

var ans = answer.Createanswer (0, "title here", "desc here", questionID)
context.SaveChanges();

Is there a way to minimize all the database calls?

Answer 1

It can be done, but it's extremely painful in .NET 3.5. They've made this much easier in .NET 4.0.

Answer 2

You don't actually need to load the Question to set the relation. Instead you can just use the EntityReference

e.g.

Answer.QuestionReference = new EntityReference<Question>();
Answer.QuestionReference.EntityKey 
  = new EntityKey("MyContextName.Question", "Id", questionId);

I personally use an extension method for setting entity keys

public static void SetEntityKey<T>(this EntityReference value, int id)
{
   value.EntityKey = new EntityKey("ContextName." + typeof(T).Name, "Id", id);
}

So it would look like this instead.

 Answer.QuestionReference = new EntityReference<Question>();
 Answer.QuestionReference.SetEntityKey<Question>(questionId);

Answer 3

As explained by AlexJ one of the EF designers http://blogs.msdn.com/alexj/archive/2009/06/19/tip-26-how-to-avoid-database-queries-using-stub-entities.aspx

Also this all falls into the realm on "optimisation" which in not often as simple as it seems

Using the the simple approach, SQL will do a read operation to load the FK (question) and cache the result, and then on a seperate command an insert operation which should be using the cached FK result

Using the attached FK method still results in the server doing a read operation for the FK, it just means one less round trip to the SQL Server. So the question becomes - over time is a round trip more expensive than the increased code complexity?

If the application and SQL Server are on the same machine this overhead is very small

Also, if the FK is a clustered index on a large or wide table the IO overhead can be significantly more than if it is a seperate standard index on just the FK value - assuming that the query optimiser is working correctly :-)