.NET:Entity Framework 笔记
有二年没关注EF,今天无意试了下发现跟主流的Hibernate等ORM框架越来越接近了,先看下Entity类的定义:
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace EFSample.Model { [Table("T_ORDER")] public class Order { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ID { set; get; } [StringLength(50)] [Column("CUSTOMER_NAME")] public String CustomerName { set; get; } [Column("AMOUNT")] public decimal Amount { set; get; } public virtual ICollection<OrderItem> OrderItems { set; get; } } }
1 using System; 2 using System.ComponentModel.DataAnnotations; 3 using System.ComponentModel.DataAnnotations.Schema; 4 5 namespace EFSample.Model 6 { 7 [Table("T_ORDER_ITEM")] 8 public class OrderItem 9 { 10 [Key] 11 [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 12 public int ID { set; get; } 13 14 [ForeignKey("Order")] 15 [Column("ORDER_ID")] 16 public int OrderID { set; get; } 17 18 [StringLength(50)] 19 [Column("PRODUCT_NAME")] 20 public String ProductName { set; get; } 21 22 [Column("PRICE")] 23 public Decimal Price { set; get; } 24 25 [Column("QUANTITY")] 26 public int Quantity { set; get; } 27 28 public virtual Order Order { set; get; } 29 30 31 } 32 }
光看Attribute,已经跟Hibernate的Annotation很相似了。
配置文件:
1 <?xml version="1.0" encoding="utf-8"?> 2 <configuration> 3 <configSections> 4 <section name="entityFramework" 5 type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> 6 </configSections> 7 8 <entityFramework> 9 <contexts> 10 <context type="EFSample.DAL.OrderContext, EFSample" disableDatabaseInitialization="false"> 11 <databaseInitializer type="EFSample.DAL.OrderInitializer, EFSample" /> 12 </context> 13 </contexts> 14 <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"> 15 <parameters> 16 <parameter value="v11.0" /> 17 </parameters> 18 </defaultConnectionFactory> 19 <providers> 20 <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> 21 </providers> 22 </entityFramework> 23 24 <startup> 25 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" /> 26 </startup> 27 28 <connectionStrings> 29 <!--连接字符串,使用SQLSERVER LocalDb--> 30 <add name="MyConn" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=OrderDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient" /> 31 </connectionStrings> 32 33 </configuration>
注:LocalDb在开发阶段很方便,但是试用中有一个小问题,如果把生成的mdf物理文件删除后,再次运行总是报错(不知道是不是个别现象),只能把Initial Catalog=OrderDatabase中的文件名改成其它名称(比如:Catalog=OrderDb),才能正常运行。
此外,NuGet Package Manager工具搞得象java的maven一样,可以自动联机下载所需的依赖项,Tools->NuGet Package Manager
输入
Install -Package EntityFramework
就能自动向project添加相关的dll引用
DbContext
1 using EFSample.Model; 2 using System.Data.Entity; 3 using System.Data.Entity.ModelConfiguration.Conventions; 4 5 namespace EFSample.DAL 6 { 7 public class OrderContext:DbContext 8 { 9 public OrderContext() : base("MyConn") { } 10 11 public DbSet<Order> Orders { set; get; } 12 13 public DbSet<OrderItem> OrderItems { set; get; } 14 15 protected override void OnModelCreating(DbModelBuilder modelBuilder) 16 { 17 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 18 } 19 } 20 }
示例代码:
1 using EFSample.DAL; 2 using EFSample.Model; 3 using System; 4 using System.Collections.Generic; 5 using System.Linq; 6 7 namespace EFSample 8 { 9 class Program 10 { 11 static void Main(string[] args) 12 { 13 using (var db = new OrderContext()) 14 { 15 16 //直接执行Sql语句 17 db.Database.ExecuteSqlCommand("delete from t_order"); 18 19 var orders = new List<Order>(){ 20 new Order{ CustomerName="jimmy.yang",Amount=200, OrderItems=new List<OrderItem>(){ 21 new OrderItem(){ Price=10, Quantity=20, ProductName="Mobile"} 22 }}, 23 new Order{ CustomerName="杨俊明",Amount=300, OrderItems=new List<OrderItem>(){ 24 new OrderItem(){ Price=15, Quantity=20, ProductName="架构之美"} 25 }}}; 26 27 //批量添加记录 28 db.Orders.AddRange(orders); 29 30 //提交到db 31 db.SaveChanges(); 32 33 //查询 34 var query = db.Orders.Where(c => c.CustomerName == "Jimmy.yang").AsQueryable(); 35 36 //输出Sql语句 37 Console.WriteLine(query.ToString()); 38 39 List<Order> orderEntities = query.ToList(); 40 41 foreach (var order in orderEntities) 42 { 43 Console.WriteLine(String.Format("ID:{0}/CustomerName:{1}/Amount:{2}/ItemCount:{3}", order.ID, order.CustomerName, order.Amount,order.OrderItems.Count)); 44 } 45 } 46 47 Console.WriteLine("ok!"); 48 Console.Read(); 49 } 50 } 51 }
输出:
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[CUSTOMER_NAME] AS [CUSTOMER_NAME],
[Extent1].[AMOUNT] AS [AMOUNT]
FROM [dbo].[T_ORDER] AS [Extent1]
WHERE N'Jimmy.yang' = [Extent1].[CUSTOMER_NAME]
ID:9/CustomerName:jimmy.yang/Amount:200/ItemCount:1
ok!
对Oracle的支持
MS默认并没有提供EF对Oracle的支持,需要到Oracle官网下载 http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html
下载比较慢,耐心等待,完成后,一路Next即可。
注意:tnsnames.ora文件的配置,一般在x:\app\client\Administrator\product\12.1.0\client_1\Network\Admin 目录下,参考内容如下:
1 XE = 2 (DESCRIPTION = 3 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.187.128)(PORT = 1521)) 4 (CONNECT_DATA = 5 (SERVER = DEDICATED) 6 (SERVICE_NAME = XE) 7 ) 8 )
然后在vs中创建ADO.NET Entity Data Model时就能连接到Oracle db了,目前尚不支持Code-First,只能使用EF5,估计EF6要等明年才会正式发布
使用细节,可参考官网教程:http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.htm#t10
Sequence的处理:
Oracle中没有自增主键,msdn和oracle建议用trigger+sequence来处理自增主键的问题,即:insert前写个触发器,检测主键是否为空,如果为空,则将sequece.nextval赋值给它,参见:https://social.msdn.microsoft.com/Forums/th-TH/73453344-bbb9-4904-b77a-a9ba807dcfd2/oracle-e-entityframework?forum=mvcpt
示例代码:
1 create sequence S_CD_TIPO minvalue 1 maxvalue 9999999 start with 1 increment by 1; 2 3 create or replace trigger tp_procedimento_trigger 4 before insert on tp_procedimento for each row 5 begin 6 if :new.cd_tipo is null then select s_cd_tipo.nextval into :new.cd_tipo from dual; 7 endif; 8 end;
但我个人并不喜欢这种做法,感觉对db有点“侵入”,我比较喜欢在c#代码层掌控一切的感觉,如果大家跟我有一样的癖好,可以这么干:
1 using System; 2 using System.Data; 3 using System.Data.Entity; 4 5 namespace EntityFrameworkTest 6 { 7 public static class SequenceHelper 8 { 9 public static int GetNextVal(this DbContext db,String sequenceName) { 10 if (db.Database.Connection.State != ConnectionState.Open) { 11 db.Database.Connection.Open(); 12 } 13 var cmd = db.Database.Connection.CreateCommand(); 14 cmd.CommandText = String.Format("select {0}.nextval from dual", sequenceName); 15 cmd.CommandType = CommandType.Text; 16 17 int result = 0; 18 int.TryParse(cmd.ExecuteScalar().ToString(),out result); 19 20 return result; 21 } 22 } 23 }
对DbContext写一个扩展方法,手动传入Sequence名称,然后在添加记录时,这样用:
1 using System; 2 using System.Linq; 3 4 namespace EntityFrameworkTest 5 { 6 class Program 7 { 8 static void Main(string[] args) 9 { 10 using (MyDbContext db = new MyDbContext()) 11 { 12 //insert 13 T_BAS_AGENT newAgent = new T_BAS_AGENT(); 14 newAgent.AGENT = "XYZ"; 15 newAgent.CYEAR = 2014; 16 newAgent.RECID = db.GetNextVal("SEQ_T_BAS_CARRIER"); 17 db.T_BAS_AGENT.Add(newAgent); 18 db.SaveChanges(); 19 20 //query 21 var findAgent = db.T_BAS_AGENT.SingleOrDefault(c => c.RECID == newAgent.RECID); 22 Console.WriteLine(string.Format("{0}/{1}/{2}", findAgent.RECID, findAgent.AGENT, findAgent.CYEAR)); 23 24 } 25 26 Console.Read(); 27 } 28 } 29 }
出处:http://yjmyzz.cnblogs.com
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。