CODEWITHSUNDEEP
c#asp.netentity-frameworkstored-procedures
Mark_Gibson
Mark_Gibson

Reputation: 932

Mapping select stored procedures in entity framework

My scenario I'm using Visual Studio 2010 with Entity Framework 4.1 I have a legacy database with many tables and many stored procedures. I'm writing an ASP.NET C# program using MVC 3

I have adopted the 'database first' design using ADO.NET DbContext so I have an edmx with all the models and associations and navigation properties nicely set up. I can map the insert, update, delete procedures to the relevant models. I've used 'Function Import' to import other stored procedures. However, I can't find a way to map my Select procedures to select actions (select by id, select list, select by filter etc).

EF seems to use lazy loading so what I want to happen is when an object fetches its child objects it uses the stored procedures already written. (The select procedures take into account an 'IsDeleted' flag, and use the 'ORDER BY' clause, amongst others)

I see from 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 allows drag and drop of SPs, which sounds, more or less, exactly what I want.

I've also come across the term DefiningQuery. http://msdn.microsoft.com/en-us/library/cc982038.aspx Is this what I want? I don't like the note 'Any changes made to the storage model, including defining queries, will be overwritten when you run the Update Model Wizard.'

In summary, what I want to happen is when an object fetches its child objects it uses my stored procedures.

Can I achieve my goal using Entity Framework? Have I missed something obvious?

Or should I try to be really clever and modify the db Entity T4 template, so that, for example, my generated Address model has this property:

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

where GetAddressLines is custom function that calls a function import and does the neccessary conversions.

Upvotes: 5

Views: 7617

Answers (2)

Dave Smalley
Dave Smalley

Reputation: 21

Except that you can, sort of.

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. Have entity framework use this "myview" instead of "mytable". Then map your insert, update and delete stored procs for this view-based entity as you did originally for your table.

Finally, use function imports for your more selective selects, and define them to return collections of your entity. So if you had something like a Person entity, and you had a stored proc called something like FetchPersonByAge(int), your entity would end up with a static method called something like "GetByAge(int)". You could then call it in code like this: var people33 = Person.getByAge(33);

I have done this, and it worked quite well, allowing me to respect a legacy database's designers demand that all database access be through their stored procs, and no user code directly accessing tables. See Julie Lerman's article:

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

Dave

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364299

It is not possible. You can import your stored procedures as function imports and manually execute them but you cannot replace queries generated by EF with custom stored procedures.

Upvotes: 7

Related Questions