［LINQ2SQL］DeferredLoadingEnabled=false ?!
Model: Parent.Id –>Child.Parent
Using dbml
Database: SQL Server 2008 express (mdb)
初始值
1: private void Initial()
2: {
3: using (DataClasses1DataContext ctx = new DataClasses1DataContext())
4: {
5: var c = from _c in ctx.Child select _c;
6: ctx.Child.DeleteAllOnSubmit(c);
7: var p = from _p in ctx.Parent select _p;
8: ctx.Parent.DeleteAllOnSubmit(p);
9: ctx.SubmitChanges();
10:
11: ctx.Parent.InsertOnSubmit(new Parent() { Id = 1, Name = "Lee" });
12: ctx.Parent.InsertOnSubmit(new Parent() { Id = 2, Name = "Wang" });
13: ctx.Child.InsertOnSubmit(new Child() { Id = 1, Name = "Hector", Parent = 1 });
14: ctx.Child.InsertOnSubmit(new Child() { Id = 2, Name = "Han", Parent = 1 });
15: ctx.SubmitChanges();
16: }
17: }
原先code, Parent p.Child 是有內容的, 筆數=2
1: using (DataClasses1DataContext ctx = new DataClasses1DataContext())
2: {
3: ctx.Log = Console.Out;
4: var v = from _v in ctx.Parent
5: where _v.Id == 1
6: select _v;
7: foreach (Parent p in v)
8: {
9: Assert.AreEqual(2, p.Child.Count);
10: }
11: }
但它Generate的sql, 卻有著效能的issue, 因為它會多次送出SQL 查詢Child.
1: SELECT [t0].[Id], [t0].[Name]
2: FROM [dbo].[Parent] AS [t0]
3: WHERE [t0].[Id] = @p0
4: -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
5: -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.5420
6:
7: SELECT [t0].[Id], [t0].[Name], [t0].[Parent]
8: FROM [dbo].[Child] AS [t0]
9: WHERE [t0].[Parent] = @p0
10: -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
11: -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.5420
故我參考MSDN: http://msdn.microsoft.com/zh-tw/library/bb399393(v=VS.90).aspx
將 DataContext.DeferredLoadingEnabled=false, 結果
Assert.AreEqual 失敗。預期: <2>。實際: <0>。
疑?! 它不會主動將關聯物件引入...
後來又參考MSDN: http://msdn.microsoft.com/zh-tw/library/Bb386920(v=VS.90).aspx
這才是正解. 因為我一直鬼打牆在設定屬性就應該可以, 所以花了很多時間在Try&Error,
最終才確認DataLoadOptions.LoadWith是必然解 ...
1: using (DataClasses1DataContext ctx = new DataClasses1DataContext())
2: {
3: DataLoadOptions lo = new DataLoadOptions();
4: lo.LoadWith<Parent>(p => p.Child);
5: ctx.LoadOptions = lo;
6:
7: ctx.Log = Console.Out;
8: ctx.DeferredLoadingEnabled = false;
9:
10: var v = from _v in ctx.Parent
11: where _v.Id == 1
12: select _v;
13: Assert.AreEqual(1, v.Count());
14:
15: foreach (Parent p in v)
16: {
17: Assert.AreEqual(2, p.Child.Count);
18: }
19: }
果然只有一組SQL, 雖然跟我預期的INNER JOIN有差距 ...
1: SELECT [t0].[Id], [t0].[Name], [t1].[Id] AS [Id2], [t1].[Name] AS [Name2], [t1].[Parent], (
2: SELECT COUNT(*)
3: FROM [dbo].[Child] AS [t2]
4: WHERE [t2].[Parent] = [t0].[Id]
5: ) AS [value]
6: FROM [dbo].[Parent] AS [t0]
7: LEFT OUTER JOIN [dbo].[Child] AS [t1] ON [t1].[Parent] = [t0].[Id]
8: WHERE [t0].[Id] = @p0
9: ORDER BY [t0].[Id], [t1].[Id]
10: -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
11: -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.5420
若你用SQL 執行會出現2筆RECORD, 但Parent 並沒有因此而變2筆, 而是Grouping起來, 只有1筆, 而p.Child有2筆.
------------------------------------
BTW, 如果要INNSER JOIN , 語法改成
1: var v = from _v in ctx.Parent
2: from _c in _v.Child
3: where _v.Id == 1
4: select new { p=_v, c=_c };
結果合乎預期
1: SELECT [t0].[Id], [t0].[Name], [t1].[Id] AS [Id2], [t1].[Name] AS [Name2], [t1].[Parent]
2: FROM [dbo].[Parent] AS [t0], [dbo].[Child] AS [t1]
3: WHERE ([t0].[Id] = @p0) AND ([t1].[Parent] = [t0].[Id])
4: -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
5: -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.5420
但集合物件的結果就不同了, 變成每一個集合都有Parent物件, 相當於SQL的結果.
我順便測了一下, 雖然Parent有重覆出現, 但其實都是同一個物件.
1: int count = 0;
2: Parent p1 = null;
3: Parent p2 = null;
4: foreach (var p in v)
5: {
6: if (count == 0)
7: {
8: p1 = p.p;
9: }
10: else
11: {
12: p2 = p.p;
13: }
14:
15: count++;
16: }
17: Assert.AreEqual(p1, p2);
18: Assert.AreSame(p1, p2);
不過Parent.Child 還是Empty...
結語: 後來才看到這篇 http://dotnetslackers.com/articles/csharp/Load-Lazy-in-LINQ-to-SQL.aspx
