phpcms网站音乐代码存放在什么位置,济南seo全网营销,wordpress添加主题,陕西网站开发公司环境说明:EntityFramework 6.1.3和.Net Framework4.5性能注意事项:https://msdn.microsoft.com/zh-cn/library/cc853327.aspx比较精髓的一点:查询执行的各个阶段中的准备查询,每个唯一查询一次。包括编写查询命令、基于模型和映射元数据生成命令树和定义所返回数据的形状的成本… 环境说明:EntityFramework 6.1.3和.Net Framework4.5性能注意事项:https://msdn.microsoft.com/zh-cn/library/cc853327.aspx比较精髓的一点:查询执行的各个阶段中的准备查询,每个唯一查询一次。包括编写查询命令、基于模型和映射元数据生成命令树和定义所返回数据的形状的成本。 因为实体 SQL查询命令和 LINQ 查询现已缓存所以以后执行相同查询所需的时间较少。 如果有缓存的话,那么查询命令转成sql语句的性能会进一步提高,是不是ORM的效率更接近Ado.Net了呢? 性能注意点:此处参考了 http://www.cnblogs.com/jake1/archive/2013/04/25/3043664.htmla.在数据库里面分页b.延迟加载要合理使用c.需要连表的地方要连表查询d.查询数据库的次数和发出的sql语句的数量和长度e.NoTracking的使用 表ContactInfo,GroupInfo说明: CREATE TABLE [dbo].[ContactInfo]( [ID] [int] IDENTITY(1,1) NOT NULL, [ContactId] [nvarchar](128) NOT NULL, [IsDelete] [int] NOT NULL, [Account] [nvarchar](64) NOT NULL, [ContactName] [nvarchar](50) NOT NULL, [CommonMobile] [nvarchar](50) NULL, [HeadPortrait] [nvarchar](256) NULL, [AttFile] [nvarchar](256) NULL, [GroupId] [int] NULL, CONSTRAINT [PK_ContactInfo] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX OFF, STATISTICS_NORECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[GroupInfo]( [GroupId] [int] IDENTITY(1,1) NOT NULL, [GroupName] [nvarchar](300) NOT NULL, CONSTRAINT [PK_GroupInfo] PRIMARY KEY CLUSTERED ( [GroupId] ASC )WITH (PAD_INDEX OFF, STATISTICS_NORECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS ON) ON [PRIMARY] ) ON [PRIMARY] 1.0 分页查询 c#语句: var db new PhoneBookEntities();
db.GroupInfo.Where(c c.GroupName.Length2).OrderByDescending(c c.GroupId).Skip(2).Take(3).ToArray(); sql语句: SELECT TOP (3)
[Filter1].[GroupId] AS [GroupId],
[Filter1].[GroupName] AS [GroupName]FROM ( SELECT [Extent1].[GroupId] AS [GroupId], [Extent1].[GroupName] AS [GroupName],
row_number() OVER (ORDER BY [Extent1].[GroupId] DESC) AS [row_number]FROM [dbo].[GroupInfo] AS [Extent1]WHERE (LEN([Extent1].[GroupName])) 2) AS [Filter1]WHERE [Filter1].[row_number] 2ORDER BY [Filter1].[GroupId] DESC 2.0 FirstOrDefault,First c#语句: var db new PhoneBookEntities();
db.GroupInfo.FirstOrDefault(c c.GroupId 1); sql语句: SELECT TOP (1)
[Extent1].[GroupId] AS [GroupId],
[Extent1].[GroupName] AS [GroupName]FROM [dbo].[GroupInfo] AS [Extent1]WHERE 1 [Extent1].[GroupId] 延迟加载: var db new PhoneBookEntities(); var ci db.ContactInfo.FirstOrDefault(c c.ID 9); /* 此时产生的sql:*/ SELECT TOP (1)
[Extent1].[ID] AS [ID],
[Extent1].[ContactId] AS [ContactId],
[Extent1].[IsDelete] AS [IsDelete],
[Extent1].[Account] AS [Account],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[CommonMobile] AS [CommonMobile],
[Extent1].[HeadPortrait] AS [HeadPortrait],
[Extent1].[AttFile] AS [AttFile],
[Extent1].[GroupId] AS [GroupId]FROM [dbo].[ContactInfo] AS [Extent1]WHERE 9 [Extent1].[ID] var gn ci.GroupInfo.GroupName; /* (运行到此行c#代码才会)产生sql:*/ exec sp_executesql NSELECT
[Extent1].[GroupId] AS [GroupId],
[Extent1].[GroupName] AS [GroupName]
FROM [dbo].[GroupInfo] AS [Extent1]
WHERE [Extent1].[GroupId] EntityKeyValue1,NEntityKeyValue1 int,EntityKeyValue11 如果是一条数据,用延迟加载是OK的.如果上面查询有多条如10条结果,每条结果都使用到GroupInfo属性,那么一共会有11条sql请求.效率低.应该使用连表,一条sql搞定.写法如下. 第一种写法 Join: 那如果是多条数据,应使用预加载.c#语句: var db new PhoneBookEntities(); var ci db.ContactInfo.Where(c c.ID 3).Join(db.GroupInfo,cc.GroupId,gg.GroupId,(c,g)new{c.ContactName,g.GroupName});foreach (var item in ci)
{MessageBox.Show(item.ContactName - item.GroupName);
} sql语句: SELECT [Extent1].[ID] AS [ID],
[Extent1].[ContactName] AS [ContactName],
[Extent2].[GroupName] AS [GroupName]FROM [dbo].[ContactInfo] AS [Extent1]INNER JOIN [dbo].[GroupInfo] AS [Extent2] ON [Extent1].[GroupId] [Extent2].[GroupId]WHERE [Extent1].[ID] 3 说明:GroupJoin的用法,和Join使用类似.区别在于第四个参数resultSelector. Join的第四个参数是 FuncContactInfo,GroupInfo,anonymous type.GroupJoin的第四个参数是 FuncContactInfo,IEnumerableGroupInfo,anonymous type.使用场景:联系人和他的好友.联系人一张表,好友关系一张表.联系人表和好友关系表做连接,查出多个联系人数据(包含他的好友),就应该使用GroupJoin.简单说:public partial class ContactInfo{public int ID { get; set; } public string ContactName { get; set; } public Nullableint GroupId { get; set; } public virtual GroupInfo GroupInfo { get; set; }}ContactInfo和GroupInfo一对一,该用Join;如果是这种情况(仅仅是假设)public partial class ContactInfo{public int ID { get; set; } public string ContactName { get; set; } public Nullableint GroupId { get; set; } public virtual ListGroupInfo GroupInfo { get; set; }}ContactInfo和GroupInfo一对多,该用GroupJoin; 第二种写法 Include: 注意:数据库设计ContactInfo,GroupInfo 要有主外键关系.c#语句: var db new PhoneBookEntities();var ci db.ContactInfo.Include(GroupInfo).Where(c c.ID 3).Select(c new { c.ContactName, c.GroupInfo.GroupName }); //或者 Include(cc.GroupInfo)foreach (var item in ci)
{MessageBox.Show(item.ContactName - item.GroupName);
} sql语句: SELECT [Extent1].[ID] AS [ID],
[Extent1].[ContactName] AS [ContactName],
[Extent2].[GroupName] AS [GroupName]FROM [dbo].[ContactInfo] AS [Extent1]LEFT OUTER JOIN [dbo].[GroupInfo] AS [Extent2] ON [Extent1].[GroupId] [Extent2].[GroupId]WHERE [Extent1].[ID] 3 3.0 Add c#语句: var db new PhoneBookEntities();var giModel new GroupInfo();
giModel.GroupName Test;
db.GroupInfo.Add(giModel);
db.SaveChanges();//Add方法之后,会把数据库表记录中的GroupId给giModel. sql语句: exec sp_executesql NINSERT [dbo].[GroupInfo]([GroupName])
VALUES (0)
SELECT [GroupId]
FROM [dbo].[GroupInfo]
WHERE ROWCOUNT 0 AND [GroupId] scope_identity(),N0 nvarchar(300),0NTest 4.0 AddRange c#语句: var db new PhoneBookEntities();var gi new GroupInfo[] { new GroupInfo() { GroupName g1 }, new GroupInfo() { GroupName g2 }, new GroupInfo() { GroupName g3 }, };
db.GroupInfo.AddRange(gi);
db.SaveChanges(); sql语句: exec sp_executesql NINSERT [dbo].[GroupInfo]([GroupName])
VALUES (0)
SELECT [GroupId]
FROM [dbo].[GroupInfo]
WHERE ROWCOUNT 0 AND [GroupId] scope_identity(),N0 nvarchar(300),0Ng1exec sp_executesql NINSERT [dbo].[GroupInfo]([GroupName])
VALUES (0)
SELECT [GroupId]
FROM [dbo].[GroupInfo]
WHERE ROWCOUNT 0 AND [GroupId] scope_identity(),N0 nvarchar(300),0Ng2... 共执行3次.但是连接只打开关闭了1次. 5.0 Remove c#语句: var db new PhoneBookEntities();var ci db.GroupInfo.FirstOrDefault(c c.GroupId 214);
db.GroupInfo.Remove(ci);
db.SaveChanges(); sql语句: SELECT TOP (1)
[Extent1].[GroupId] AS [GroupId],
[Extent1].[GroupName] AS [GroupName]FROM [dbo].[GroupInfo] AS [Extent1]WHERE 214 [Extent1].[GroupId]exec sp_executesql NDELETE [dbo].[GroupInfo]
WHERE ([GroupId] 0),N0 int,0214 感觉繁琐啦?解决方案,1,自己定义方法;2,引用EntityFramework.Extended 来源https://www.nuget.org/packages/EntityFramework.Extended/或者在当前项目里,引用点击右键,选择管理NuGet程序包,联机搜索并下载.引用之后操作就简单了. c#语句: var db new PhoneBookEntities();
db.GroupInfo.Delete(c c.GroupName Test);//过时的方法db.GroupInfo.Where(c c.GroupName Test).Delete();//如果查到的记录数为0,也不报错.上边的先查 FirstOrDefault 后删 Remove,你猜猜报错吗db.SaveChanges(); sql语句: DELETE [dbo].[GroupInfo]FROM [dbo].[GroupInfo] AS j0 INNER JOIN (SELECT [Extent1].[GroupId] AS [GroupId]FROM [dbo].[GroupInfo] AS [Extent1]WHERE NTest [Extent1].[GroupName]) AS j1 ON (j0.[GroupId] j1.[GroupId]) 一条sql语句搞定删除. 6.0 更新操作 基于EntityFramework.Extended的更新操作.c#语句: db.GroupInfo.Where(c c.GroupName.Contains(g)).Update(c new GroupInfo() { GroupName c.GroupName!});//此处没有db.SaveChanges();,一样执行了操作. sql语句: UPDATE [dbo].[GroupInfo] SET [GroupName] [GroupName] N! FROM [dbo].[GroupInfo] AS j0 INNER JOIN (SELECT [Extent1].[GroupId] AS [GroupId]FROM [dbo].[GroupInfo] AS [Extent1]WHERE [Extent1].[GroupName] LIKE N%g%) AS j1 ON (j0.[GroupId] j1.[GroupId]) 也是一条sql语句搞定批量修改. (注意:update语句中只set了GroupName字段;不用EntityFramework.Extended,用EF的先查后改,sql语句也是只set了GroupName字段.更特殊的情况,以下例子: var gi db.GroupInfo.FirstOrDefault(c c.GroupId 219 );//此条记录的GroupName为Testgi.GroupName Test;
db.SaveChanges(); EF会自动优化,最终结果只有一个select语句,而没有update语句. 此处细节,赞! 对比NHibernate 4,以下两行代码产生的sql语句会set Product表的[所有]字段var pl session.QueryProduct().FirstOrDefault(c c.Name cnblogs);pl.Name ICE;)如果先查出来要更改的数据,再修改.也是可以的. 但是从效率考虑,不管是c#写法还是产生的sql语句,基于EntityFramework.Extended的更新操作更优. 7.0 EntityFramework.Extended中Future的使用 c#语句: var db new PhoneBookEntities();var fci db.ContactInfo.Where(c c.ID 1).FutureFirstOrDefault();var fgi db.GroupInfo.Where(c c.GroupId 2).FutureFirstOrDefault();
ContactInfo ci fci.Value;
GroupInfo gi fgi.Value; //采用Future的写法,不会立即查询数据库.只要调用结果的任意一个 .ToList,.ToArray或者.Value ,才会查数据库.并且只发一个请求(Query #1 Query #2 拼接好后发给数据库,一起执行语句). sql语句: -- Query #1SELECT TOP (1)
[Extent1].[ID] AS [ID],
[Extent1].[ContactId] AS [ContactId],
[Extent1].[IsDelete] AS [IsDelete],
[Extent1].[Account] AS [Account],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[CommonMobile] AS [CommonMobile],
[Extent1].[HeadPortrait] AS [HeadPortrait],
[Extent1].[AttFile] AS [AttFile],
[Extent1].[GroupId] AS [GroupId]FROM [dbo].[ContactInfo] AS [Extent1]WHERE [Extent1].[ID] 1;-- Query #2SELECT TOP (1)
[Extent1].[GroupId] AS [GroupId],
[Extent1].[GroupName] AS [GroupName]FROM [dbo].[GroupInfo] AS [Extent1]WHERE [Extent1].[GroupId] 2; 标注:EntityFramework.Extended相关部分参考了 顾振印的博文: http://www.cnblogs.com/GuZhenYin/p/5482288.html 8.0 AsNoTracking c#语句: var db new PhoneBookEntities(); var gi db.GroupInfo.FirstOrDefault(c c.GroupId 219);
MessageBox.Show(db.Entry(gi).State.ToString());//Unchangedvar giAnk db.GroupInfo.AsNoTracking().FirstOrDefault(c c.GroupId 219);
MessageBox.Show(db.Entry(giAnk).State.ToString());//Detached 相关文章 第一篇 Entity Framework Plus 之 Audit第二篇 Entity Framework Plus 之 Query Future第三篇 Entity Framework Plus 之 Query Cache第四篇 Entity Framework Plus 之 Batch OperationsEntity Framework教程(第二版)采用EntityFramework.Extended 对EF进行扩展(Entity Framework 延伸系列2)Dapper、Entity Framework 和混合应用 原文地址http://www.cnblogs.com/DKnight/p/5601680.html .NET社区新闻深度好文微信中搜索dotNET跨平台或扫描二维码关注 赞赏 人赞赏
