we have created stored procedures on the SQL Server, we now move on to customize the EDM so that EF will use those stored procedures in place of dynamic queries. As mentioned earlier, the current version of EF either uses all stored procedures for all three data-changing operations (INSERT, UPDATE, DELETE) or uses none on the per-entity basis. As a matter of fact, we will get a compiler error if say, for an entity, only the INSERT stored procedure were implemented. Unlike the customization for SELECT stored procedures, we do not need to make any changes in CSDL.
So here is the customization necessary:
In SSDL, under the
In MSL, under the
During the above customization, I have these general observations:
In MSL, the generated
In MSL, the format of FunctionName attribute can be very confusion. We have to prefix it wity WORKModel.Store. to pass the compiler.
In MSL, for the stored procedure parameter mapping, if the column is a foreign key, we need to use the
For the DELETE stored procedures on tables with foreign keys - Driver and DriverName are such tables, we need to have the foreign key column as one of the input parameters even though the stored procedure itself does not use it. That is because EF needs to infer the order of deletions. I will discuss that further when I blog about the DELETE stored procedures.