mysql - 如何只获取列表中指定的项目？

Question

我使用以下 MySql 查询从数据库中提取以下数据:

SELECT vs.value, vs.is_header, vsa.is_required, vsa.name, vsar.value
FROM vista_struttura AS vs
LEFT JOIN vista_struttura_attributi AS vsa
    ON vs.id_vista_struttura = vsa.id_vista_struttura
LEFT JOIN vista_struttura_attributi_raccordi AS vsar
    ON vsa.input_type = vsar.input_type
ORDER BY vs.sort;

提取的数据是

我必须使用以下代码将此数据保存在我自己构建的模型中:

var model = new List<Header>();
using (var connection = new MySqlConnection(connectionString))
{
    connection.Open();                                  
    var sql =   "SELECT vs.value, vs.is_header, vsa.is_required, vsa.name, vsar.value " +
                "FROM vista_struttura AS vs " +
                "LEFT JOIN vista_struttura_attributi AS vsa " +
                    "ON vs.id_vista_struttura = vsa.id_vista_struttura " +
                "LEFT JOIN vista_struttura_attributi_raccordi AS vsar " +
                    "ON vsa.input_type = vsar.input_type " +
                "ORDER BY vs.sort";
    var cmd = new MySqlCommand(sql, connection);
    var rdr = cmd.ExecuteReader();  

    var rows = new List<ViewProperties>();  

    while (rdr.Read())
    {         
        var value = rdr[0].ToString();
        var isHeader = Convert.ToBoolean(rdr[1]);
        var isRequired = (rdr[2] == DBNull.Value) ? (bool?) null : Convert.ToBoolean(rdr[2]);
        var name = rdr[3].ToString();
        var inputType = rdr[4].ToString();

        var properties = new ViewProperties()
        {
            Value = value,
            IsHeader = isHeader,
            IsRequired = isRequired,
            Name = name,
            InputType = inputType
        };     

        rows.Add(properties); 

        var header = new Header()
        {            
            HeaderValue = (properties.IsHeader == true) ? properties.Value : null,
            Rows = rows
        };
        if (header.HeaderValue != null)
        {
            model.Add(header);
        }

    }                
} 

模型

标题

public class Header
{
    public string HeaderValue { get; set; }
    public IList<ViewProperties> Rows { get; set; }
}

查看属性

public class ViewProperties
{
    public string Value { get; set; }
    public bool IsHeader { get; set; }
    public bool? IsRequired { get; set; }
    public string Name { get; set; }
    public string InputType { get; set; }
}

调试应用程序时我得到了错误的模型，不是我想要的......

我想获取第一个标题的前 4 行和第二个标题的最后 3 行。

我该怎么办？

更好的做法是:在获取此模型然后使用 linq 处理它，还是获取已经正确的模型？

谢谢

Answer 1

您需要以下代码才能获得所需的结果，请进行必要的修改:

var rows = new List<ViewProperties>();  // ViewProperties List

// Segregate null value, GroupBy to aggregate using Value
var viewPropertiesGrouping = rows.Where(x => x.IsHeader)
                                 .GroupBy(x => x.Value);

// Traverse through IEnumerable<IGrouping<string,ViewProperties>>, created above and fill the Header object and add to the Model

foreach (var prop in viewPropertiesGrouping)
{
    Header header  = new Header();
    header.HeaderValue = prop.Key;
    header.Rows = prop.Select(y => y).ToList();
    model.Add(header);
}