Linq query, select inner query into list?

Question

Hello!

I am writing a linq query to select a blogpost,

    from data in dataContext.Blog_BlogPosts
        join tagsData in dataContext.Blog_TagToPostConnections on data.BlogPostID equals tagsData.BlogPostID
        where data.Slug == "asp-programmering"
        select new BlogPost
        {
            Title = data.Title,
            Slug = data.Slug,
            Author = data.Author,
            Text = data.Text,
            Tags = (from tags in dataContext.Blog_Tags where tags.TagID == tagsData.TagID select new Tag { TagID = tags.TagID, TagName = tags.Tag }).ToList()
        };

The blogpost class looks like this (used to test it)

    public class BlogPost
    {
        public string Slug { get; set; }
        public string Title { get; set; }
        public string Text { get; set; }
        public string Author { get; set; }
        public DateTime Published { get; set; }
        public List<Tag> Tags { get; set; }
    }

The blogpost i am testing got 3 tags attached to it. The table structure is:

(table)BlogPost -> (table)BlogPostTags <- (table)Tags

So the BlogPostTags table only contains 2 fields, BlogPostID and TagID.

When i run the query above i get 3 results back. Same blogpost 3 times but with 1 tag in each. It should return 1 post with 3 tags. The problem lies in the Tags query above.

Answer 1

It's because you are joining on Blog_TagToPostConnections in the outer expression. Since there are three matching tags, the join will produce three result items. Move the join into the expression being assigned to Tags (that's where you want three things to pop out).

from data in dataContext.Blog_BlogPosts
    where data.Slug == "asp-programmering"
    select new BlogPost
    {
        Title = data.Title,
        Slug = data.Slug,
        Author = data.Author,
        Text = data.Text,
        Tags = (from tags in dataContext.Blog_Tags
                join tagsData in dataContext.Blog_TagToPostConnections
                     on tags.TagID equals tagsData.TagID
                where data.BlogPostID == tagsData.BlogPostID
                select new Tag { TagID = tags.TagID, TagName = tags.Tag }
               ).ToList()
    };