How can I reduce the number of db round-trip with this Linq2Sql?

Question

I have the following Linq2Sql and it does more than one round for my SELECT statement. I do not know why. Code first, then explanation: -

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

Database now

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

therefore, one question has one, many tags, and the link table is in the middle. This way I can reuse tags several times. (I am open to a better scheme, if any).

To do this, the following Sql code generated by Linq2Sql is executed

 SELECT [t0].[QuestionId] AS [ID], etc.... <-- that 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 specified 2x .. I think because the first sql block returns TWO results, so the second one is launched for each result from the first.

Is there a way to make this one SQL query instead of 1 + n, where n = the number of results of the first query?

Update:

I have tried both loading and lazy loading, and there is no difference.

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

Answer 1

ToList () definitely holds you back. You must do ToList () for the whole request.

Another thing that I think you can do is use let. I think in this case it can create deferred 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 may be one of the cases where LINQ alone is not enough. Did you consider this logic as UDF or SPROC instead, and just use LINQ to call it? LINQ-to-SQL is very good at invocation (Entity Framework is not so good in UDF).

You can then merge the tags in the database and, for example, return it as varchar. There is a TSQL trick for this without a cursor:

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

(possibly removing the final comma)

After running @foo will be CSV values ​​- very effective if you return a single line. Not so good if you return a few main lines.

Answer 3

You can try setting up Eager Loading for this association of objects. 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 reorganize your code a bit. Basically you are telling the framework to release SQL, to pull out a wider graph of objects, rather than waiting until an association of objects is available (lazy loading by default).

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

Answer 4

I think the problem is that you should call .ToList () for the whole request. This will return the entire collection to one of db.

In your case, the first SQL command returns only the identifiers of all questions, and then for each question there is one SQL call (during iteration in the foreach loop) - see the @ x1 parameter.

Answer 5

You can do lazy loading as follows:

 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. Once the TagList property is listed, the IQueryable will be saved, which will be stored internally.

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