New Posts New Posts RSS Feed: Stored Proc Queries
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Stored Proc Queries
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
LowOrbit View Drop Down
Newbie
Newbie


Joined: 17-Aug-2007
Location: United States
Posts: 20 		Post Options Post Options   Quote LowOrbit Quote  Post ReplyReply Direct Link To This Post Topic: Stored Proc Queries
    Posted: 06-Jan-2013 at 11:15am
Hello
 
Is it possible to execute a StoredProcQuery:
 
  1. When you model is done in Code First? I see examples when doing it Model First but cannot find any examples when the model is Code First. If so, could you provide a simple example of the mappings, etc., that need to be done?
  2. On the server side from an RPC function? If so, are there any special changes/configurations versus what is done in question 1?

Thanks!
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 06-Jan-2013 at 2:14pm
EF doesn't yet support mapping to stored procedures, but it does have a back door which you can take advantage of in DevForce.  The "back door" is described here - http://msdn.microsoft.com/en-us/data/jj592907 - and involves using the DbSet.SqlQuery method.  The SqlQuery method allows you to write a raw SQL query or call a stored procedure.
 
Since stored procedures can't be mapped to your model you can't use the DevForce StoredProcQuery, which under the hood expects to call the ObjectContext.ExecuteFunction method.
 
You can use an RPC method, although it might be some work to push entities into an EntityCacheState and return that.  Using an RPC method would be best for stored procedures not returning entities.
 
It's probably best to use DevForce's named query support.  DevForce supports both "default" and custom named queries, and includes support for parameters.  Using a named query, you provide the actual implementation for the query, and DevForce will call this when you execute a query from the client EntityManager.
 
You write a simple query provider, and in a named query method call the DbSet.SqlQuery method with your SQL.  For example, here's a query provider which implements the default named query for the Product entity (from the CodeFirstWalk sample) and calls a stored procedure named "Get_Products":
 
[EnableClientAccess]
public class NamedQueryServiceProvider {
  public IEnumerable<Product> GetProducts() {
    using (var context = new ProductDbContext("CodeFirstWalk")) {
      var products = context.Products.SqlQuery("dbo.Get_Products").ToList();
      return products;
    }
  }
}
 
 


Edited by kimj - 06-Jan-2013 at 2:15pm
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down