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.

mwilczynski · Accepted Answer · 2018-06-23 20:51:33Z

7

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

edited Jun 23, 2018 at 20:51

answered Jun 23, 2018 at 20:41

mwilczynski

3,1301 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Happy Coconut Over a year ago

Dude I can't thank you enough. This has helped me a LOT. I just learn from you for the ICollection. Thank you again Sir and have a nice day.

mwilczynski Over a year ago

Good luck with your journey in .NET. :)

Happy Coconut Over a year ago

Using the "Method syntax", when i don't use the line .Include(a => a.Projects), still everything works fine. Can i delete it or i need it for other reasons not known to me. Just out of curiosity so i will know i the future.

mwilczynski Over a year ago

It means you have lazy loading enabled and it gets joined automatically when you request it. So yes, you might not need it.

Collectives™ on Stack Overflow

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

1 Answer 1

Query syntax

Method syntax

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Query syntax

Method syntax

4 Comments

Your Answer

Sign up or log in

Post as a guest