none
Distinct doesn't work with LoadWith? RRS feed

  • Question

  • I need to query 2 tables, which are related as many to many (with a third table). Since I'm going to use a lot of data from both end tables, I'm using loadwith option:

    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Post>(t => t.PostTags);
    options.LoadWith<PostTag>(t => t.Tag);
    
    MyDataContext db = new MyDataContext();
    db.LoadOptions = options;
    
    IQueryable<Post> query = (from p in db.Posts
                    join pt in db.PostTags on p.ID equals pt.PostID
                    where pt.Tag.Name == "tag name"
                    select p).Distinct();
    I've added a Distinct() call because the result is duplicated rows for posts that have more than 1 tag, but it made no difference.

    If I comment out the LoadWith lines, it doesn't duplicate any line (obviously).

    What caught my attention is that when I checked the generated SQL, it had no DISTINCT keyword. If I copy and paste this query and simply add the "distinct", it works as it should.

    Any ideas on how to work around this?

    The only reason I can't just distinct them "manually" in the code (aside from performance reasons) is that I need those results paged, and currently, I'm using query.Skip(10).Take(10), and so, this query needs to be correct, so that the correct number of rows is skipped and taken.

    Thanks in advance.
    Wednesday, October 21, 2009 3:36 PM

Answers

  • Hi Rachel,

    May be its because you are explicity joining posts with posttags in addition to requesting eager load for posttags.
    What if you try this:
    var query = 
     from p in db.Posts
     where p.PostsTags.Any(t => t.Tag.Name == "tag name")
     select p;


    Hope that helps.

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Yichun_Feng Wednesday, October 28, 2009 2:23 AM
    Thursday, October 22, 2009 6:30 AM