英文:

C# Web API Merge Elements Into Single Element (with Objects)

问题

我正在构建一个C# Web API，其目的是允许外部公司通过调用API来检索学生数据（他们称之为“roles”）。现在，他们可以选择通过调用https://localhost:XXXXX/custom-roles-api/campusCustomRoles来检索所有学生，或者通过调用https://localhost:XXXXX/custom-roles-api/campusCustomRoles/123456来检索单个学生，其中“123456”是学生ID编号。有些学生只有一行数据，因为他们属于一个部门，因此在GET调用中的JSON返回中以单个元素显示他们，但是对于属于2个或多个部门的学生，返回会显示多个元素，例如：

{
    "roles": [
        [
            "STUDENT",
            "UG",
            2,
            "Politics",
            "Smith, John",
            123456
        ],
        [
            "STUDENT",
            "UG",
            2,
            "Religions",
            "Smith, John",
            123456
        ]
    ]
}

在上面的示例中，学生John Smith属于2个部门（Religions和Politics），在JSON返回中以两个元素表示。外部公司请求的是一个单一元素，它会像这样：

{
    "roles": [
        [
            "STUDENT",
            "UG",
            2,
            "Politics",
            "Religions",
            "Smith, John",
            123456
        ]
    ]
}

我急于补充说，其中一个要求是仅返回对象值而不包括键（或列名）。

以下是我的Roles类的外观：

public class Roles
{
    List<Roles> studentRoles = new List<Roles>();

    public int UserName { get; set; }
    public string PersonName { get; set; }
    public string Profile { get; set; }
    public string Level { get; set; }
    public int Year { get; set; }
    public string Department { get; set; }
}

public class readRoles : Roles
{
    public readRoles(DataRow dataRow)
    {
        UserName = (int)dataRow["UserName"];
        PersonName = (string)dataRow["PersonName"];
        Profile = (string)dataRow["Profile"];
        Level = (string)dataRow["Level"];
        Year = Convert.ToInt32(dataRow["Year"]);
        Department = (dataRow["Department"] == DBNull.Value) ? "No Department" : dataRow["Department"].ToString();
    }

    public int UserName { get; set; }
    public string PersonName { get; set; }
    public string Profile { get; set; }
    public string Level { get; set; }
    public int Year { get; set; }
    public string Department { get; set; }
}

并且在我的控制器中有以下代码：

public HttpResponseMessage Get(int id)
{
    string connString;
    SqlConnection con;
    connString = @"XXXXXXXXXX";
    DataSet _ds = new DataSet();
    con = new SqlConnection(connString);
    con.Open();

    var sql = @"select distinct CONCAT(CONCAT(mytable.surname, ', '), dbo.initcap(mytable.forenames)) as 'PersonName' 
    , convert(int, mytable.studentID) as 'UserName' 
    , mytable.category as 'Profile' 
    , mytable.level as 'Level' 
    , mytable.year as 'Year' 
    , mytable.depat as 'Department' 
    from MYTABLE 
    WHERE convert(int, mytable.studentID) = @UserName
    order by 2 desc ";

    SqlParameter param = new SqlParameter();
    param.ParameterName = "@UserName";
    param.Value = id;
    SqlCommand CMD2 = new SqlCommand(sql, con);
    CMD2.Parameters.Add("@UserName", SqlDbType.Int).Value = id;
    CMD2.Connection = con;
    CMD2.CommandText = sql;
    CMD2.CommandType = System.Data.CommandType.Text;
    SqlDataReader dr = CMD2.ExecuteReader();
    SqlDataAdapter _adapter = new SqlDataAdapter(sql, con);
    _adapter = new SqlDataAdapter
    {
        SelectCommand = new SqlCommand(sql, con)
    };
    _adapter.SelectCommand.Parameters.AddWithValue("@UserName", id);
    _adapter.Fill(_ds, "roles");

    List<Roles> roles = new List<Roles>(_ds.Tables[0].Rows.Count);

    if (_ds.Tables[0].Rows.Count > 0)
    {
        foreach (DataRow studentrole in _ds.Tables[0].Rows)
        {
            roles.Add(new readRoles(studentrole));
        }
    }

    foreach (DataRow drow in _ds.Tables[0].Rows)
    {
        var item = new List<object>();

        item.Add((string)drow["Profile"]);
        item.Add((string)drow["Level"]);
        item.Add(Convert.ToInt32(drow["Year"]));
        item.Add((drow["Department"] == DBNull.Value) ? "No Department" : drow["Department"].ToString());
        item.Add((string)drow["PersonName"]);
        item.Add((int)drow["UserName"]);

        studentRoles.Add(item);
    }

    var response = Request.CreateResponse(HttpStatusCode.OK, new { roles = studentRoles });

    // 下面的代码部分是将部门合并在一起，但仅适用于学生属于只有2个部门的情况，但有许多情况下学生属于多个部门
    if (roles.Count() > 1)
    {
        for (int i = 0; i < studentRoles[0].Count(); i++)
        {
            if (studentRoles[0][i].ToString() != studentRoles[1][i].ToString())
            {
                var arr = new JArray();
                arr.Add(studentRoles[0][i]);
                arr.Add(studentRoles[1][i]);
                studentRoles[0][i] = arr;
            }
        }
        studentRoles[1].Clear()
    }

    return response;
}

