Join 3 One to Many Tables in Entity Framework

Question

i have 2 tables that each one has a one-to-many relation to the table between and the table between has ids of 2 other tables

dbo.Posts          dbo.Posts_Categories          dbo.Categories
   -ID                -ID                           -ID
   -Title             -PostID                       -Name
                      -CategoryID

result i expect is :

Title = post1          Categories = web,mobile,desktop
Title = post2          Categories = app,game
...

i know how to query this in sql using Stuff function and For Xml Path but i have no idea how do i do this in entity framework!

any suggestion or book for how to do works in this way might help!

Edit: EF classes added:

    public class Post : ReportingBase {
        public Post() { }

        [Required, MaxLength(500)]
        public string Title { get; set; }
        [Required, MaxLength(500)]
        public string Address { get; set; }
        [Required]
        public string Body { get; set; }
        [Required, MaxLength(500)]
        public string Tags { get; set; }
        [Required]
        public int Visit { get; set; }



        public virtual ICollection<Post_Category> Posts_Categories { get; set; }
        public virtual ICollection<Post_AttachedFile> Posts_AttachedFiles { get; set; }

        [ForeignKey("Image")]
        public virtual int? ImageID { get; set; }
        public virtual Image Image { get; set; }
    }

    public class Post_Category {
        public Post_Category() { }

        [Key, Column(Order = 0)]
        public int PostID { get; set; }

        [Key, Column(Order = 1)]
        public int CategoryID { get; set; }

        public virtual Post Post { get; set; }
        public virtual Category Category { get; set; }
    }

    public class Category : EntityBase {
        public Category() { }

        [Required, MaxLength(50)]
        public string Name { get; set; }

        [Required, MaxLength(150)]
        public string Address { get; set; }
        public int? ParentID { get; set; }



        public virtual ICollection<Post_Category> Posts_Categories { get; set; }
    }

thank you in advance

Edit : According to @IvanStoev answer i did following :

    List<P> p = context.Posts.Select(post => new {
        Title = post.Title,
        Categories = post.Posts_Categories.Select(pc => pc.Category.Name).ToList()
    }).ToList();

and created a class called P :

public class P {
    public string Title { get; set; }
    public List<string> Categories { get; set; }
}

but it doesn't work correctly and the problem is how to return the result.

Answer 1

You should be using .Include() for any join in EF Core.

I've come up with this simple example: one person can have many dogs.

public class Person
{
    public Guid Id { get; set; }
    public ICollection<Dog> Dogs { get; set; } // One Person can have many Dogs
}

public class Dogs
{
    public Guid Id { get; set; }
    public Guid PersonId { get; set; }
}

Generate the migrations after creating the models. Not going over how to do that in this answer.

Here's how you use .Include() to join upon the two different tables:

public class PersonRepository : RepositoryBase
{
    public IEnumerable<Person> FetchPeopleWithManyDogs()
    {
        return DatabaseContext.Person
            .Include(x => x.Dogs)
            .Where(x => x.Dogs.Count() > 1).ToList();
    }
}

Answer 2

You can use Linqpad (software) to get familiarize with the Linq query it builds lambda expression for you by connecting to the database and provides output too to cross verify.

The below one is the lambda expression for joining the tables you have mentioned.

p - Post  
pc - post_categories
c - categories

Code:

Posts.Join(Post_Categories, p => p.ID, pc => pc.ID, ( p, pc) => new { p = p, pc = pc})
     .Join(Categories, pcc => pcc.pc.CategoryID, c => c.ID, ( pcc, c) => new { pcc = pcc, c = c})
     .Select(p.Title)
     .Select(c.Name)

Answer 3

In EF it's even easier than in SQL thanks to the concept of so called navigation properties. All you need to know is a basic LINQ query syntax and just follow them (navigate) to get the data needed. For instance:

var result = db.Posts
    .Select(post => new
    {
        Title = post.Title,
        Categories = post.Posts_Categories
            .Select(pc => pc.Category.Name)
            .ToList()
    })
    .ToList();

The result is a list of anonymous type having string Title property and List<string> Categories property containing the related category names.