Most-Wanted Features in Entity Framework

Remarkable that most ORM tools focus on generating SQL, whether it's EF, Hibernate, Datanucleus or whatever. To me that trying to solve a problem that's not there, hence all those tools introduce one. Most developers are perfectly capable of writing understandable, high performance SQL (or whatever language your datastore speaks). The only cumbersome part is that result set mapping to some object model. So please focus on mapping from a relational model to an object model and give the developer control over the SQL, since ORM will never going to get that right.

True, but there are advantages to having your business logic entirely in a programming language like C#. For eg, with LINQ to Entities, you could build your query in stages, and have the benefits of better readability and easier debugging at the same time being database agnostic (at least for the databases that the ORM supports). It also reduces the overall code you have to write.

I agree its a tough problem to solve (to make SQL generated by ORM tools equivalent or close to hand-written SQL) but its worth trying.

Another potential solution could be to use micro-ORMs like Dapper or PetaPoco, where as you said, they focus more on object-relational mapping and leave the SQL writing to the developers.

Is using C# to build sql really an "advantage"? I really doubt it. Besides showing some shiny fluent API, I really don't see the benefit. As a developer the most important thing I care about is the sql sent to server and the mapped result.

It is more architect friendly. :)

EF (I think 4.1 onwards) follows so-called convention-over-configuration approach - so you don't really need to use the fluent API most of the cases. It is convenient, and it does take less code from the developers. Data Access Code routinely forms 25-30% of the code in LOB applications (using hand-coded SQL and manual mapping), and this can be more or less eliminated.

What I meant by LINQ to entities, is that you can form the query in steps (thanks to the IQueryable interface) and it gives you the benefit of being able to debug. Again this is quite convenient.

Similarly loading of associated entities without having to specify joins is another convenience. This makes LINQ queries far more compact than corresponding SQL queries. For eg, imagine the SQL query for the following LINQ-to-entities query, which will contain all the required joins between 5 entities (including the FilmSize entity in the select). More complex the logic, better is the LINQ query to understand and debug -

var consumptions = this.DbContext.ReportRows.Where(p => p.Report.FixedPattern.Customer.FoundryID.Equals(foundryId))
.Select(p => new
{
Date = p.RGReport.ReportDate,
SentToHO = 0,
Consumed = p.FilmSize.Area,
ReceivedFromHO = 0
});

Now this sure doesn't come free, as the no. 1 feature request makes clear.

The question is whether the convenience and smaller code base is worth the trade-off in terms of quality of SQL generated. The answer will depend on the context - and if hand-coded SQL is required, I don't think EF is useful there. Dapper, Massive, PetaPoco are there to fill exactly that need.

In the forum mentioned above, the 3rd most requested feature is: "EF Support for Second Level Cache". Wanting this is a mistake. What's really needed is a non-relational database to separate the data into two views. A core (CRUD, relational view) and the query view, e.g. working a little bit like RavenDB. The kind of database I'm talking about would be designed to overcome the ACID requirement of data consistency: every CRUD change would seamlessly trigger an update to the query view. Imagine this query view being a set of indices; one for each query, but far faster than SQL Server's index rebuilding features and happening behind the scenes constantly as database inserts, deletes and updates are done.

The big problem here is that each team are atomised. The EF team do EF stuff, the database team do SQL Server stuff. You need one team to do both.

Thanks Mark; I can see how the pattern you are mentioning would be useful for scaling and improving read performance; I guess the advantage is that the query view is loaded in advance, before first query call is made to the database. The only potential bottlenecks I see is how fast the query view gets updated (the faster the db we pick, the more compromise we may have to make on the consistency of the cache) and the extra load added on the database by the triggers themselves.

However this would still be useful where people don't mind a little delay, and we could batch the updates to the query view every few minutes. I think the folks at StackExchange use a similar approach.

Are you aware of any tools that allow something similar today, without having to do everything manually?

Meanwhile note that these are feature requests voted by users which affect the ranking, nothing that the EF team has directly contributed to. If you just throw a note in there about this alternative, it might provide food for thought to Microsoft.

The problem is that most developers think they can write high performance SQL, but most DBA's think otherwise.
Having a DBA makes makes a lot of difference as DB performance and correctness is what those people do. If there is no DBA those dimensions may not be very important. Consequently you might be wasting time and money on optimizing SQL, while you could have used an higher level tool and create more features.

Interesting that it is suggested to go straight to SQL for this. When doing code-first with EF, you are green fielding. In the past week I have had 3 new projects cross my desk with the solution being EF code-first. Two of them have bulk CUD needs. Really defeats the purpose...

ZZZ Projects offers 2 kinds of bulk operations via the Entity Framework Extensions Library. They increase drastically your performance over the SaveChanges method from Entity Framework.
www.zzzprojects.com/entity-framework-extensions/

BulkSaveChanges

The first way is via our main feature, the BulkSaveChanges method which literally replaces the SaveChanges method. You can expect to save your entities a minimum of 10-15 times faster with thousands of entities. This method supports all kinds of associations and entity types (TPC, TPH, and TPT).

using (var ctx = new DbContextEntities())
{
// ... Make any changes to your DbContext ...
ctx.BulkSaveChanges();
}

Bulk Operations

The second ways is via Bulk Operations methods (BulkDelete, BulkInsert, BulkUpdate and BulkMerge) which increases even more the performance and allows customization for many settings like what's the primary key to use.

using (var ctx = new DbContextEntities())
{
// ... Make any changes to your DbContext ...

// Use Entity Framework Setting
ctx.BulkUpdate(list);

// OR customize your settings
ctx.BulkUpdate(list, operation => operation.ColumnPrimaryKeyExpression = column => column.ID);
}