我遇到的问题是，我的代码如预期那样工作，但仅当学生属于只有2个部门时，例如上面的John Smith示例。我需要帮助调整上面的代码的最后部分，以适应返回多于2个元素的学生，因为有些学生属于多个部门。例如，如果John Smith属于3个部门：

{
    "roles": [
        [
            "STUDENT",
            "UG",
            2,
            "Politics",
            "Smith, John",
            123456
        ],
        [
            "STUDENT",
            "UG",
            2,
            "Religions",
            "Smith, John",
            123456
        ],
        [
            "STUDENT",
            "UG",
            2,
            "Languages

<details>
<summary>英文:</summary>

I am building a C# Web API and its purpose is to enable an external company to retrieve student data (they call them &#39;roles&#39;) via a GET call to the API I am building. Now, they have the choice of either retrieving all students via a call to https://localhost:XXXXX/custom-roles-api/campusCustomRoles or to retrieve a single student it&#39;d be https://localhost:XXXXX/custom-roles-api/campusCustomRoles/123456 with &#39;123456&#39; being the student Id number. Some students have one row of data as they below to a single department, so the JSON return in the GET call shows them in a single element, however for students who belong to 2 or more departments, the return shows more than one element, for example;

{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Smith, John",
123456
],
[
"STUDENT",
"UG",
2,
"Religions",
"Smith, John",
123456
]
]
}

In the above example, student John Smith belongs to 2 departments (Religions and Politics) and this is represented in two elements in the JSON return. What the external company have requested is for a single element which would look like this and this is where I am stuck;

{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Religions",
"Smith, John",
123456
]
]
}

I hasten to add that it was also one of the requirements to return only the Object values without the keys (or column names). 
The Below is what my Roles Class looks like;

public class Roles
{

    List<Roles> studentRoles = new List<Roles>();
public int UserName { get; set; }
public string PersonName { get; set; }
public string Profile { get; set; }
public string Level { get; set; }
public int Year { get; set; }
public string Department { get; set; }
}
public class readRoles : Roles
{
public readRoles(DataRow dataRow)
{
UserName = (int)dataRow["UserName"];
PersonName = (string)dataRow["PersonName"];
Profile = (string)dataRow["Profile"];
Level = (string)dataRow["Level"];
Year = Convert.ToInt32(dataRow["Year"]);
Department = (dataRow["Department"] == DBNull.Value) ? "No Department" : dataRow["Department"].ToString();
}
public int UserName { get; set; }
public string PersonName { get; set; }
public string Profile { get; set; }
public string Level { get; set; }
public int Year { get; set; }
public string Department { get; set; }
}

And below, I have this in my Controller;

