ASP.NET Web Api连接两个表以创建对象数组 [英] ASP.NET Web Api join two tables to make an array of objects

问题描述

这可能已经是一个问题了，但我仍然无法解决我的问题.我什至不知道我走的路是否正确.一些帮助将不胜感激.

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.

在ASP.NET Web API项目中，我有两个模型类，如下所示:

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

和:

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

这创建了两个由ArtistID连接的SQL表，作为Project中的外键.

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

我有以下控制器代码:

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.

推荐答案

查询语法

如果您想继续使用查询语法，则可以加入集合，然后将其结果投影为仅从artistProjects返回ProjectName，即:

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

}

---

方法语法

如果您想使用方法语法，请阅读以下内容. 您可以将导航属性添加到Artist，即:

---

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

然后修改您的查询，以便为Artist加载(加入)Projects:

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

这篇关于ASP.NET Web Api连接两个表以创建对象数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！