none
Joins RRS feed

  • Question

  • Hi, I'm a bit new to Linq to SQL (this is my first project using it) and I thought I knew how to do joins, but I used this add-on (http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx) and saw that it was generating incorrect SQL.

    These are my tables (approximation, since I'm not allowed to use real names), common many to many, really:
    Post (pk ID), many to many with Tag (pk ID) through PostTag (pks PostID, TagID)

    So, I've built the following Linq query:
    IQueryable<Post> query = (from p in db.Posts
    from pt in p.PostTags
    where pt.Tag.Name = "tag name"
    select p);
    Thinking, hey, I don't actually have to tell L2S on which fields it has to build this join, since it already knows from the attributes all these classes have.

    Well, I'm doing so and it's generating a CROSS JOIN with PostTag instead of (inner) joining on Post.ID = PostTag.PostID.

    I've searched the web a bit and saw that there's this join keyword. Do I actually have to tell L2S on which fields it has to join (making the foreign key properties useless to query)?

    What would be the best way to write the above query, to get all posts that belong to a tag name?

    Thanks in advance.
    Wednesday, October 21, 2009 12:23 PM

Answers