public HttpResponseMessage Get(int id)
{
string connString;
SqlConnection con;
connString = @"XXXXXXXXXX";
DataSet _ds = new DataSet();
con = new SqlConnection(connString);
con.Open();

        var sql = @"select distinct CONCAT(CONCAT(mytable.surname, ', '),dbo.initcap(mytable.forenames)) as 'PersonName' 
,convert(int,mytable.studentID) as 'UserName' 
,mytable.category as 'Profile' 
,mytable.level as 'Level' 
,mytable.year as 'Year' 
,mytable.depat as 'Department' 
from MYTABLE 
WHERE convert(int,mytable.studentID) = @UserName
order by 2 desc ";
SqlParameter param = new SqlParameter();
param.ParameterName = "@UserName";
param.Value = id;
SqlCommand CMD2 = new SqlCommand(sql, con);
CMD2.Parameters.Add("@UserName", SqlDbType.Int).Value = id;
CMD2.Connection = con;
CMD2.CommandText = sql;
CMD2.CommandType = System.Data.CommandType.Text;
SqlDataReader dr = CMD2.ExecuteReader();
SqlDataAdapter _adapter = new SqlDataAdapter(sql, con);
_adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, con)
};
_adapter.SelectCommand.Parameters.AddWithValue("@UserName", id);
_adapter.Fill(_ds, "roles");
List<Roles> roles = new List<Roles>(_ds.Tables[0].Rows.Count);
if (_ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow studentrole in _ds.Tables[0].Rows)
{
roles.Add(new readRoles(studentrole));
}
}
foreach (DataRow drow in _ds.Tables[0].Rows)
{
var item = new List<object>();
item.Add((string)drow["Profile"]);
item.Add((string)drow["Level"]);
item.Add(Convert.ToInt32(drow["Year"]));
item.Add((drow["Department"] == DBNull.Value) ? "No Department" : drow["Department"].ToString());
item.Add((string)drow["PersonName"]);
item.Add((int)drow["UserName"]);
studentRoles.Add(item);
};
var response = Request.CreateResponse(HttpStatusCode.OK, new { roles = studentRoles });

// The below part of the code is where the departments are merged but this solution only works if there are only 2 departments, but there are many cases where a student has more than 2 departments
if (roles.Count() > 1)
{
for (int i = 0; i < studentRoles[0].Count(); i++)
{
if (studentRoles[0][i].ToString() != studentRoles[1][i].ToString())
{
var arr = new JArray();
arr.Add(studentRoles[0][i]);
arr.Add(studentRoles[1][i]);
studentRoles[0][i] = arr;
}
}
studentRoles[1].Clear()
}

        return response;
}

The problem I am having is that, my code works as expected but ONLY if a student belongs to only 2 departments, like our example above, John Smith. I need help tweaking the above last bit of code to accommodate students who will be returned with more than 2 elements as some belong to multiple departments. For example, if John Smith belonged to 3 departments;

{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Smith, John",
123456
],
[
"STUDENT",
"UG",
2,
"Religions",
"Smith, John",
123456
],
[
"STUDENT",
"UG",
2,
"Languages",
"Smith, John",
123456
]
]
}

I would expect John Smith's return to look like this;

{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Religions",
"Languages",
"Smith, John",
123456
]
]
}

Eventually, the external company's requirement would be for students' Modules to be included, and of course students will have multiple Modules, but I'll cross that bridge when I get there, ha! Any help would be appreciated.
</details>
# 答案1
**得分**: 1
将我的解决方案添加到您的代码中：https://stackoverflow.com/a/75485207/11392290

var roles = JArray.From(studentRoles);

if (roles.Count() > 1)
{
.... 使用我的代码
}

var response = Request.CreateResponse(HttpStatusCode.OK, new { roles = roles });

<details>
<summary>英文:</summary>
add to your code my solution https://stackoverflow.com/a/75485207/11392290

var roles = JArray.From(studentRoles);

if (roles.Count() > 1)
{
.... use my code
}

var response = Request.CreateResponse(HttpStatusCode.OK, new { roles = roles });

</details>