c# - 将 MySQL 查询中项目的嵌套列表属性映射到对象

Question

我想获取产品的详细信息以及该产品的图片列表。下面的代码工作正常，但它只返回一张产品图片。我在将 MySQL 查询的结果映射到 C# 对象时遇到问题。

这是我到目前为止尝试过的

public ProductModel GetProductDetail(uint product_id)
{
    var cmd = this.MySqlDatabase.Connection.CreateCommand() as MySqlCommand;
    cmd.CommandText = @"SELECT products.*, images.* FROM products LEFT JOIN images ON products.product_id= images.product_id WHERE products.product_id = @product_id LIMIT 0,1";
    cmd.Parameters.AddWithValue("@product_id", product_id);
    using (var reader = cmd.ExecuteReader())
        while (reader.Read())
        {
            return new ProductModel()
            {
                product_id = reader.GetValue<UInt32>("product_id"),
                subcategory_id = reader.GetValue<UInt32>("subcategory_id"),
                product_name = reader.GetValue<String>("product_name"),
                description = reader.GetValue<String>("description"),
                is_recent = reader.GetValue<Boolean>("is_recent"),
                is_popular = reader.GetValue<Boolean>("is_popular"),
                is_available = reader.GetValue<Boolean>("is_available"),
                price = reader.GetValue<Decimal>("price"),
                overall_rating = reader.GetValue<Double>("overall_rating"),
                views = reader.GetValue<UInt32>("views"),
                people_rated = reader.GetValue<UInt32>("people_rated"),
                date_posted = reader.GetValue<DateTime>("date_posted"),
                Images = new List<ImagesModel>
                {
                    new ImagesModel
                    {
                        imageurl = reader.GetValue<String>("imageurl"),
                        created_date = reader.GetValue<DateTime>("created_date")
                    }
                }
            };
        }
    return null;
}

下面也是我得到的 JSON 结果

{
    "code": 1,
    "message": "Product found",
    "document": {
        "product_id": 11,
        "subcategory_id": 22,
        "product_name": "Lead Guitar",
        "description": "Unde dolor sed natus velit omnis doloribus consequatur adipisci quos quasi laboriosam optio aut beatae; cupiditate modi animi hic non qui consequatur, natus voluptatem quae ea quia est doloremque possimus.",
        "is_recent": true,
        "is_popular": false,
        "is_available": false,
        "price": 1942.51,
        "overall_rating": 3,
        "views": 520,
        "people_rated": 350,
        "date_posted": "2005-05-08T06:21:03",
        "images": [
            {
                "imageurl": "http://www.thetehad.es/but/teerted/anne/ionhe.png",
                "created_date": "2021-08-28T12:33:01"
            },
            .
            .
            . 
            // THERE SHOULD BE MORE IMAGES HERE BUT I AM GETTING ONLY ONE
        ]
    }
}

请问我在这里做错了什么？非常感谢任何帮助。

标签： c#mysqlsqlado.net

Answer 1

有几个问题需要指出。

问题

第一的

SELECT products.*, images.* FROM products LEFT JOIN images ON products.product_id= images.product_id WHERE products.product_id = @product_id LIMIT 0,1

您的 MYSQL 查询包含LIMIT 0,1，这意味着它将仅返回第一行。

LEFT JOIN用于此查询不是那么正确，因为通过删除，LIMIT 0,1它返回 1 xn 记录，其中 n 是具有相同 的图像记录的数量product_id。

第二

return new ProductModel()
{
    Images = new List<ImagesModel>
    {
        new ImagesModel
        {
            imageurl = reader.GetValue<String>("imageurl"),
            created_date = reader.GetValue<DateTime>("created_date")
        }
    }
};

您创建new ProductModel()和分配只包含Images一个ImagesModel 对象。List<ImagesModel>

解决方案

对于第一个问题：将查询分成两部分并使用两个 DataReader：

• 第一个 DataReader 只会Product根据 ProductID 查询记录
• 第二个DataReader会Image根据ProductID查询记录

对于第二个问题：从第二个 DataReader 检索图像记录并使用循环创建图像对象

public ProductModel GetProductDetail(uint product_id)
{
    ProductModel product = null;

    using (var productCmd = this.MySqlDatabase.Connection.CreateCommand() as MySqlCommand)
    {
        productCmd.CommandText = @"SELECT * FROM products WHERE product_id = @product_id";
        productCmd.Parameters.Add("@product_id", SqlDbType.int).Value = product_id;

        using (var reader = productCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                product = new ProductModel()
                {
                    product_id = reader.GetValue<UInt32>("product_id"),
                    subcategory_id = reader.GetValue<UInt32>("subcategory_id"),
                    product_name = reader.GetValue<String>("product_name"),
                    description = reader.GetValue<String>("description"),
                    is_recent = reader.GetValue<Boolean>("is_recent"),
                    is_popular = reader.GetValue<Boolean>("is_popular"),
                    is_available = reader.GetValue<Boolean>("is_available"),
                    price = reader.GetValue<Decimal>("price"),
                    overall_rating = reader.GetValue<Double>("overall_rating"),
                    views = reader.GetValue<UInt32>("views"),
                    people_rated = reader.GetValue<UInt32>("people_rated"),
                    date_posted = reader.GetValue<DateTime>("date_posted")  
                };
            }
        }          
    }
           
    if (product == null)
        return null;

    var imageList = new List<ImagesModel>(); 
    using (var imageCmd = this.MySqlDatabase.Connection.CreateCommand() as MySqlCommand)
    {
        imageCmd.CommandText = @"SELECT * FROM images WHERE product_id = @product_id";
        imageCmd.Parameters.Add("@product_id", SqlDbType.int).Value = product_id;

        using (var reader = imageCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                imageList.Add(new ImagesModel
                {
                    imageurl = reader.GetValue<String>("imageurl"),
                    created_date = reader.GetValue<DateTime>("created_date")
                });
            }
        }          
    }
    
    product.Images = imageList;    
    return product;
}