In entity framework, mapping selected stored procedures

asp.net c# entity-framework stored-procedures

Question

My situation I'm using Entity Framework 4.1 and Visual Studio 2010 for this. My old database has several tables and stored procedures. I'm using MVC 3 to create an ASP.NET C# application.

I've used ADO.NET DbContext to implement the "database first" architecture, and as a result, I have an edmx with all the models, associations, and navigation properties neatly set up. I am able to map the insert, update, and delete operations to the appropriate models. Other saved procedures that I imported using the "Function Import" function. I am unable to connect my Select processes to select actions, however (select by id, select list, select by filter etc).

Since EF seems to employ lazy loading, I want the previously written stored procedures to be used when an object downloads its child objects. (The select processes, among other things, employ the 'ORDER BY' clause and take into consideration the 'IsDeleted' flag.)

I note in this article http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx that Linq to SQL permits drag and drop of SPs, which seems about as perfect as it could be for me.

Additionally, I've heard the phrase DefiningQuery. Do I really want this? 'Any changes made to the storage model, including creating queries, will be erased when you execute the Update Model Wizard,' the notice reads, doesn't sit well with me.

In conclusion, I want an object to apply my stored methods when it obtains its child objects.

Can I accomplish my objective using Entity Framework? Have I overlooked anything simple?

Alternatively, should I attempt to be very smart and alter the database Entity T4 template such that, for instance, my created Address model includes the following property:

public virtual ICollection<AddressLine> AddressLines { 
    get{
        DBWrapper _db = new DBWrapper();
        return _db.GetAddressLines(AddressID);
    } 
    set{}; 
}

where the custom function GetAddressLines imports a function, calls it, and does the necessary conversions.

1
5
10/31/2011 12:42:58 PM

Accepted Answer

It cannot be done. Your stored procedures may be manually executed after being imported as functions, but you cannot use your own stored procedures to replace EF's built-in query generation.

6
10/31/2011 2:18:31 PM

Popular Answer

Nevertheless, you kind of can.

Take your most basic select stored procedure (i.e., the one which is closest to "select * from mytable", and use it to define a view in your database. Use this "myview" instead of "mytable" in entity framework. Then, just like you did initially for your table, map your insert, update, and delete stored procs to this view-based object.

Finally, construct function imports to return collections of your object and utilize them for your more selective picks. Therefore, if you had an entity like a Person and a stored procedure named FetchPersonByAge(int), your entity would eventually have a static method called "GetByAge(int)". Then, you might use the following code to call it: people33 is set to Person.getByAge(33);

This is what I did, and it was fairly successful. This allowed me to comply with the old database's designers' requirement that all database access go via stored procedures rather than through user code directly accessing tables. See the article by Julie Lerman:

http://msdn.microsoft.com/en-us/data/gg699321.aspx

Dave



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow