EF: SQL queries
With EF4, it’s possible to execute some SQL queries. (It was possible with EF v1 with EF extensions).
I don’t like it. Indeed, it implies to break the EDM abstraction against the DataBase.
Moreover, we can do without. In the V1, it was possible, with SSDL Functions, to write the SQL query to execute.
I think that the ability to write our own SQL queries is interesting, particularly in an iterative development process but I think that loosing the abstraction against the DB is a pity.
However, I just found a case where this new feature is useful: the unit tests. Indeed, it’s a shame to have to define SSDL Functions if they are used only by tests. Imagine that we have to initialize the DB for our tests. We will start by deleting the existing DataRows. To do it, we can use EF “basic” deletion:
using (var context = new MyNorthwindEFEntities())
{
foreach (var c in context.Categories.ToList())
context.DeleteObject(c);
context.SaveChanges();
}
But it isn’t good to have to load all the entities in order to delete them. Moreover, we will have n DELETE queries instead of only one.
We can improve it by loading only the entity id:
using (var context = new MyNorthwindEFEntities())
{
foreach (var cId in context.Categories.Select(c => c.CategoryID))
{
var c = new Categories{ CategoryID = cId };
context.AttachTo("Categories", c);
context.DeleteObject(c);
}
context.SaveChanges();
}
It’s better but we still have one SELECT and n DELETE.
As I wrote up, it is possible to use an SSDL Function which make the DELETE for us.
With EF4, it is also possible to execute directly the SQL query:
context.ExecuteStoreCommand("DELETE FROM Products");
which is, I admit, very useful.
With EF4, we also have the ExecuteStoreQuery<TResult> method which can be used to let EF materialize the SQL DataRows (from the SQL query) to entities.