LINQ: Using AssociateWith and Distinct together

Question

I haven't found a way to use these two elements together.

Here is my issue:

A web page has many bookmarks. A user owns a bookmark. A user can bookmark the same page multiple times (for my purposes).

What I want is a list of the distinct users who have bookmarked a page. I'm trying to use the LoadWith() and AssociateWith() methods of the DataContext, because I don't want to have to write a completely new query everytime I want a slight variant of the data. So, sometimes I want a page, sometimes I want a page and a list of bookmarks, etc.

I can have code like this that gets me the page and it's bookmarks:

dlo.LoadWith<Page>(p => p.Bookmarks);

And add this to get me users:

dlo.LoadWith<Bookmark>(b => b.User);

But I don't know how I can constrain the users to be distinct. I assume it's via an AssociateWith() command, but that doesn't support the Distinct() operator: http://msdn.microsoft.com/en-us/library/bb548919.aspx

Is what I'm doing possible, or am I going to have to write a new query for it?

Thanks! Tom

Answer 1

Suppose that there are two pages and two users and that both users have bookmarked both pages.

If you issue

dlo.LoadWith<Page>(p => p.Bookmarks);
dlo.LoadWith<Bookmark>(b => b.User);
dc.DataLoadOptions = dlo;
List<Page> myPages = dc.Page.ToList();

Then LinqToSql will load 2 pages, 4 bookmarks and 2 users and construct the object graph you'd expect.

Then, to get distinct users without hitting the database, you have to traverse the graph and do something like this:

List<User> myUsers = myPages
  .SelectMany(p => p.Bookmarks)
  .Select(b => b.User)
  .Distinct()
  .ToList()

---

a user can bookmark the same page multiple times in this case, so they're not distinct

Ok, here's an untested shot at it.

List<PageWithUsers> myPages = dc.Pages
  .Select(p => new PageWithUsers()
  {
    Page = p
    Users = p.Bookmarks
      .Select(b => b.UserId)
      .Distinct()
      .Take(10)
      .Select(i => dc.Users.Where(u => u.UserId = i))
  })
  .ToList();