LINQ to SQL - DataLoadOptions
[ 2007-08-25 21:26:20 | 作者: yuhen ]
我们先看下面这个 One-to-Many 显示的例子。
输出(显示结果被调整过，便于查看)
从输出结果中，我们发现遍历 group.Users 时会导致多次获取 User 的 SQL 执行，很显然这种方式存在性能问题，如何优化呢？用存储过程？还是……
输出
DataContext 通过 DataLoadOptions.LoadWith<T>() 来 "提示" LINQ to SQL "一次性" 载入所需的全部数据。
创建 DataLoadOptions 对象时有点小细节需要注意，如果我们按下面这种方式写的话，会导致异常。
我们还可以使用 DataLoadOptions.AssociateWith<T>() 对需要载入的 User 做出条件限制。
输出
要是反过来，Many-to-One 会如何？
输出
![[lol]](styles/default/images/smilies/icon_lol.gif)
同样有效。OK~~~ 继续看看更复杂点的测试。
输出
![[cry]](styles/default/images/smilies/icon_cry.gif)
没效果~~ 反过来呢？
输出
这次倒是有效，就是不知道这么折腾，"性能" 真的如我们预期的那样提高吗？![[confused]](styles/default/images/smilies/icon_confused.gif)
评论Feed: http://www.rainsts.net/feed.asp?q=comment&id=546
using (DataClasses1DataContext context = new DataClasses1DataContext())
{
context.Log = Console.Out;
var groups = from g in context.Groups select g;
foreach (var group in groups)
{
Console.WriteLine("Group:{0}", group.Name);
foreach (var user in group.Users)
{
Console.WriteLine("\tUser:{0}", user.Name);
}
Console.WriteLine("--------------");
}
}输出(显示结果被调整过，便于查看)
SELECT [t0].[Id], [t0].[Name]
FROM [dbo].[Group] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[Id], [t0].[Name], [t0].[Age], [t0].[GroupId]
FROM [dbo].[User] AS [t0]
WHERE [t0].[GroupId] = @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [7]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Group:group1
User:user1
User:user2
--------------
SELECT [t0].[Id], [t0].[Name], [t0].[Age], [t0].[GroupId]
FROM [dbo].[User] AS [t0]
WHERE [t0].[GroupId] = @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [8]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Group:group2
User:user3
User:user4
--------------从输出结果中，我们发现遍历 group.Users 时会导致多次获取 User 的 SQL 执行，很显然这种方式存在性能问题，如何优化呢？用存储过程？还是……
using (DataClasses1DataContext context = new DataClasses1DataContext())
{
context.Log = Console.Out;
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Group>(g => g.Users);
context.LoadOptions = options;
var groups = from g in context.Groups select g;
foreach (var group in groups)
{
Console.WriteLine("Group:{0}", group.Name);
foreach (var user in group.Users)
{
Console.WriteLine("\tUser:{0}", user.Name);
}
Console.WriteLine("--------------");
}
}输出
SELECT [t0].[Id], [t0].[Name], [t1].[Id] AS [Id2], [t1].[Name] AS [Name2], [t1].[Age], [t1].[GroupId], (
SELECT COUNT(*)
FROM [dbo].[User] AS [t2]
WHERE [t2].[GroupId] = [t0].[Id]
) AS [count]
FROM [dbo].[Group] AS [t0]
LEFT OUTER JOIN [dbo].[User] AS [t1] ON [t1].[GroupId] = [t0].[Id]
ORDER BY [t0].[Id], [t1].[Id]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Group:group1
User:user1
User:user2
--------------
Group:group2
User:user3
User:user4
--------------DataContext 通过 DataLoadOptions.LoadWith<T>() 来 "提示" LINQ to SQL "一次性" 载入所需的全部数据。
创建 DataLoadOptions 对象时有点小细节需要注意，如果我们按下面这种方式写的话，会导致异常。
context.LoadOptions = new DataLoadOptions(); context.LoadOptions.LoadWith<Group>(g => g.Users);
我们还可以使用 DataLoadOptions.AssociateWith<T>() 对需要载入的 User 做出条件限制。
using (DataClasses1DataContext context = new DataClasses1DataContext())
{
context.Log = Console.Out;
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Group>(g => g.Users);
options.AssociateWith<Group>(g => from u in g.Users where u.Age < 14 select u);
context.LoadOptions = options;
var groups = from g in context.Groups select g;
foreach (var group in groups)
{
Console.WriteLine("Group:{0}", group.Name);
foreach (var user in group.Users)
{
Console.WriteLine("\tUser:{0}, Age:{1}", user.Name, user.Age);
}
Console.WriteLine("--------------");
}
}输出
SELECT [t0].[Id], [t0].[Name], [t1].[Id] AS [Id2], [t1].[Name] AS [Name2], [t1].[Age], [t1].[GroupId], (
SELECT COUNT(*)
FROM [dbo].[User] AS [t2]
WHERE ([t2].[Age] < @p0) AND ([t2].[GroupId] = [t0].[Id])
) AS [count]
FROM [dbo].[Group] AS [t0]
LEFT OUTER JOIN [dbo].[User] AS [t1] ON ([t1].[Age] < @p0) AND ([t1].[GroupId] = [t0].[Id])
ORDER BY [t0].[Id], [t1].[Id]
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [14]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Group:group1
User:user1, Age:13
--------------
Group:group2
User:user3, Age:13
--------------要是反过来，Many-to-One 会如何？
using (DataClasses1DataContext context = new DataClasses1DataContext())
{
context.Log = Console.Out;
var options = new DataLoadOptions();
options.LoadWith<User>(u => u.Group);
context.LoadOptions = options;
var user = (from u in context.Users select u).FirstOrDefault();
Console.WriteLine(user.Name);
Console.WriteLine(user.Group.Name);
}输出
SELECT TOP 1 [t0].[Id], [t0].[Name], [t0].[Age], [t0].[GroupId], [t2].[test], [t2].[Id] AS [Id2],
[t2].[Name] AS [Name2]
FROM [dbo].[User] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[Id], [t1].[Name]
FROM [dbo].[Group] AS [t1]
) AS [t2] ON [t2].[Id] = [t0].[GroupId]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
user1
group1 同样有效。OK~~~ 继续看看更复杂点的测试。using (DataClasses1DataContext context = new DataClasses1DataContext())
{
context.Log = Console.Out;
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Group>(g => g.Users);
options.LoadWith<User>(u => u.Articles);
context.LoadOptions = options;
var groups = from g in context.Groups select g;
foreach (var group in groups)
{
Console.WriteLine("Group:{0}", group.Name);
foreach (var user in group.Users)
{
Console.WriteLine("\tUser:{0}", user.Name);
foreach (var article in user.Articles)
{
Console.WriteLine("\t\tArticle:{0}", article.Title);
}
}
Console.WriteLine("--------------");
}
}输出
SELECT [t0].[Id], [t0].[Name]
FROM [dbo].[Group] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[Id], [t0].[Name], [t0].[Age], [t0].[GroupId], [t1].[Id] AS [Id2],
[t1].[Title], [t1].[Content], [t1].[UserId], (
SELECT COUNT(*)
FROM [dbo].[Article] AS [t2]
WHERE [t2].[UserId] = [t0].[Id]
) AS [count]
FROM [dbo].[User] AS [t0]
LEFT OUTER JOIN [dbo].[Article] AS [t1] ON [t1].[UserId] = [t0].[Id]
WHERE [t0].[GroupId] = @x1
ORDER BY [t0].[Id], [t1].[Id]
-- @x1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [7]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Group:group1
User:user1
Article:title1
User:user2
Article:title2
--------------
SELECT [t0].[Id], [t0].[Name], [t0].[Age], [t0].[GroupId], [t1].[Id] AS [Id2], [t1].[Title],
[t1].[Content], [t1].[UserId], (
SELECT COUNT(*)
FROM [dbo].[Article] AS [t2]
WHERE [t2].[UserId] = [t0].[Id]
) AS [count]
FROM [dbo].[User] AS [t0]
LEFT OUTER JOIN [dbo].[Article] AS [t1] ON [t1].[UserId] = [t0].[Id]
WHERE [t0].[GroupId] = @x1
ORDER BY [t0].[Id], [t1].[Id]
-- @x1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [8]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Group:group2
User:user3
Article:title3
User:user4
Article:title4
-------------- 没效果~~ 反过来呢？using (DataClasses1DataContext context = new DataClasses1DataContext())
{
context.Log = Console.Out;
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Article>(a => a.User);
options.LoadWith<User>(u => u.Group);
context.LoadOptions = options;
var article = (from a in context.Articles select a).FirstOrDefault();
Console.WriteLine(article.Title);
Console.WriteLine(article.User.Name);
Console.WriteLine(article.User.Group.Name );
}输出
SELECT TOP 1 [t0].[Id], [t0].[Title], [t0].[Content], [t0].[UserId], [t4].[test], [t4].[Id] AS [Id2],
[t4].[Name], [t4].[Age], [t4].[GroupId], [t4].[test2], [t4].[Id2] AS [Id3], [t4].[Name2]
FROM [dbo].[Article] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[Id], [t1].[Name], [t1].[Age], [t1].[GroupId], [t3].[test] AS [test2],
[t3].[Id] AS [Id2], [t3].[Name] AS [Name2]
FROM [dbo].[User] AS [t1]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t2].[Id], [t2].[Name]
FROM [dbo].[Group] AS [t2]
) AS [t3] ON [t3].[Id] = [t1].[GroupId]
) AS [t4] ON [t4].[Id] = [t0].[UserId]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
title1
user1
group1这次倒是有效，就是不知道这么折腾，"性能" 真的如我们预期的那样提高吗？
[最后修改由 yuhen， 于 2007-08-26 11:26:13]
评论Feed: http://www.rainsts.net/feed.asp?q=comment&id=546
这篇日志没有评论。
