public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public string ZipCode { get; set; }
}
public class MyContext : DbContext
{
static MyContext()
{
Database.SetInitializer(new MyContextInitializer());
}
public DbSet<Customer> Customers { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Add(new FunctionsConvention<MyContext>("dbo"));
}
[DbFunction("MyContext", "CustomersByZipCode")]
public IQueryable<Customer> CustomersByZipCode(string zipCode)
{
var zipCodeParameter = zipCode != null ?
new ObjectParameter("ZipCode", zipCode) :
new ObjectParameter("ZipCode", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext
.CreateQuery<Customer>(
string.Format("[{0}].{1}", GetType().Name,
"[CustomersByZipCode](@ZipCode)"), zipCodeParameter);
}
public ObjectResult<Customer> GetCustomersByName(string name)
{
var nameParameter = name != null ?
new ObjectParameter("Name", name) :
new ObjectParameter("Name", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.
ExecuteFunction<Customer>("GetCustomersByName", nameParameter);
}
}
public class MyContextInitializer : DropCreateDatabaseAlways<MyContext>
{
public override void InitializeDatabase(MyContext context)
{
base.InitializeDatabase(context);
context.Database.ExecuteSqlCommand(
"CREATE PROCEDURE [dbo].[GetCustomersByName] @Name nvarchar(max) AS " +
"SELECT [Id], [Name], [ZipCode] " +
"FROM [dbo].[Customers] " +
"WHERE [Name] LIKE (@Name)");
context.Database.ExecuteSqlCommand(
"CREATE FUNCTION [dbo].[CustomersByZipCode](@ZipCode nchar(5)) " +
"RETURNS TABLE " +
"RETURN " +
"SELECT [Id], [Name], [ZipCode] " +
"FROM [dbo].[Customers] " +
"WHERE [ZipCode] = @ZipCode");
}
protected override void Seed(MyContext context)
{
context.Customers.Add(new Customer {Name = "John", ZipCode = "98052"});
context.Customers.Add(new Customer { Name = "Natasha", ZipCode = "98210" });
context.Customers.Add(new Customer { Name = "Lin", ZipCode = "98052" });
context.Customers.Add(new Customer { Name = "Josh", ZipCode = "90210" });
context.Customers.Add(new Customer { Name = "Maria", ZipCode = "98074" });
context.SaveChanges();
}
}
class Program
{
static void Main()
{
using (var ctx = new MyContext())
{
const string zipCode = "98052";
var q = ctx.CustomersByZipCode(zipCode)
.Where(c => c.Name.Length > 3);
Console.WriteLine("TVF: CustomersByZipCode('{0}')", zipCode);
foreach (var customer in q)
{
Console.WriteLine("Id: {0}, Name: {1}, ZipCode: {2}",
customer.Id, customer.Name, customer.ZipCode);
}
const string name = "Jo%";
Console.WriteLine("\nStored procedure: GetCustomersByName '{0}'", name);
foreach (var customer in ctx.GetCustomersByName(name))
{
Console.WriteLine("Id: {0}, Name: {1}, ZipCode: {2}",
customer.Id, customer.Name, customer.ZipCode);
}
}
}
------------------------------------------------------------------------------------------------------------------------------------
This article is from: http://blog.3d-logic.com/2014/04/09/support-for-store-functions-tvfs-and-stored-procs-in-entity-framework-6-1/
}
