我怎样才能降低分贝往返与此LINQ2SQL多少？ [英] How can i reduce the number of db round-trips with this Linq2Sql?

问题描述

我有以下LINQ2SQL和它做我的SELECT语句多个往返。我不知道为什么。首先，code，然后解释： -

 由对在db.Questions
选择新Models.Question
{
    标题= p.Title，
    标记列表=（从吨p.QuestionTags
               选择t.Tag.Name）.ToList（）
}
 

现在的数据库是

  

问题＆LT;酮以多对一> QuestionTags＆LT; -many以单>标签的

这样一个问题有一对多标签，在中间的链接表。这样一来，我可以重复使用的标签多次。 （我开到一个更好的模式，如果有一个）。

这样做确实由LINQ2SQL产生下面的SQL code

 选择[T0] [QuestionId] AS [ID]，等等...＆LT;  - 这就是很好的一个
 

  EXEC sp_executesql的N'SELECT [T1]。[名]
FROM [DBO]。[QuestionTags] AS [T0]
INNER JOIN [DBO]。[标签] AS [T1] ON [T1]。[TAGID] = [T0]。[TAGID]
WHERE [T0]。[QuestionId] = @ X1'，N'@ X1 INT'，@ X1 = 1
 

第二个SQL块列2倍..我想这是因为第一个SQL块返回两个结果，所以第二个被解雇每个结果从第一。

有没有什么办法可以让这一个SQL语句，而不是1 + n，其中n =结果的第一个查询的数量？

更新：

我都试过渴望和延迟加载，而且也没有区别。

  DataLoadOptions dataLoadOptions =新DataLoadOptions（）;
dataLoadOptions.LoadWith＆LT;问题＆GT;（X =＆GT; x.QuestionTags）;
dataLoadOptions.LoadWith＆LT; QuestionTag＆GT;（X =＆GT; x.Tag）;
db.LoadOptions = dataLoadOptions;
 

解决方案

在了ToList（）绝对是抱着你回来。你应该在整个查询做了ToList（）。

这是我认为你可以做的是用让另一件事。我认为在这种情况下，可以创建一个延迟的执行和被包括在离pression树，但因人而异。

 由对在db.Questions
让标签=（从吨p.QuestionTags
               选择t.Tag.Name）
选择新Models.Question
{
    标题= p.Title，
    标记列表=标签
}
 

I've got the following Linq2Sql and it's doing more than one round trip for my 'SELECT' statement. I'm not sure why. First the code, then the explanation:-

from p in db.Questions
select new Models.Question
{
    Title = p.Title,
    TagList = (from t in p.QuestionTags
               select t.Tag.Name).ToList()
}

Now the database is

Questions <-one to many-> QuestionTags <-many to one->Tag

so one question has one to many Tags, with a link table in the middle. This way, i can reuse tags multiple times. (I'm open to a better schema if there's one).

Doing this does the following Sql code generated by Linq2Sql

SELECT [t0].[QuestionId] AS [ID], etc....  <-- that's the good one

.

exec sp_executesql N'SELECT [t1].[Name]
FROM [dbo].[QuestionTags] AS [t0]
INNER JOIN [dbo].[Tags] AS [t1] ON [t1].[TagId] = [t0].[TagId]
WHERE [t0].[QuestionId] = @x1',N'@x1 int',@x1=1

The second sql block is listed 2x .. i think that's because the first sql block returns TWO results, so the second one is fired for each result from the first.

Is there any way i can make this one sql statement instead of 1 + n, where n = the number of results from the first query?

Update:

I've tried both Eager and Lazy loading and there's no difference.

DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<Question>(x => x.QuestionTags);
dataLoadOptions.LoadWith<QuestionTag>(x => x.Tag);
db.LoadOptions = dataLoadOptions;

解决方案

The ToList() is definitely holding you back. You should do a ToList() on the whole query.

Another thing that I think you can do is use "let". I think in this case, it can create a delayed execution and be included in the expression tree, but YMMV.

from p in db.Questions
let Tags = (from t in p.QuestionTags
               select t.Tag.Name)
select new Models.Question
{
    Title = p.Title,
    TagList = Tags
}

这篇关于我怎样才能降低分贝往返与此LINQ2SQL多少？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！