EntitySQL入门
2012-04-22 19:18 田志良 阅读(528) 评论(0) 编辑 收藏 举报Entity SQL 是 ADO.NET 实体框架 提供的 SQL 类语言,用于支持 实体数据模型 (EDM)。Entity SQL 可用于对象查询和使用 EntityClient 提供程序执行的查询。
l 关键字
Value关键字
ESQL 提供了 SELECT VALUE 子句以跳过隐式行构造。SELECT VALUE 子句中只能指定一项。在使用这样的子句时,将不会对 SELECT 子句中的项构造行包装器,并且可生成所要形状的集合,例如:SELECT VALUE it FROM NorthwindEntities.Customers as it
it关键字
it 出现在 ESQL 中, 查询对象的别名默认值 "it" 改成其他字符串,例如:
"SELECT VALUE it FROM NorthwindEntities.Customers as it " 。
l 注释:
Entity SQL 查询可以包含注释。注释行以两个短划线 (--) 开头。
"SELECT VALUE it FROM NorthwindEntities.Customers as it -- this a comment "
l Select查询
例如:
SELECT VALUE it FROM NorthwindEntities.Customers as it
l 参数
参数是在esql之外定义的变量,每个参数都有名称和类型,参数名称在查询表达式中定义,并以@符号作为前缀。例如:
Select VALUE c from NorthwindEntities.Customers as c where c.CustomerID=@customerID
l 聚合
Enity SQL不支持 * ,所以esql不支持count(*),而是使用count(0),例如:
Select count(0) from NorthwindEntities.Customers
l 分页SKIP/LIMIT
可以通过在 ORDER BY 子句中使用 SKIP 和 LIMIT 子子句执行物理分页。若要以确定的方式执行物理分页,应使用 SKIP 和 LIMIT。如果您只是希望以非确定的方式限制结果中的行数,则应使用 TOP。TOP 和 SKIP/LIMIT 是互斥的
使用SKIP/LIMIT分页,esql代码如下:
Select value c from NorthwindEntities.Customers as c order by c.CustomerID skip 0 limit 10
l TOP
SELECT 子句可以在可选的 ALL/DISTINCT 修饰符之后具有可选的 TOP 子子句。TOP 子子句指定查询结果中将只返回第一组行。esql代码如下:
Select top(10) c.CustomerID from NorthwindEntities.Customers as c order by c.CustomerID
l NULL处理
Null 文本与 Entity SQL 类型系统中的任何类型都兼容,可以使用cast进行类型转换,例如:
select cast(c.region as string) from NorthwindEntities.Customers as c order by c.CustomerID limit 10
其中, Nvarchar等可以成string,数字类型可以转成int32,其他的类型转换类似。如果无法完成转换,则将报异常。还有可以处理的方法有treat。
l 标识符
Entity SQL 提供两种标识符:简单标识符和带引号的标识符
简单标识符:Entity SQL 中的简单标识符是字母数字和下划线字符的序列。标识符的第一个字符必须是字母字符(a-z 或 A-Z)。
带引号的标识符:带引号的标识符是括在方括号 ([]) 中的任何字符序列。带中文的部分,请使用方括号包括起来,否则会报如下异常信息:“简单标识符“中文”只能包含基本拉丁字符。若要使用UNICODE 字符,请使用转义标识符”
正确的代码如下:
Select c.CustomerID as [中文字符] from NorthwindEntities.Customers as c order by c.CustomerID skip 0 limit 10
l ROW
Esql可使用row来构建匿名的结构类型的纪录。例如:
SELECT VALUE row(p.ProductID as ProductID,p.ProductName as ProductName) FROM NorthwindEntities.Products as p order by p.ProductID LIMIT 10
l Key
提取引用或实体表达式的键。如下esql语句,直接返回Customer表的主键:
string esql = "SELECT value key(c) FROM NorthwindEntities.Customers as c order by c.CustomerID LIMIT 10"
l CreateRef/ref/deref
CreateRef创建对实体集中的实体的引用。
ref返回对实体实例的引用,之后就可以当作实体来访问其属性,esql语句如下:
SELECT ref(c).CustomerID FROM NorthwindEntities.Customers as c order by c.CustomerID LIMIT 10
deref运算符取消引用一个引用值,并生成该取消引用的结果。
l CASE语句:
string esql = "using SqlServer;select case when len(trim(c.CustomerID))==0 then true else false end from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
l 运算符
Esql支持的运算符有:加+、减-、乘*、除/、取模%、-负号。Esql语句如下:
select 100/2 as OP from NorthwindEntities.Customers as c order by c.CustomerID limit 10
l 比较运算符
Esql支持的比较运算符有:=,>,>=,IS [NOT] NULL,<,[NOT] BETWEEN,!=,<>,[NOT] LIKE。Esql语句如下:
select value p from NorthwindEntities.Products as p where p.UnitPrice > 20 order by p.ProductID limit 10
l 逻辑运算符
Esql支持的逻辑运算符有:and(&&),not(!),or(||)。Esql语句如下:
select value p from NorthwindEntities.Products as p where p.UnitPrice > 20 and p.UnitPrice<100 order by p.ProductID limit 10
或
select value p from NorthwindEntities.Products as p where p.UnitPrice > 20 && p.UnitPrice<100 order by p.ProductID limit 10
l 字符串连接运算符。
加号 (+) 是 Entity SQL 中可将字符串串联起来的唯一运算符。Esql语句如下:
select c.CustomerID + c.ContactName from NorthwindEntities.Customers as c order by c.CustomerID limit 10
l 嵌套查询
在 Entity SQL 中,嵌套查询必须括在括号中,将不保留嵌套查询的顺序
select c1.CustomerID from( select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10) as c1
l 日期时间函数
Esql提供的日期时间函数有:CurrentDateTime()获取当前服务器的日期时间,还有month,day,year,second, Minute ,Hour等。例如:
select CurrentDateTime() from NorthwindEntities.Customers as c order by c.CustomerID limit 10
l 字符串函数
Esql提供的字符串函数有:Concat,IndexOf,Left,Length,Ltrim,Replace,Reverse,Rtrim,SubString,Trim,ToLower,ToUpper.例如:
select Reverse(p.ProductName) as ProductName from NorthwindEntities.Products as p order by p.ProductID limit 10
l GUID
Esql提供newguid()函数,产生一个新的Guid。例如:
select newguid() from NorthwindEntities.Customers as c order by c.CustomerID limit 10
l 数学函数:
Abs,Ceiling,Floor,Round
l 统计函数:
Avg,BigCount,Count,Max,Min,StDev,Sum
l 位计算函数
如果提供 Null 输入,则这些函数返回 Null。这些函数的返回类型与参数类型相同。如果函数采用多个参数,则这些参数必须具有相同的类型。若要对不同类型执行位运算,则需要显式强制转换为相同类型.
BitWiseAnd,BitWiseNot,BitWiseOr,BitWiseXor
l 命名空间
Entity SQL 引入命名空间以避免全局标识符(如类型名称、实体集、函数等)出现名称冲突。Entity SQL 中的命名空间支持与 .NET Framework 中的命名空间支持类似。
Entity SQL 提供两种形式的 USING 子句:限定命名空间(其中,提供较短的别名以表示命名空间)和非限定命名空间,如下例所示:
USING System.Data;
USING tsql = System.Data;
例如:
string esql = "using System; select cast(p.UnitPrice as Int32) from NorthwindEntities.Products as p order by p.ProductID limit 10 ";
string esql = "using System;using SqlServer; select (cast(p.UnitPrice as Int32)),SqlServer.ltrim(p.ProductName) as nameLen from NorthwindEntities.Products as p order by p.ProductID limit 10 ";
最后,简单说一下Esql与T-Sql的某些差异:
l Entity SQL 中的所有列引用都必须用表别名限定.
l Esql不支持Any,all限定运算符以及*运算
l Entity SQL 当前未提供对 DML 语句(insert、update、delete)的支持。
l Entity SQL 的当前版本未提供对 DDL 的支持。
ObjectQuery 类支持对 实体数据模型 (EDM) 执行 LINQ to Entities 和 Entity SQL 查询。ObjectQuery 还实现了一组查询生成器方法,这些方法可用于按顺序构造等效于 Entity SQL 的查询命令。下面是 ObjectQuery 的查询生成器方法以及等效的 Entity SQL 语句:
Distinct,Except,GroupBy,Intersect,OfType,OrderBy,Select,SelectValue,Skip,Top,Union,UnionAll,Where
每个查询生成器方法返回 ObjectQuery 的一个新实例。使用这些方法可以构造查询,而查询的结果集基于前面 ObjectQuery 实例序列的操作。下面来看具体的代码片断:
l Execute方法:
using (var edm = new NorthwindEntities())
{
string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query = edm.CreateQuery<Customers>(esql);
ObjectResult<Customers> results = query.Execute(MergeOption.NoTracking);
Assert.AreEqual(results.Count(), 10);
foreach (Customers c in query)
Console.WriteLine(c.CustomerID);
}
其中需要说明的是: MergeOption这个枚举类型的参数项,MergeOption有四种值分别是:
l AppendOnly: 只追加新实体,不修改以前获取的现有实体。这是默认行为。
l OverwriteChanges: 将 ObjectStateEntry 中的当前值替换为存储区中的值。这将使用服务器上的数据重写在本地所做的更改。
l PreserveChanges: 将替换原始值,而不修改当前值。这对于在发生开放式并发异常之后强制成功保存本地值非常有用。
l NoTracking: 将不修改 ObjectStateManager,不会获取与其他对象相关联的关系,可以改善性能。
l GetResultType方法:返回查询结果的类型信息.例如:
using (var edm = new NorthwindEntities())
{
string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query = edm.CreateQuery<Customers>(esql);
Console.WriteLine(query.GetResultType().ToString());
//输出结果为:
//NorthWindModel.Customers
}
l ToTraceString方法:获取当前执行的SQL语句。
l Where
实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql = "select value c from NorthwindEntities.Customers as c ";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql);
//使用ObjectParameter的写法
query1 = query1.Where("it.CustomerId=@customerid");
query1.Parameters.Add(new ObjectParameter("customerid", "ALFKI"));
//也可以这样写
//ObjectQuery<Customers> query2 = edm.Customers.Where("it.CustomerID='ALFKI'");
foreach (var c in query1)
Console.WriteLine(c.CustomerID);
//显示查询执行的SQL语句
Console.WriteLine(query1.ToTraceString());
}
l First/ FirstOrDefault
实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query = edm.CreateQuery<Customers>(esql);
Customers c1 = query.First();
Customers c2 = query.FirstOrDefault();
Console.WriteLine(c1.CustomerID);
Assert.IsNotNull(c2);
Console.WriteLine(c2.CustomerID);
}
l Distinct
实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql = "select value c.City from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<string> query = edm.CreateQuery<string>(esql);
query = query.Distinct();
foreach (string c in query)
{
Console.WriteLine("City {0}", c);
}
}
l Except:返回两个查询的差集。实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10";
ObjectQuery<Customers> query2 = edm.CreateQuery<Customers>(esql2);
query1 = query1.Except(query2);
foreach (Customers c in query1)
{
Console.WriteLine(c.Country);
//输出:UK
}
}
l Intersect:返回两个查询的交集。实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10";
ObjectQuery<Customers> query2 = edm.CreateQuery<Customers>(esql2);
query1 = query1.Intersect(query2);
foreach (Customers c in query1)
{
Console.WriteLine(c.Country);
}
}
l Union/UnionAll:返回两个查询的合集,包括重复项。其中UnionAll必须是相同类型或者是可以相互转换的。
l Include:可通过此方法查询出与相关的实体对象。实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql1 = "select value c from NorthwindEntities.Customers as c WHERE c.CustomerID ='HANAR'";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
query1 = query1.Include("Orders");
foreach (Customers c in query1)
{
Console.WriteLine("{0},{1}", c.CustomerID, c.Orders.Count);
//输出:HANAR,14
}
}
l OfType: 根据制定类筛选元素创建一个新的类型。此类型是要在实体模型中已定义过的。
l OrderBy
实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
query1.OrderBy("it.country asc,it.city asc");
//也可以这样写
//query1.OrderBy("it.country asc");
//query1.OrderBy("it.city asc");
foreach (Customers c in query1)
{
Console.WriteLine("{0},{1}", c.Country, c.City);
}
}
l Select
实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
ObjectQuery<DbDataRecord> records = query1.Select("it.customerid,it.country");
foreach (DbDataRecord c in records)
{
Console.WriteLine("{0},{1}", c[0], c[1]);
}
Console.WriteLine(records.ToTraceString());
//SQL输出:
//SELECT TOP (10)
//1 AS [C1],
//[Extent1].[CustomerID] AS [CustomerID],
//[Extent1].[Country] AS [Country]
//FROM [dbo].[Customers] AS [Extent1]
//ORDER BY [Extent1].[CustomerID] ASC
}
l SelectValue
实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
ObjectQuery<string> records = query1.SelectValue<string>("it.customerid");
foreach (string c in records)
{
Console.WriteLine("{0}", c);
}
Console.WriteLine(records.ToTraceString());
//SQL输出:
//SELECT TOP (10)
//[Extent1].[CustomerID] AS [CustomerID]
//FROM [dbo].[Customers] AS [Extent1]
//ORDER BY [Extent1].[CustomerID] ASC
}
l Skip/Top
实例代码如下:
using (var edm = new NorthwindEntities())
{
string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID ";
ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
query1 = query1.Skip("it.customerid asc", "10");
query1 = query1.Top("10");
foreach (Customers c in query1)
{
Console.WriteLine("{0}", c.CustomerID);
}
Console.WriteLine(query1.ToTraceString());
//SQL输出:
//SELECT TOP (10)
//[Extent1].[CustomerID] AS [CustomerID]
//FROM [dbo].[Customers] AS [Extent1]
//ORDER BY [Extent1].[CustomerID] ASC
}
本节,简单的介绍一下与ObjectQuery查询相关的语法,我个人觉得查询写法比较多,需要在日常的编程中去发现,在这里就不一一复述了。下节,将介绍EntityClient相关的内容。
System.Data.EntityClient 命名空间是 实体框架的 .NET Framework 数据提供程序。EntityClient 提供程序使用存储特定的 ADO.NET 数据提供程序类和映射元数据与实体数据模型进行交互。EntityClient 首先将对概念性实体执行的操作转换为对物理数据源执行的操作。然后再将物理数据源返回的结果集转换为概念性实体。
EntityClient下的类有以下几个:
l EntityConnection
l EntityCommand
l EntityConnectionStringBuilder
l EntityParameter
l EntityDataReader
l EntityParameterCollection
l EntityProviderFactory
l EntityTransaction
从类的名字上看,我们就知道它们的作用是什么了。在此,就不再一一解释了。直接通过实例代码来学习它们。
l EntityConnection:
实例代码1:
string con = "name = NorthwindEntities";
using (EntityConnection econn = new EntityConnection(con))
{
string esql = "Select VALUE c from NorthwindEntities.Customers as c where c.CustomerID='ALFKI'";
econn.Open();
EntityCommand ecmd = new EntityCommand(esql, econn);
EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);
if (ereader.Read())
{
Console.WriteLine(ereader["CustomerID"]);
}
Console.WriteLine(ecmd.ToTraceString());
}
上述代码中,需要注意的是EntityConnection的构造方法。其中,连接字符串写法有多很,如下:
写法1:
string con ="name = NorthwindEntities" ;其中的”NorthwindEntities”是配置文件中的连接字符串名称
写法2:
string con = System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString; 其中的”NorthwindEntities”是配置文件中的连接字符串名称
写法3:
string con = @" metadata=res://*/NorthWind.csdl|res://*/NorthWind.ssdl|res://*/NorthWind.msl;provider=System.Data.SqlClient;provider connection string='Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True'";其中的这些字符串是配置文件中的连接字符串的值
写法4:
NorthwindEntities edm = new NorthwindEntities();
string con = edm.Connection.ConnectionString;
上述写法中,基于写法简单、方便我比较推荐使用第1种或者第2种写法。
l EntityCommand
它具有的方法有:ExecuteDbDataReader、 ExecuteNonQuery 、 ExecuteReader 、 ExecuteScalar等。
实例代码2:
string con = "name = NorthwindEntities";
using (EntityConnection econn = new EntityConnection(con))
{
string esql = "Select VALUE c from NorthwindEntities.Customers as c where c.CustomerID='ALFKI'";
econn.Open();
EntityCommand ecmd = econn.CreateCommand();
ecmd.CommandText = esql;
EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);
if (ereader.Read())
{
Console.WriteLine(ereader["CustomerID"]);
}
Console.WriteLine(ecmd.ToTraceString());
}
代码中,EntityCommand创建方式和实例代码1中的稍有不同,相信大家都明白,就不多说了。
l EntityConnectionStringBuilder
实例代码3:
EntityConnectionStringBuilder esb = new EntityConnectionStringBuilder();
esb.Provider = "System.Data.SqlClient";
esb.Metadata = @"res://*/NorthWind.csdl|res://*/NorthWind.ssdl|res://*/NorthWind.msl";
esb.ProviderConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True";
EntityConnection econn = new EntityConnection(esb.ConnectionString)//创建连接
l EntityParameter
代码实例4:
string con = "name = NorthwindEntities";
using (EntityConnection econn = new EntityConnection(con))
{
string esql = "Select value c from NorthwindEntities.Customers as c order by c.CustomerID skip @start limit @end";
econn.Open();
EntityCommand ecmd = new EntityCommand(esql, econn);
EntityParameter p1 = new EntityParameter("start", DbType.Int32);
p1.Value = 0;
EntityParameter p2 = new EntityParameter("end", DbType.Int32);
p2.Value = 10;
ecmd.Parameters.Add(p1);
ecmd.Parameters.Add(p2);
EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (ereader.Read())
{
Console.WriteLine(ereader["CustomerID"]);
}
Console.WriteLine(ecmd.ToTraceString());
}
其中,参数是以@符号前缀的,EntityParameter实体参数类,除了可以直接构造出实例来。为实体命令对象添加参数,我们还可以直接调用Parameters.AddWithValue方法。如下代码:
ecmd.Parameters.AddWithValue("start", 0);
ecmd.Parameters.AddWithValue("end", 10);
我比较喜欢用上面的代码,简单、方便。
l EntityDataReader
string con = "name = NorthwindEntities";
using (EntityConnection econn = new EntityConnection(con))
{
string esql = "Select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10 ";
econn.Open();
EntityCommand ecmd = new EntityCommand(esql, econn);
EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (ereader.Read())
{
Console.WriteLine("{0},{1},{2},{3},{4}", ereader[0], ereader[1], ereader[2], ereader[3], ereader[4]);
}
Console.WriteLine(ecmd.ToTraceString());
}
需要注意的是:CommandBehavior.SequentialAccess;这个地方。不同的枚举项,对查询会有不同影响。枚举如下:
l Default 此查询可能返回多个结果集。在功能上等效于调用 ExecuteReader()。
l SingleResult 查询返回一个结果集。
l SchemaOnly 查询仅返回列信息。
l KeyInfo 此查询返回列和主键信息。
l SingleRow 查询应返回一行。
l SequentialAccess 提供一种方法,以便 DataReader 处理包含带有大二进制值的列的行。
l CloseConnection 在执行该命令时,如果关闭关联的 DataReader 对象,则关联的 Connection 对象也将关闭。
需要说明的是,如果使用SequentialAccess则需按顺序访问列,否则将抛异常。如下代码,将会抛异常:
while (ereader.Read())
{
//异常信息:从列序列号“1”开始读取的尝试无效。通过CommandBehavior.SequentialAccess,只能从列序列号“5”或更大值处开始读取
Console.WriteLine("{0},{1},{2},{3},{4}", ereader[4], ereader[1], ereader[2], ereader[3], ereader[0]);
}
l EntityTransaction:
事务类。目前由于ESQL仅提供查询的命令,没有提供对Insert、Update、Delete等的支持。所以,我觉得目前这个类基本没有用,(不可能我做查询还使用事务吧!)。
从上述简单的介绍,我们可以看到,EntityClient和SqlClient下的类基本上是一致的。所以很容易掌握。其他就不多说了。
增加:
方法1:使用AddToXXX(xxx)方法:实例代码如下:
using (var edm = new NorthwindEntities())
{
Customers c = new Customers { CustomerID = "c#", City = "成都市", Address = "中国四川省", CompanyName = "cnblogs", Country = "中国", Fax = "10086", Phone = "1008611", PostalCode = "610000", Region = "天府广场", ContactName = "风车车.Net" };
edm.AddToCustomers(c);
int result = edm.SaveChanges();
Assert.AreEqual(result, 1);
Customers addc = edm.Customers.FirstOrDefault(cc => cc.CustomerID == "c#");
Console.WriteLine("CustomerId={0},City={1}", addc.CustomerID, addc.City);
}
方法2:使用ObjectContext的AddObject(string entitySetName, object entity)方法。实例代码如下:
using (var edm = new NorthwindEntities())
{
Customers c = new Customers { CustomerID = "c2", City = "成都市2", Address = "中国四川省2", CompanyName = "cnblogs", Country = "中国", Fax = "10086", Phone = "1008611", PostalCode = "610000", Region = "天府广场", ContactName = "风车车.Net" };
edm.AddObject("Customers", c);
int result = edm.SaveChanges();
Assert.AreEqual(result, 1);
Customers addc = edm.Customers.FirstOrDefault(cc => cc.CustomerID == "c2");
Console.WriteLine("CustomerId={0},City={1}", addc.CustomerID, addc.City);
}
其中,在代码中,需要注意的是:AddObject方法中参数“entitySetName ”就是指对应实体名称,应该是:“Customers”,而不是“NorthwindEntities.Customers”;
l 更新:
using (var edm = new NorthwindEntities())
{
Customers addc = edm.Customers.FirstOrDefault(cc => cc.CustomerID == "c2");
addc.City = "CD";
addc.ContactName = "cnblogs";
addc.Country = "CN";
int result = edm.SaveChanges();
Assert.AreEqual(result, 1);
Customers updatec = edm.Customers.FirstOrDefault(cc => cc.CustomerID == "c2");
Console.WriteLine("CustomerId={0},City={1}", updatec.CustomerID, updatec.City);
}
其中,需要注意的是:不能去更新主键,否则会报“System.InvalidOperationException : 属性“xxx”是对象的键信息的一部分,不能修改。”
l 删除:
实例代码如下:
using (var edm = new NorthwindEntities())
{
Customers deletec = edm.Customers.FirstOrDefault(cc => cc.CustomerID == "c2");
edm.DeleteObject(deletec);
int result = edm.SaveChanges();
Assert.AreEqual(result, 1);
Customers c = edm.Customers.FirstOrDefault(cc => cc.CustomerID == "c2");
Assert.AreEqual(c, null);
}
l 事务:
实例代码如下:
NorthwindEntities edm = null;
System.Data.Common.DbTransaction tran = null;
try
{
edm = new NorthwindEntities();
edm.Connection.Open();
tran = edm.Connection.BeginTransaction();
Customers cst = edm.Customers.FirstOrDefault(cc => cc.CustomerID == "c#");
cst.Country = "CN";
cst.City = "CD";
edm.SaveChanges();
tran.Commit();
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
throw ex;
}
finally
{
if (edm != null && edm.Connection.State != System.Data.ConnectionState.Closed)
edm.Connection.Close();
}