<n>The following article was originally published on the <a href="http://encodo.com/en/blogs.php?entry_id= 311">Encodo blogs</a> and is cross-published here.</n> <hr> Many improvements have been made to Microsoft's Entity Framework (EF) since I last used it in production code. In fact, we'd last used it waaaaaay back in 2008 and 2009 when EF had just been released. Instead of EF, I've been using the <a href="{www-en}/quino">Quino</a> ORM whenever I can. However, I've recently started working on a project where EF5 is used (EF6 is in the late stages of release, but is no longer generally available for production use). Though I'd been following the latest EF developments via the <a href="http://blogs.msdn.com/b/adonet/">ADO.Net blog</a>, I finally had a good excuse to become more familiar with the latest version with some hands-on experience. <h>Our history with EF</h> <a href="{www-en}view_article.php?entry_id=158">Entity Framework: Be Prepared</a> was the first article I wrote about working with EF. It's quite long and documents the pain of using a 1.0 product from Microsoft. That version support only a database-first approach, the designer was slow and the ORM SQL-mapper was quite primitive. Most of the tips and advice in the linked article, while perhaps amusing, are no longer necessary (especially if you're using the Code-first approach, which is highly recommended). Our next update, <a href="{www-en}view_article.php?entry_id=163">The Dark Side of Entity Framework: Mapping Enumerated Associations</a>, discusses a very specific issue related to mapping enumerated types in an entity model (something that Quino does very well). This shortcoming in EF has also <a href="http://msdn.microsoft.com/en-us/data/hh859576.aspx">been addressed</a> but I haven't had a chance to test it yet. Our final article was on performance, <a href="{www-en}view_article.php?entry_id=178">Pre-generating Entity Framework (EF) Views</a>, which, while still pertinent, no longer needs to be done manually (there's an <a href="http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d">Entity Framework Power Tools</a> extension for that now). So let's just assume that that was the old EF; what's the latest and greatest version like? Well, as you may have suspected, you're not going to get an article about Code-first or database migrations.<fn> While a lot of things have been fixed and streamlined to be not only much more intuitive but also work much more smoothly, there are still a few operations that aren't so intuitive (or that aren't supported by EF yet). <h>Standard way to delete objects</h> One such operation is deleting multiple objects in the database. It's not that it's not possible, but that the only solution that immediately appears is to, <ul> load the objects to delete into memory, then remove these objects from the context and finally save changes to the context, which will remove them from the database </ul> The following code illustrates this pattern for a hypothetical list of users. <code> var users = context.Users.Where(u => u.Name == "John"); foreach (var u in users) { context.Users.Remove(u); } context.SaveChanges(); </code> This seems somewhat roundabout and quite inefficient.<fn> <h>Support for batch deletes?</h> While the method above is fine for deleting a small number of objects---and is quite useful when removing different types of objects from various collections---it's not very useful for a large number of objects. Retrieving objects into memory only to delete them is neither intuitive nor logical. The question is: is there a way to tell EF to delete objects based on a query from the database? I found an example attached as an answer to the post <a href="http://stackoverflow.com/questions/9582632/simple-delete-query-using-ef-code-first" source="Stack Overflow">Simple delete query using EF Code First</a>. The gist of it is shown below. <code> context.Database.SqlQuery<user>( "DELETE FROM Users WHERE Name = @name", new [] { new SqlParameter("@name", "John") } ); </code> To be clear right from the start, using ESQL is already sub-optimal because the identifiers are not statically checked. This query will cause a run-time error if the model changes so that the "Users" table no longer exists or the "Name" column no longer exists or is no longer a string. Since I hadn't found anything else more promising, though, I continued with this approach, aware that it might not be usable as a pattern because of the compile-time trade-off. Although the answer had four up-votes, it is not clear that either the author or any of his fans have actually tried to execute the code. The code above returns an <c>IEnumerable<user></c> but doesn't actually <i>do</i> anything. After I'd realized this, I went to MSDN for more information on the <c>SqlQuery</c> method. The documentation is not encouraging for our purposes (still trying to <i>delete</i> objects without first <i>loading</i> them), as it describes the method as follows (emphasis added), <bq>Creates a raw SQL query that will <b>return</b> elements of the given generic type. The type can be any type that has properties that match the names of the <b>columns returned from</b> the query, or can be a simple primitive type.</bq> This does not bode well for deleting objects using this method. Creating an enumerable does very little, though. In order to actually execute the query, you have to evaluate it. <i>Die Hoffnung stirbt zuletzt</i><fn> as we like to say on this side of the pond, so I tried evaluating the enumerable. A <c>foreach</c> should do the trick. <code> <hl>var users = </hl>context.Database.SqlQuery<user>( "DELETE FROM Users WHERE Name = @name", new [] { new SqlParameter("@name", "John") } ); foreach (var u in users) { // NOP? } </code> As indicated by the "NOP?" comment, it's unclear what one should actually <i>do</i> in this loop because the query already includes the command to delete the selected objects. Our hopes are finally extinguished with the following error message: <div class="error">System.Data.EntityCommandExecutionException : The data reader is incompatible with the specified 'Demo.User'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.</div> That this approach does not work is actually a relief because it would have been far too obtuse and confusing to use in production. It turns out that the <c>SqlQuery</c> only works with <c>SELECT</c> statements, as was strongly implied by the documentation. <code> var users = context.Database.SqlQuery<user>( "<hl>SELECT *</hl> FROM Users WHERE Name = @name", new [] { new SqlParameter("@name", "John") } ); </code> Once we've converted to this syntax, though, we can just use the much clearer and <i>compile-time--checked</i> version that we started with, repeated below. <code> var users = context.Users.Where(u => u.Name == "John"); foreach (var u in users) { context.Users.Remove(u); } context.SaveChanges(); </code> So we're back where we started, but perhaps a little wiser for having tried. <h>Deleting objects with Quino</h> As a final footnote, I just want to point out how you would perform multiple deletes with the Quino ORM. It's quite simple, really. Any query that you can use to <i>select</i> objects you can also use to <i>delete</i> objects<fn>. So, how would I execute the query above in Quino? <code> Session.Delete(Session.CreateQuery<user>().WhereEquals(User.MetaProperties.Name, "John").Query); </code> To make it a little clearer instead of showing off with a one-liner: <code> var query = Session.CreateQuery<user>(); query.WhereEquals(User.MetaProperties.Name, "John"); Session.Delete(query); </code> Quino doesn't support using Linq to create queries, but its query API is still more statically checked than ESQL. You can see how the query could easily be extended to restrict on much more complex conditions, even including fields on joined tables. <hr> <ft>As I wrote, We're using Code-first, which is <i>much</i> more comfortable than using the database-diagram editor of old. We're also using the nascent "Migrations" support, which has so far worked OK, though it's nowhere near as convenient as Quino's automated schema-migration.</ft> <ft>Though it is inefficient, it's better than a lot of other examples out there, which almost unilaterally include the call to <c>context.SaveChanges()</c> <i>inside</i> the <c>foreach</c>-loop. Doing so is wasteful and does not give EF an opportunity to optimize the delete calls into a single SQL statement (see footnote below).</ft> <ft>Translates to: "Hope is the last (thing) to die."</ft> <ft>With the following caveats, which generally apply to all queries with any ORM: <ul> Many databases use a different syntax and provide different support for <c>DELETE</c> vs. <c>SELECT</c> operations. Therefore, it is more likely that more complex conditions are not supported for <c>DELETE</c> operations on some database back-ends Since the syntax often differs, it's more likely that a more complex query will fail to map properly in a <c>DELETE</c> operation than in a <c>SELECT</c> operation simply because that particular combination has never come up before. That said, Quino has quite good support for deleting objects with restrictions not only on the table from which to delete data but also from other, joined tables. </ul> Some combination of these reasons possibly accounts for EF's lack of support for batch deletes. </ft>