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

Question

Hi folks,

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

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.

Answer 2

I guess the problem is that you should call .ToList() for the whole query too. This will return the whole collection at one from the db.

In your case, the first SQL command returns only IDs of all questions and then, single SQL call occurs for every question (during iteration in a foreach loop) - see @x1 param.

Answer 3

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 4

You could try configuring Eager Loading on this object association. Something like:

        var dlo = new DataLoadOptions();
        // Configure eager loading
        dlo.LoadWith<Question>(q => q.QuestionTags);

        _context = new WhateverContext();
        _context.LoadOptions = dlo;

But you may need to refactor your code a little. Basically you are telling the framework to issue SQL to pull in the wider object graph, rather than wait until the object association is accessed (lazy loading is the default).

Maybe see (http://blog.codeville.net/2007/12/02/linq-to-sql-lazy-and-eager-loading-hiccups/). Different Steven by the way!

Answer 5

You can do lazy loading like this:

from p in db.Questions
let Tags = GetTags(Questions.Id)
select new Models.Question
{
    Title = p.Title,
    TagList = LazyList<string>(Tags)
}

public IQueryable<string> GetTags(int questionId) {
    from qt in db.QuestionTags
    join t in db.Tags on qt.TagId equals t.Id
    where qt.questionId = questionId
    select t.Name
}

LazyList is an IQueryable container that implements IList. As soon as the TagList property is enumerated the IQueryable stored inside will be executed.

The LazyList class was written by Rob Connery and can be found here: http://blog.wekeroad.com/blog/lazy-loading-with-the-lazylist/