Never SELECT * from Entity Framework called Stored Procedures

The Entity Framework can help us create an efficient Data Access Layer or a horribly inefficient one. 

It's worth mentioning that if we use Entity Framework to call a stored procedure that returns a database derived Entity using a SELECT * (star), we will have that horribly inefficient DAL.  Of course, as we've been told for years, SELECT * is 99.0% wrong with 1% for those dynamic field cases.  With EF calling a stored procedure, it's really wrong.

SQL Profiler will show us that the store procedure does return all records as fast as it can, barring a badly written stored procedure or low SQL server memory.  Entity Framework unfortunately does not assume all fields have returned.  EF then calls back with the defined primary key for each record.  This means that for a stored procedure that returns 1 million rows in one connection, actually returns 2 million rows with 2 million and one database server calls / connections from the application layer.  Try it on a 3 record table sometime to see that it calls SQL Server 4 times for three rows even when we use a stored procedure.  Enterprise DBA's will complain as they should.  If there is an obscure switch to avoid this, I couldn't find it.

The Moral of the Story:

• Always define fields from the SELECT statement even if SQL 2005 and greater does optimize it for us.
• Use SQL Profiler to look at the number of calls and the I/O count of each call.