How can i reduce the number of db round-trips with this Linq2Sql?

Question

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;

Answer 1

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
}

Answer 2

This might be one of the cases where LINQ by itself isn't enough. Have you considered writing this logic as a UDF or an SPROC instead, and simply using LINQ to call it? LINQ-to-SQL is very good at calling either (Entity Framework isn't so great with UDFs).

Then you could do the tag combining at the database, and return it as a varchar, for example. There is a TSQL trick for doing this without a cursor:

DECLARE @foo varchar(max)
SET @foo = ''
SELECT @foo = @foo + [SomeColumn] + ',' -- CSV
FROM [SomeTable]
WHERE -- some condition

(perhaps removing the trailing comma)

After running this, @foo will be the CSV of the values - very effective if you are returning a single row. Not so great if you are returning multiple main rows.