I agree if you want absolute peak performance and you have the resources and expertise you should go the direct SQL route. Also, I should probably have said a mixture of ORM and direct SQL is probably the best approach as most applications probably have some functionality that is similar to reporting and will require several data processing steps that are best handled in a stored procedure. That said, for basic CRUD and filtering operations I would still advocate an ORM tool unless peak performance is the top requirement. Also in a mixed situation, I’d probably use the ORM tool as the structure to call and pass parameters to the stored procedure or to pass my straight SQL as many ORM tools have this functionality.

The mass update is an interesting task and how it is handled depends on the ORM tool . I think entity framework doesn’t handle it at all. Generally Entity Framework is a step behind in the ORM world. I came across a post indicating you can extend EF 4 to handle mass udpates, (http://geeks.ms/blogs/unai/archive/2008/07/17/multiple-entity-updates-with-entity-framework-ef-fetch-updates.aspx), but it doesn’t handle it natively. EF and nHinbernate create a session context and manage entity changes in memory until you tell the context to save to the database. LLBL Gen can also manage objects in a context and only write changes back to the db when told to, but it’s not it’s default mode of operation.

LLBL gen pro has handled multiple entity updates for a long time. It allows you to create a template entity that shows the values that should be set, and then define a predicate that defines what entities the the template changes should be applied to. Here’s an overly simple and unreal example.

///–update all persons in db who have city “Toledo” to “Cleveland”

PersonEntity personTemplate = new PersonEntity();
personTemplate.City = “Cleveland”

RelationPredicateBucket predicate= new RelationPredicateBucket();
predicate.PredicateExpression.Add(PersonFields.City == “Toledo”);

DataAccessAdapter adapter = new DataAccessAdapter();
int numberUpdated = adapter.UpdateEntitiesDirectly(personTemplate, predicate);

//—-

I’ve used LLBL myself for years because EF and it predecessors have been missing functionality like this. Here’s a link to more info about the LLBL gen updating. (http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/Adapter/gencode_usingentityclasses_adapter.htm#Modifyinganentity) and (http://www.llblgen.com/documentation/)

I think amount of code needed to accomplish a task is a somewhat useful metric for effort, but I would probably use code smith to generate code for the direct SQL approach in simple cases anyway, what would you suggest as a better proxy for time/effort required in creating a data access solution?

Thanks again for your comment!

However, putting aside the fact that I have created my own database class that substantially reduces the amount of code needed for direct SQL, I think it is a mistake to present a discussion like this and only look at the amount of code needed while completely ignoring the efficiency of the resulting code.

In this particular example, EF is probably a good choice because selecting rows into a List of data types is what EF does well. However, even in this case, all tests I’ve seen show that straight SQL will tend to be faster.

What about tasks like “UPDATE MyTable SET Downloads = Downloads + 1 WHERE ID=@ID”? EF/LINQ is simply a hindrance for such tasks.

Yes, EF and LINQ can be a useful tool for the developer. But I think the question about when to use them or direct SQL is a little more complex that this post suggests.