環境說明: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.html
a.在資料庫裡面分頁
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.Length>=2).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] > 2 ORDER 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 N'SELECT [Extent1].[GroupId] AS [GroupId], [Extent1].[GroupName] AS [GroupName] FROM [dbo].[GroupInfo] AS [Extent1] WHERE [Extent1].[GroupId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
如果是一條數據,用延遲載入是OK的.如果上面查詢有多條如10條結果,每條結果都使用到GroupInfo屬性,那麼一共會有11條sql請求.效率低.應該使用連表,一條sql搞定.寫法如下.
第一種寫法 Join:
那如果是多條數據,應使用預載入.
c#語句:
var db = new PhoneBookEntities(); var ci = db.ContactInfo.Where(c => c.ID >3).Join(db.GroupInfo,c=>c.GroupId,g=>g.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的第四個參數是 Func<ContactInfo,GroupInfo,anonymous type>.
GroupJoin的第四個參數是 Func<ContactInfo,IEnumerable<GroupInfo>,anonymous type>.
使用場景:聯繫人和他的好友.聯繫人一張表,好友關係一張表.聯繫人表和好友關係表做連接,查出多個聯繫人數據(包含他的好友),就應該使用GroupJoin.
簡單說:
public partial class ContactInfo
{
public int ID { get; set; }
public string ContactName { get; set; }
public Nullable<int> 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 Nullable<int> GroupId { get; set; }
public virtual List<GroupInfo> 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 }); 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 N'INSERT [dbo].[GroupInfo]([GroupName]) VALUES (@0) SELECT [GroupId] FROM [dbo].[GroupInfo] WHERE @@ROWCOUNT > 0 AND [GroupId] = scope_identity()',N'@0 nvarchar(300)',@0=N'Test'
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 N'INSERT [dbo].[GroupInfo]([GroupName]) VALUES (@0) SELECT [GroupId] FROM [dbo].[GroupInfo] WHERE @@ROWCOUNT > 0 AND [GroupId] = scope_identity()',N'@0 nvarchar(300)',@0=N'g1' exec sp_executesql N'INSERT [dbo].[GroupInfo]([GroupName]) VALUES (@0) SELECT [GroupId] FROM [dbo].[GroupInfo] WHERE @@ROWCOUNT > 0 AND [GroupId] = scope_identity()',N'@0 nvarchar(300)',@0=N'g2' ...
共執行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 N'DELETE [dbo].[GroupInfo] WHERE ([GroupId] = @0)',N'@0 int',@0=214
感覺繁瑣啦?解決方案,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 N'Test' = [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為"Test" gi.GroupName = "Test"; db.SaveChanges();
EF會自動優化,最終結果只有一個select語句,而沒有update語句. 此處細節,贊!
對比NHibernate 4,以下兩行代碼產生的sql語句會set Product表的[所有]欄位
var pl = session.Query<Product>().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 #1 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 [Extent1].[ID] > 1; -- Query #2 SELECT 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());//Unchanged var giAnk = db.GroupInfo.AsNoTracking().FirstOrDefault(c => c.GroupId == 219); MessageBox.Show(db.Entry(giAnk).State.ToString());//Detached