Tweaking class generation in Entity Framework to use Stored Procedures

Question

I am looking for some guidance on how to tweak the T4 code generation of the entity classes in EntityModel.tt and possibly EntityModel.Context.tt (I'm using POCO classes) to force any queries on entities to use stored procedures (I already have the EntitySQL.tt generating all my Stored procedures)

For example, something like

context.MyEntities().Single(c => c.id == 5)

Would not generate SQL, but would call the SP `sp_GetMyEntities'

If you have a better strategy for achieving the same result I would be glad to learn about it.

UPDATE At this point I'm interested only in the READ part of CRUD. And there are no joins. Just simple loading of a single entity by ID. Theoretically the SP can be set to accept the ID of the loaded item as a parameter, to avoid loading the whole table (in this case there will be bn need for the .Single(c => c.id == 5) part and ID will be provided to the MyEntities object.

This is for a "database first" implementation

Two questions; first does every single entity have 4 associated stored procedures for CRUD, second how would stored procedures be used to join tables? — Erik Philips
– Erik Philips, Commented May 18, 2012 at 21:19
Don't. More often than not the EF SQL is more efficient than most people would write. You're causing a massive headache for the developer that replaces you. — Mike Miller
– Mike Miller, Commented May 18, 2012 at 21:22
At this point I'm interested only in the READ part of CRUD. And there are no joins. Just clean loading of a single entity by ID. In terms if effiency, lets assume I have good reasons to create this "headache" :) — Ron Harlev
– Ron Harlev, Commented May 18, 2012 at 21:38

Pawel · Accepted Answer · 2012-05-18 23:37:51Z

1

Code first does not support mapping to stored procedures. Model first and Database first flows do support mapping to stored procedures but it does not work the way you would like to use it since stored procedures are not compoosable (i.e. if you do .Single() the filtering will happen at best on the client not in the database - for performance reasons you want the filtering to happen on the database site since you probably don't want to bring the whole database to the client just to select one entity). Core libraries of Entity Framework 5 that ship with .NET Framework 4.5 support TVFs which are composable and should make it possible what you are after but again not in code first scenarios.

answered May 18, 2012 at 23:37

Pawel

31.7k4 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Tweaking class generation in Entity Framework to use Stored Procedures

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related