Yes you can execute sprocs with EF Fluent API Code Only

  • [アーティクル]

OK let’s make this clear. You can totally call SQL Functions, SQL SPROCS, or any other raw SQL statement and use it in EF Code Only. What you don’t get is automatic or fluent API configuration statements that perform this mapping work for you and by default, no tracking occurs on the materialized objects (though you can manually do that work yourself to attach it), and the functions are not composable in LINQ statements that run on the server.

This is all capable because of the SqlQuery<T>() method exposed on the Database type in EntityFramework. In a nutshell it directly executes the provided SQL statement by marshaling it to the underlying provider connection and materializes the result set as types shaped as T. Mapping occurs EXACTLY as property names in the target type (as far as I know there’s no overriding this behavior) so that means if you have a property named CustomerID then you need a column with the exact same name in the SQL statement.

Note: The returned type does NOT need to actually be a class mapped in EF DbSet<T> types. It can be any type with a default constructor and properties with get/set (public visibility is not required).

So here are my simple recommendations:

  1. Create your context type per normal EF Fluent API practices by subclassing DbContext.
  2. Do whatever “normal” EF mapping and configuration work you do.
  3. For each sproc or other function you want to expose:
    1. Create a new method on your context type
    2. Declare any input parameters required for your SPROC, function, or SQL where clause as method parameters (usually this is 100% isomorphic to your sql but not required)
    3. Create your SQL statement
    4. Acquire a reference to the EF Database instance vis the protected base.Database property
    5. Pass the SQL statement + parameters to the SqlQuery<T>() method on the Database reference
    6. Realize or mutate or do whatever you want with the expression (or nothing) and return it to the client

In example imagine I have a sproc, GetPeople, that takes no parameters and returns a result set of Id int, FirstName varchar(50), LastName varchar(50) (doesn’t matter what the actual table/views are).

I have this class to represent the output

[DebuggerDisplay("FirstName = {FirstName}, LastName = {LastName}")]

public class Person

{

  public Guid Id { get; set; }

  public String FirstName { get; set; }

  public String LastName { get; set; }

}

 

 I could map the sproc the Person type with the following method on my DbContext based type

public virtual IEnumerable<Person> GetAllPeople()

{

  var results = this.Database.SqlQuery<Person>("execute dbo.GetPeople");

  return results;

}

 

That’s right folks. That is it. Of course you can see that you can call anything yourself here including your own SQL select statements or what not. It’s all based on convention.

I've put together the worlds easiest working example solution (including database) that you can use to run this example (and others such as parameterized requests and SQL Commands). I hope this clears up things for people out there.

EF Awesomeness.zip

Comments

  • Anonymous
    February 16, 2012
    The comment has been removed