Introduction: (Why Hybrid?)
Code First Approach is best for large system but it does not support the SPs in EF-Way.
Using SP would be just a point of Choice or it would be required due to other reasons (described next).
If you need both (i.e. Code First Approach and SP Support in EF-way) then my solution is to use the Hybrid Approach.
Ok, But Why this detailed article? Microsoft Clearly does NOT Allow to use EF in Hybrid Mode. So using Hybrid Mode of EF need some tricks.
Background
Why Hybrid, Explained (please skip it, if you already convinced)
- Normally for large projects, Code First Approach is preferred due to more flexibility, control and Database Versioning. But EF (code first / design first) is not good for:
- Concurrency Management: EF always require to fetch data from DB before posting data back to DB i.e. for each ‘Update’/’Insert’ you will have One additional ‘Select’ Call. Definitely, Performance Concerns.
- Update/Insert Multiple Records: Even though, you can add/update multiple entities without making call to DB and in the End you can call ‘SaveChanges’ to post all the changes to the DB. What does it do? It takes final set of changes and make individual calls to your each change – per change One DB call.
Definitely, Performance Concerns.
- Code First Approach need lots of coding to get the starting point.
- Solution to the above mentioned problems, a wise architect would not always rely on EF completely. To keep the design and software flexible with best possible performance, one will have the best option i.e. Stored-Procedures. SPs are very well supported in EF-6, earlier versions either didn’t had the support or limited or very limited support.
- So let have the SP along with Code First Approach, But Wait…
- Now What?
- Code First Approach does NOT support SP Mapping (along with other DB-Object Mapping like query views, defining views, Table Value Function, Temp Tables etc.)
- These mapping are supported in Database First Approach.
- If you want to map the SP in Code First, then maybe you can do it, the effort required is huge. This effort is neither documented nor tested by Microsoft or any other Developer. I am Too Lazy.
- There are another possibility of calling SP while using Code First Approach. It is sort of low level mechanism of calling SP, sort of calling manual SQL-Commands like “exec <sp_name>” Or calling SP similar to ADO.Net. Still the both ways, it need lots of code and you need to write it manually. I am Too Lazy.
- So we are on Dead End, We need good features of Code First Approach and to overcome limited features of Code First, we need SP support from Database Approach.
Side Note: (Can be just skipped)
For God Sake, ADO.Net is still betterJ. EF is the wrapper on top of ADO.Net i.e. as per feature set, ADO.Net is Supper Set of EF. Implementing all the possibilities of ADO.Net requires time to be developed in EF. The version 6 is fair enough good but still have limitation as I have described above.
Its ok, I am not going back to ADO.Net world, there are still benefits of EF over ADO.Net. (I am not explaining it here).
Using the code
If you start with Database first approach, the auto-generated DBContext Class, contains the following “OnModelCreating” overridden function:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
If you try to copy paste the POCOs n DBContext classes from auto-generated code (from database first approach of EF) to your implementation of Code First Approach of EF then you will get this exception.
How EF recognise the code is running as Code-First or DB-First? Simple the Connection String.
- Connection String for DB-First, contains additional information required to load the edmx file and related schema and mapping files etc.
- Connection string for Code-First, contains simple Connection string.
So the solution is simple,
- Create your model using DB-First Approach, and keep the connection string in the same format of DB-First Approach.
- Extend your POCOs or DB Context files as required. Extending POCOs and DBContext in separate files as Partial classes comply the SOLID’s Open-Close-Principle. This step give you the freedom of Code First Approach (without writing lots of code to start with).
- Since your target was to use SPs in EF-Way, you (most-probably) already imported all you SPs in your EF-Model (during step # 1 - above). This SP-Import do multiple things:
- Defines the function imports and schema in configuration files of DB-First EF Model
- Define Function Mapping and Param Info etc. in same configuration files.
- Add a Function in your Auto-Generated DBContext class. These function takes all the params of the corresponding SP.
- You are good to go with your Hybrid Approach, But still you need to consider following points:
- Normally you do not have any Select SP, because LINQ is being used instead. Using LINQ or Dynamic Linq is far more batter instead of writing Huge number of Select SP.
- Normally you have most of the Update/Insert/Delete’s Default SPs & few specialized SPs for same.
- Default SPs (Insert/Update), requires mostly all of the columns as SP param. It cause auto-generated SP Function will contain lots of function params (equal to the Fields in the corresponding Tables). These Params mostly already available in respective POCO.
- So why not replace the Param with a single POCO-based-Param. But wait, you cannot change the auto generated code. Its ok, use another partial class of your DBContext for Modified SP calls.
- Now you are good to go.
Points of Interest
Related Post Comming Soon:
1) How to maintain Concurency in EF using SPs (DB Design Approach and Auto Generate SPs for Update & Insert)
2) Saving Bulk Update/Insert Using SPs in EF - Table Value Function in Sql Server 2008+
History
Not Yet.
