Stored Proc Queries : EF doesn't yet support...

Sun, 06 Jan 2013 14:14:42 -700

Author: kimj
Subject: 3876
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 GetProducts() {
    using (var context = new ProductDbContext("CodeFirstWalk")) {
      var products = context.Products.SqlQuery("dbo.Get_Products").ToList();
      return products;
    }
}
}

More info on named queries is here - http://drc.ideablade.com/xwiki/bin/view/Documentation/named-query.

Edited by kimj - 06-Jan-2013 at 2:15pm

Stored Proc Queries : HelloIs it possible to execute...

Sun, 06 Jan 2013 11:15:59 -700

Author: LowOrbit
Subject: 3876
Posted: 06-Jan-2013 at 11:15am

Hello

Is it possible to execute a StoredProcQuery:

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?
On the server side from an RPC function? If so, are there any special changes/configurations versus what is done in question 1?

Thanks!

DevForce Community Forum : Stored Proc Queries

Stored Proc Queries : EF doesn't yet support...

Stored Proc Queries : HelloIs it possible to execute...