LINQ：一起使用AssociateWith和Distinct

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.

我想要的是一个为页面添加书签的不同用户列表。我正在尝试使用DataContext的LoadWith()和AssociateWith()方法,因为我不希望每次我想要一个轻微的数据变体时都要编写一个全新的查询。所以,有时我想要一个页面,有时我想要一个页面和一个书签列表等。

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

但我不知道如何限制用户的区别。我假设它是通过AssociateWith()命令,但不支持Distinct()运算符: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?

我正在做什么,或者我将不得不为它写一个新的查询?

1 个解决方案

#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.

然后LinqToSql将加载2个页面,4个书签和2个用户,并构建您期望的对象图。

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();

#1