有二年没关注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 }