ASP.NET Web Api join two tables to make an array of objects

Question

This might be already asked question but I still can't solve my problem. I don't even know if I am on the right path. Some help will be appreciated.

I have two model classes in an ASP.NET Web API project looking like this:

namespace Artists.Models
{
    public class Artist
    {
        public int ArtistID { get; set; }
        public string ArtistName { get; set; }
    }
}

and :

namespace Artists.Models
{
    public class Project
    {
        public int ProjectID { get; set; }
        public string ProjectName { get; set; }
        public int ArtistID { get; set; }
    }
}

That created two SQL tables connected by ArtistID as a foreign key in Project.

I have this controller code:

public IQueryable<Object> GetArtists()
{
     return from a in db.Artists
            join p in db.Projects on a.ArtistID equals p.ArtistID
            select new
            {
                account_name = a.ArtistName,
                project_name = p.ProjectName
            };
}

that returns this output in Postman:

[
    {
        "name": "Jack",
        "project_name": "ProjectOne"
    },
    {
        "name": "Mike",
        "project_name": "ProjectTwo"
    },
    {
        "name": "Mike",
        "project_name": "ProjectThree"
    },
    {
        "name": "John",
        "project_name": "ProjectFour"
    },
    {
        "name": "John",
        "project_name": "ProjectFive"
    }
]

but I want the output to be like this:

[
    {
        "name": "Jack",
        "projects": ["ProjectOne"]
    },
    {
        "name": "Mike",
        "projects": ["ProjectTwo", "ProjectThree"]
    },
    {
        "name": "John",
        "projects": ["ProjectFour", "ProjectFive"]
    },
]

Am I even close? I know this might an asked question but I really can't figure it out.

Answer 1

Query syntax

If you want to keep using query syntax you can join into collection and then project its results to only return ProjectName from artistProjects, ie:

public IQueryable<Object> GetArtists()
{

    return from a in db.Artists
            join p in db.Projects on a.ArtistID equals p.ArtistID into artistProjects
            select new
            {
                name = a.ArtistName,
                projects = artistProjects.Select(ap => ap.ProjectName)
            };

}

---

Method syntax

If you want to go for method syntax then read below. You could add navigation property to Artist, ie:

namespace Artists.Models
{
    public class Artist
    {
        public int ArtistID { get; set; }
        public string ArtistName { get; set; }
        public ICollection<Project> Projects { get; set; }
    }
}

Then modify your query to eager load (join) Projects for Artist:

public IQueryable<Object> GetArtists()
{
    return db.Artists
            .Include(a => a.Projects)
            .Select(a => 
                new
                {
                    name = a.ArtistName,
                    projects = a.Projects.Select(p => p.ProjectName)
                });
}