Sql – How to i reduce the number of db round-trips with this Linq2Sql

linq-to-sqlnet

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;

Best Answer

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
}