Sunday, March 6, 2011

Linq2Sql Many:Many question, How would you do this?

I know many:many isn't supported in Linq2Sql but I am working on a workaround

I am working with my little SO clone and I have a table with Questions and a table with Tags and a linking table QuestionTag so I have a classic many:many relationship between Questions and Tags.

To display the list of Questions on the front page I have this class I want to fill up from a Linq2Sql query

public class ListQuestion
{
   public int QuestionID { get; set; }
   public string Title{ get; set; }
   public IEnumerable<Tag> Tags { get; set; }
}


public IEnumerable<ListQuestion> GetQuestions()
{
   from q in Questions
   .................
   select new ListQuestion{ ... }
}

The problem is how should I fill up the Tag collection. I found out this isn't possible to do in 1 single query so I have divided this into 2 queries, 1 to get the questions and 1 to get the tags and later try to join them. I know many:many is supported in Entity framework so how do they do it? How would you do this? Any alternative approach? The query should of course be efficient.

From stackoverflow
  • This may work for your case;

    from q in Questions
    select new ListQuestion 
    { 
      Tags = q.QuestionTags.Select(qt => qt.Tag),
      QuestionId = q.ID,
      Title = q.Title
    }
    
    TT : It works. Thx a alot. I can't see how the actual query looks like. I would like to avoid that there is fired 1 query for every question to get the tags.
    Pure.Krome : Does this do TWO round trips to the DB? I've got the same exact question here: http://stackoverflow.com/questions/371279/how-can-i-reduce-the-number-of-db-round-trips-with-this-linq2sql
    yapiskan : No, there will be only one round trip to the DB.

0 comments:

Post a Comment