EntityFramework数据库优先方式，很明显，我们需要先设计数据库模型。
假设我们需要设计一个零售系统，需要一些表结构：
生成数据库建表SQL
1 USE [RETAIL] 2 ; 3 4 IF SCHEMA_ID(N'STORE') IS NULL EXECUTE(N'CREATE SCHEMA [STORE]'); 5 ; 6 7 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[FK_OrderDetail_Order]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) 8 ALTER TABLE [STORE].[OrderDetail] DROP CONSTRAINT [FK_OrderDetail_Order] 9 ; 10 11 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[FK_OrderDetail_Product]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) 12 ALTER TABLE [STORE].[OrderDetail] DROP CONSTRAINT [FK_OrderDetail_Product] 13 ; 14 15 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[FK_Order_Customer]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) 16 ALTER TABLE [STORE].[Order] DROP CONSTRAINT [FK_Order_Customer] 17 ; 18 19 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[FK_Order_Shipper]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) 20 ALTER TABLE [STORE].[Order] DROP CONSTRAINT [FK_Order_Shipper] 21 ; 22 23 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[FK_Product_Category]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) 24 ALTER TABLE [STORE].[Product] DROP CONSTRAINT [FK_Product_Category] 25 ; 26 27 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[FK_Product_Supplier]') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) 28 ALTER TABLE [STORE].[Product] DROP CONSTRAINT [FK_Product_Supplier] 29 ; 30 31 32 33 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[Shipper]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 34 DROP TABLE [STORE].[Shipper] 35 ; 36 37 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[Supplier]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 38 DROP TABLE [STORE].[Supplier] 39 ; 40 41 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[Category]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 42 DROP TABLE [STORE].[Category] 43 ; 44 45 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[OrderDetail]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 46 DROP TABLE [STORE].[OrderDetail] 47 ; 48 49 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[Order]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 50 DROP TABLE [STORE].[Order] 51 ; 52 53 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[Product]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 54 DROP TABLE [STORE].[Product] 55 ; 56 57 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[STORE].[Customer]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 58 DROP TABLE [STORE].[Customer] 59 ; 60 61 62 CREATE TABLE [STORE].[Shipper] ( 63 [Id] bigint identity(1,1) NOT NULL, 64 [CompanyName] nvarchar(max) NOT NULL, 65 [ContactName] nvarchar(max) NOT NULL, 66 [Phone] nvarchar(1024) NOT NULL 67 ) 68 ; 69 70 CREATE TABLE [STORE].[Supplier] ( 71 [Id] bigint identity(1,1) NOT NULL, 72 [CompanyName] nvarchar(max) NOT NULL, 73 [ContactName] nvarchar(1024) NULL, 74 [ContactTitle] nvarchar(1024) NULL, 75 [Address] nvarchar(max) NULL 76 ) 77 ; 78 79 CREATE TABLE [STORE].[Category] ( 80 [Id] bigint identity(1,1) NOT NULL, 81 [Name] nvarchar(256) NOT NULL, 82 [Description] nvarchar(max) NULL 83 ) 84 ; 85 86 CREATE TABLE [STORE].[OrderDetail] ( 87 [Id] bigint identity(1,1) NOT NULL, 88 [OrderId] bigint NOT NULL, 89 [ProductId] bigint NOT NULL, 90 [Quantity] int DEFAULT 0 NOT NULL, 91 [Discount] float DEFAULT 0 NOT NULL 92 ) 93 ; 94 95 CREATE TABLE [STORE].[Order] ( 96 [Id] bigint identity(1,1) NOT NULL, 97 [CustomerId] bigint NOT NULL, 98 [ShipperId] bigint NOT NULL, 99 [OrderDate] datetime NOT NULL, 100 [TotalCharge] float DEFAULT 0 NOT NULL, 101 [Freight] float DEFAULT 0 NOT NULL, 102 [ShipDate] datetime NOT NULL, 103 [ShipAddress] nvarchar(max) NOT NULL 104 ) 105 ; 106 107 CREATE TABLE [STORE].[Product] ( 108 [Id] bigint identity(1,1) NOT NULL, 109 [CategoryId] bigint NOT NULL, 110 [SupplierId] bigint NOT NULL, 111 [Name] nvarchar(1024) NOT NULL, 112 [Description] nvarchar(max) NULL, 113 [UnitPrice] float DEFAULT 0 NOT NULL, 114 [UnitsInStock] int DEFAULT 0 NOT NULL 115 ) 116 ; 117 118 CREATE TABLE [STORE].[Customer] ( 119 [Id] bigint identity(1,1) NOT NULL, 120 [Name] nvarchar(256) NOT NULL, 121 [Address] nvarchar(max) NULL, 122 [Phone] nvarchar(256) NULL 123 ) 124 ; 125 126 127 ALTER TABLE [STORE].[Shipper] ADD CONSTRAINT [PK_Shipper] 128 PRIMARY KEY CLUSTERED ([Id]) 129 ; 130 131 ALTER TABLE [STORE].[Supplier] ADD CONSTRAINT [PK_Supplier] 132 PRIMARY KEY CLUSTERED ([Id]) 133 ; 134 135 ALTER TABLE [STORE].[Category] ADD CONSTRAINT [PK_Category] 136 PRIMARY KEY CLUSTERED ([Id]) 137 ; 138 139 ALTER TABLE [STORE].[OrderDetail] ADD CONSTRAINT [PK_OrderItem] 140 PRIMARY KEY CLUSTERED ([Id]) 141 ; 142 143 ALTER TABLE [STORE].[Order] ADD CONSTRAINT [PK_Order] 144 PRIMARY KEY CLUSTERED ([Id]) 145 ; 146 147 ALTER TABLE [STORE].[Product] ADD CONSTRAINT [PK_Product] 148 PRIMARY KEY CLUSTERED ([Id]) 149 ; 150 151 ALTER TABLE [STORE].[Customer] ADD CONSTRAINT [PK_Customer] 152 PRIMARY KEY CLUSTERED ([Id]) 153 ; 154 155 156 157 ALTER TABLE [STORE].[OrderDetail] ADD CONSTRAINT [FK_OrderDetail_Order] 158 FOREIGN KEY ([OrderId]) REFERENCES [STORE].[Order] ([Id]) 159 ; 160 161 ALTER TABLE [STORE].[OrderDetail] ADD CONSTRAINT [FK_OrderDetail_Product] 162 FOREIGN KEY ([ProductId]) REFERENCES [STORE].[Product] ([Id]) 163 ; 164 165 ALTER TABLE [STORE].[Order] ADD CONSTRAINT [FK_Order_Customer] 166 FOREIGN KEY ([CustomerId]) REFERENCES [STORE].[Customer] ([Id]) 167 ; 168 169 ALTER TABLE [STORE].[Order] ADD CONSTRAINT [FK_Order_Shipper] 170 FOREIGN KEY ([ShipperId]) REFERENCES [STORE].[Shipper] ([Id]) 171 ; 172 173 ALTER TABLE [STORE].[Product] ADD CONSTRAINT [FK_Product_Category] 174 FOREIGN KEY ([CategoryId]) REFERENCES [STORE].[Category] ([Id]) 175 ; 176 177 ALTER TABLE [STORE].[Product] ADD CONSTRAINT [FK_Product_Supplier] 178 FOREIGN KEY ([SupplierId]) REFERENCES [STORE].[Supplier] ([Id]) 179 ;
生成数据库表
然后生成ADO.NET Entity Data Model，
选择数据库，
定义模型名空间，
得到数据模型edmx文件，
我们先定义DomainModels.Customer，
1 public class Customer 2 { 3 public long Id { get; set; } 4 public string Name { get; set; } 5 public string Address { get; set; } 6 public string Phone { get; set; } 7 8 public override string ToString() 9 { 10 return string.Format("Id[{0}], Name[{1}], Address[{2}], Phone[{3}]", 11 Id, Name, Address, Phone); 12 } 13 }
定义ICustomerRepository接口，
1 public interface ICustomerRepository 2 { 3 void InsertCustomer(Customer customer); 4 void UpdateCustomer(Customer customer); 5 List<Customer> GetAllCustomers(); 6 List<Customer> GetCustomersByAddress(string address); 7 void DeleteAllCustomers(); 8 void DeleteCustomersByAddress(string address); 9 }
开始定义增删改查操作，
新增：
1 public void InsertCustomer(DomainModels.Customer customer) 2 { 3 using (RetailEntities context = new RetailEntities()) 4 { 5 Customer entity = Mapper.Map<DomainModels.Customer, Customer>(customer); 6 context.Customers.AddObject(entity); 7 context.SaveChanges(); 8 9 customer.Id = entity.Id; 10 } 11 }
修改：
1 public void UpdateCustomer(DomainModels.Customer customer) 2 { 3 using (RetailEntities context = new RetailEntities()) 4 { 5 Customer entity = context.Customers.AsQueryable().Single(c => c.Id == customer.Id); 6 7 entity.Name = customer.Name; 8 entity.Address = customer.Address; 9 entity.Phone = customer.Phone; 10 11 context.SaveChanges(); 12 } 13 }
查询：
1 public List<DomainModels.Customer> GetCustomersByAddress(string address) 2 { 3 using (RetailEntities context = new RetailEntities()) 4 { 5 List<Customer> entities = context.Customers.AsQueryable().Where(c => c.Address == address).ToList(); 6 List<DomainModels.Customer> customers = new List<DomainModels.Customer>(); 7 8 foreach (var entity in entities) 9 { 10 DomainModels.Customer customer = Mapper.Map<Customer, DomainModels.Customer>(entity); 11 customers.Add(customer); 12 } 13 14 return customers; 15 } 16 }
删除：
1 public void DeleteCustomersByAddress(string address) 2 { 3 using (RetailEntities context = new RetailEntities()) 4 { 5 List<Customer> entities = context.Customers.AsQueryable().Where(c => c.Address == address).ToList(); 6 7 foreach (var entity in entities) 8 { 9 context.DeleteObject(entity); 10 } 11 12 context.SaveChanges(); 13 } 14 }
CustomerRepository完整实现：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using AutoMapper; 6 using WhenEntityFrameworkMeetUnity.DataAccess; 7 8 namespace WhenEntityFrameworkMeetUnity 9 { 10 public class CustomerRepository : ICustomerRepository 11 { 12 public CustomerRepository() 13 { 14 Mapper.CreateMap<DomainModels.Customer, Customer>(); 15 Mapper.CreateMap<Customer, DomainModels.Customer>(); 16 } 17 18 #region ICustomerRepository Members 19 20 public void InsertCustomer(DomainModels.Customer customer) 21 { 22 using (RetailEntities context = new RetailEntities()) 23 { 24 Customer entity = Mapper.Map<DomainModels.Customer, Customer>(customer); 25 context.Customers.AddObject(entity); 26 context.SaveChanges(); 27 28 customer.Id = entity.Id; 29 } 30 } 31 32 public void UpdateCustomer(DomainModels.Customer customer) 33 { 34 using (RetailEntities context = new RetailEntities()) 35 { 36 Customer entity = context.Customers.AsQueryable().Single(c => c.Id == customer.Id); 37 38 entity.Name = customer.Name; 39 entity.Address = customer.Address; 40 entity.Phone = customer.Phone; 41 42 context.SaveChanges(); 43 } 44 } 45 46 public List<DomainModels.Customer> GetAllCustomers() 47 { 48 using (RetailEntities context = new RetailEntities()) 49 { 50 List<Customer> entities = context.Customers.AsQueryable().ToList(); 51 List<DomainModels.Customer> customers = new List<DomainModels.Customer>(); 52 53 foreach (var entity in entities) 54 { 55 DomainModels.Customer customer = Mapper.Map<Customer, DomainModels.Customer>(entity); 56 customers.Add(customer); 57 } 58 59 return customers; 60 } 61 } 62 63 public List<DomainModels.Customer> GetCustomersByAddress(string address) 64 { 65 using (RetailEntities context = new RetailEntities()) 66 { 67 List<Customer> entities = context.Customers.AsQueryable().Where(c => c.Address == address).ToList(); 68 List<DomainModels.Customer> customers = new List<DomainModels.Customer>(); 69 70 foreach (var entity in entities) 71 { 72 DomainModels.Customer customer = Mapper.Map<Customer, DomainModels.Customer>(entity); 73 customers.Add(customer); 74 } 75 76 return customers; 77 } 78 } 79 80 public void DeleteAllCustomers() 81 { 82 using (RetailEntities context = new RetailEntities()) 83 { 84 List<Customer> entities = context.Customers.AsQueryable().ToList(); 85 86 foreach (var entity in entities) 87 { 88 context.DeleteObject(entity); 89 } 90 91 context.SaveChanges(); 92 93 //context.ExecuteStoreCommand("TRUNCATE TABLE [RETAIL].[STORE].[Customer]"); 94 } 95 } 96 97 public void DeleteCustomersByAddress(string address) 98 { 99 using (RetailEntities context = new RetailEntities()) 100 { 101 List<Customer> entities = context.Customers.AsQueryable().Where(c => c.Address == address).ToList(); 102 103 foreach (var entity in entities) 104 { 105 context.DeleteObject(entity); 106 } 107 108 context.SaveChanges(); 109 } 110 } 111 112 #endregion 113 } 114 }
具体使用：
1 ICustomerRepository customerRepository = new CustomerRepository(); 2 3 // =============== 增 =============== 4 Console.ForegroundColor = ConsoleColor.DarkRed; 5 6 DomainModels.Customer customer1 = new DomainModels.Customer() 7 { 8 Name = "Dennis Gao", 9 Address = "Beijing", 10 Phone = "18888888888", 11 }; 12 customerRepository.InsertCustomer(customer1); 13 Console.WriteLine(customer1);
完整代码和索引
EntityFramework用法探索系列