Entity Framework Developers Clamor for Better SQL Generation -- Visual Studio Magazine

Data Driverby David Ramel

Entity Framework Developers Clamor for Better SQL Generation

"I have seen simple select statements [in Entity Framework] with 4 or 5 includes result in nearly 5,000 line SQL statements when an equivalent hand-written SQL statement is [about] 15 lines."

So reads the first of 21 comments on Microsoft's "ADO.NET Entity Framework (EF) Feature Suggestions" site, where developers can post, vote and comment on proposed EF enhancements. "Improved SQL Generation" is by far the No. 1 feature suggestion, with some 1,400 votes.

Several developer complaints about SQL generation concerned bloated code or slow performance. You saw an example of the former; the latter is exemplified by this comment:

"I just documented a case where the Contains() operator reduces EF query performance by a factor of 300. Further diagnosis revealed that the slowdown occurs in the query-generation portion of the request, so I agree that this needs some serious attention."

Microsoft's Diego Vega, program manager for DataFx, responded that the core EF library update coming in .NET 4.5 will provide better SQL generation in some cases, but not for all of the listed scenarios. "I am going to leave this idea as open for now, until we figure out a better way to track individual scenarios, which we need to do to get more actionable data," Vega said.

Speaking of .NET 4.5 and EF core libraries, some comments concerned the attempted separation of EF releases from .NET. The EF team shipped the first two releases as part of the .NET Framework, but then released separate updates to get them out to developers faster, as mentioned in this October blog post. That post said the June 2011 CTP was the first attempted full EF shipment separate from the .NET Framework. But technical problems resulted, so several feature improvements -- such as support for enums, spatial types and others -- that rely on .NET core library updates will have to wait for next EF preview that will ship along with the upcoming public preview of .NET 4.5.

When Vega reiterated this on the feature request page, a couple of readers found fault with that, as evidenced by this comment:

"Diego, No offense but moving to v4.5 of the framework is really not an option for ... most corporate software."

And this:

"I guess because EF core library is included in .NET framework we should wait until then! That is awful, please consider put EF libraries out of .NET framework. Good ORM needs frequent release cycle."

Umm ... isn't that what the team said they were trying to do back in October?

Why, yes, replied Vega in a response to the two reader comments that were posted earlier this month:

"We have indeed looked at taking the whole of EF out of .NET Framework for this same reason. The June 2011 CTP of EF was a first attempt that showed us it is going to be harder than we thought because of the impact on existing applications, partner teams and ADO.NET provider writers."

You can read more about the long, strange trip to EF 5.0 here and bone up on coming EF 5.0 improvements here.

Besides improved SQL generation, the top five feature requests include "Batch CUD Support (1,179 votes); "EF Support for Second Level Cache" (655 votes); "Support for multiple databases" (595 votes); and "Entity Designer: Speed up & Optimize for using with 200+ entities" (524 votes).

What feature requests would you like to see in EF? Comment here or drop me a line.

Posted by David Ramel on 02/27/2012 at 4:22 PM

Printable Format

comments powered by Disqus

Reader Comments:

Tue, Jul 17, 2012 enko

I completely agree, I've tested a trivial object containing two strings each of 50 length being stored by EF in .4sec. That is on subsequent calls, not initial call. That is running in dual 4.4ghz core, with database located on an SSD. I have my own handwritten sproc storing and deleting a 10k varbinary(max) blob in .15sec. I don't know how little you have to care about your performance to use this framework. For me its simply unacceptable.

Sat, Mar 17, 2012 DH Indiana

Part of the problem is all the entity mappers -- they cause even more queries as they try to map one entity to another. Clever idea. Again, poorly implemented. I cannot believe how many people think this is the "end all beat all".

Fri, Mar 16, 2012 DH INDIANA

OK, seen (and even written a few similar, more specific models). EF is the WORST, if you're at all concerned about you DB performance. Write REAL queries directed at your DB (preferrably SP's) to do this. EF is HORRIBLE at optimization. Like LINQ all you want, but it is CRAP in my opinion. Neat idea, poorly implemented -- not a knock against MS people as much as the silly early adopters. REALLY!? If you're using this without some DB Admins in the middle, then you're someone I would NEVER hire -- and I have real say over some real positions right now...

Thu, Mar 15, 2012 Steve Mets Baton Rouge, LA

I'd like to see better SQL generation, but I have always been able to find a way to make make any queries fast. But I recently needed to get the schema name from the Entity Set, and I couldn't find a way to do it. There should be helper methods to easily get metadata about storage, such as schema, extended properties, permissions.

Thu, Mar 15, 2012 Ramone Hamilton Marietta

I definitely agree with you however my pain poit has been how clunky EF is when trying to get it work in a multi-database environment, i.e. you have developemnt, staging and production server/datbase.

Wed, Feb 29, 2012 kinect_dev

I love LINQ and I love the EF. Together they have changed how I develop software more deeply than any feature since generics. besides the performance improvements previosuly requested, I would love better support for data/schema migration, unit testing (mocks) and the ability to more easily swap out the underlying database without changing my model.

Add Your Comments Now:

Your Name:(optional)

Your Email:(optional)

Your Location:(optional)

Comment:

Please type the letters/numbers you see above

Data Driverby David Ramel