《Entity Framework 6 Recipes》中文翻译——第十章EntityFramework存储过程处理（一）

　　在这一章中，我们探讨了一些方案，特别是专注于使用存储过程与实体框架。在这本书中，我们使用存储过程在其他处理方案，但通常他们是在实施插入，更新和删除操作的上下文中。在本章中，我们将向您展示如何使用存储过程所暴露的数据的几种方法。

返回一个实体集合

问题

　　您希望使用代码第二的方法从存储过程获得实体集合。

解决方案

　　代码第二是指应用代码第一技术来模拟一个现有的数据库模式的做法。让我们说，你有一个POCO模型。

 public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string Company { get; set; }
        public string ContactTitle { get; set; }
    }

我们还有DbContext类

public partial class EntitiesContext : DbContext
    {
        public EntitiesContext()
            : base("name=EntitiesContext")
        {
        }

        public virtual DbSet<Customer> Customers { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Types<Customer>()
                        .Configure(c =>
                        {
                            c.HasKey(cust => cust.CustomerId);
                            c.Property(cust => cust.CustomerId)
                            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                            c.Property(cust => cust.Name)
                             .HasMaxLength(50);
                            c.Property(cust => cust.Company)
                             .HasMaxLength(50);
                            c.Property(cust => cust.ContactTitle)
                             .HasMaxLength(50);
                            c.ToTable("Customer");
                        });

        }
    }

在数据库中，我们定义存储过程，返回客户的公司名称和客户的标题了

create procedure [dbo].[GetCustomers]
(@Company nvarchar(50),@ContactTitle nvarchar(50))
as
begin
select * from Customer where
(@Company is null or Company = @Company) and
(@ContactTitle is null or ContactTitle = @ContactTitle)
End

要使用“获取客户存储过程”的模型，做以下操作：

1、创建一个新的公共方法GetCustomers在DbContext子类，以两个字符串参数，并返回一个客户对象。

 public ICollection<Customer> GetCustomers(string company, string contactTitle)
        {
            throw new NotImplementedException();
        }

2、GetCustomers()方法通过在数据库的上下文中调用数据库的SQL查询。

public ICollection<Customer> GetCustomers(string company, string contactTitle)
        {
            return Database.SqlQuery<Customer>("EXEC GetCustomers @Company, @ContactTitle"
                                  , new SqlParameter("Company", company)
                                  , new SqlParameter("Contact Title", contactTitle))
                                  .ToList();

        }

3、在Program中调用

class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EntitiesContext())
            {
                var c1 = new Customer
                {
                    Name = "Robin Steele",
                    Company = "Go Shop Now.com",
                    ContactTitle = "CEO"
                };
                var c2 = new Customer
                {
                    Name = "Orin Torrey",
                    Company = "Go Shop Now.com",
                    ContactTitle = "Sales Manager"
                };
                var c3 = new Customer
                {
                    Name = "Robert Lancaster",
                    Company = "Go Shop Now.com",
                    ContactTitle = "Sales Manager"
                };
                var c4 = new Customer
                {
                    Name = "Julie Stevens",
                    Company = "Go Shop Now.com",
                    ContactTitle = "Sales Manager"
                };
                context.Customers.Add(c1);
                context.Customers.Add(c2);
                context.Customers.Add(c3);
                context.Customers.Add(c4);
                context.SaveChanges();

            }
            using (var context = new EntitiesContext())
            {
                var allCustomers = context.GetCustomers("Go Shop Now.com", "Sales Manager");
                Console.WriteLine("Customers that are Sales Managers at Go Shop Now.com");
                foreach (var c in allCustomers)
                {
                    Console.WriteLine("Customer: {0}", c.Name);
                }

            }
           // Console.ReadKey();
        }
    }

结果