如何加载多对多LINQ查询? [英] How to load Many to many LINQ query?

问题描述

我具有以下(相当标准的)表结构:

I have the following (pretty standard) table structure:

Post <-> PostTag <-> Tag

假设我有以下记录:

PostID Title
1,     'Foo'
2,     'Bar'
3,     'Baz'

TagID Name
1,    'Foo'
2,    'Bar'

PostID TagID
1      1
1      2
2      2

换句话说，第一篇文章有​​两个标签，第二篇文章有一个标签，而第三篇文章没有任何标签.

In other words, the first post has two tags, the second has one and the third one doesn't have any.

我想在一个查询中加载所有帖子及其标签，但无法找到合适的运算符组合.我已经能够加载仅带有标签的帖子或当多个标签时重复的帖子.

I'd like to load all posts and it's tags in one query but haven't been able to find the right combination of operators. I've been able to load either posts with tags only or repeated posts when more than one tag.

鉴于上面的数据库，我想在Post对象的collection属性中接收三个帖子及其标签(如果有).可能吗?

Given the database above, I'd like to receive three posts and their tags (if any) in a collection property of the Post objects. Is it possible at all?

谢谢

推荐答案

是的！有效.

如果有人遇到相同的问题，这就是我所做的:

If anyone is having the same problem here's what I did:

public IList<Post> GetPosts(int page, int record)
{
    var options = new DataLoadOptions();
    options.LoadWith<Post>(p => p.PostTags);
    options.LoadWith<PostTag>(pt => pt.Tag);
    using (var db = new DatabaseDataContext(m_connectionString))
    {
        var publishDateGmt = (from p in db.Posts
                              where p.Status != PostStatus.Hidden
                              orderby p.PublishDateGmt descending
                              select p.PublishDateGmt)
                              .Skip(page * record)
                              .Take(record)
                              .ToList()
                              .Last();
        db.LoadOptions = options;
        return (from p in db.Posts
                where p.Status != PostStatus.Closed 
                    && p.PublishDateGmt >= publishDateGmt
                orderby p.PublishDateGmt descending
                select p)
                .Skip(page * record)
                .ToList();
    }
}

这仅执行两个查询并为每个帖子加载所有标签.

This executes only two queries and loads all tags for each post.

想法是获取一些值以限制我们需要的最后一个帖子的查询(在这种情况下，PublishDateGmt列就足够了)，然后用该值而不是Take()来限制第二个查询.

The idea is to get some value to limit the query at the last post that we need (in this case the PublishDateGmt column will suffice) and then limit the second query with that value instead of Take().

感谢您对sirrocco的帮助.

Thanks for your help sirrocco.

这篇关于如何加载多对多LINQ查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！