C# 一对多关系

Question

我在 SQL 中有这个查询

SELECT p.Price, p.ID, p.Name, c.Name as CategoryName, p.CategoryID, c.ID AS CategoryPK,c.ParentID
,o.ID as OrderID,o.ProductID,o.ClientCompanyID,o.Quentity
FROM Products p LEFT JOIN Categories c ON p.CategoryID=c.ID LEFT JOIN Orders o ON o.ProductID=p.ID

还有这段代码

static List<Product> GetProducts(SqlCommand command)
{
    SqlDataReader reader = command.ExecuteReader();
    List<Product> listProducts = new List<Product>();
    while (reader.Read())
    {
        Product product = new Product();
        product.ID = reader.GetInt32(reader.GetOrdinal("ID"));

        product.Name = (!reader.IsDBNull(reader.GetOrdinal("Name")) ? (string)reader["Name"] : null);

        product.Price = reader.GetDecimal(reader.GetOrdinal("Price"));
        product.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
        Category newCat = new Category();
        newCat.ID = reader.GetInt32(reader.GetOrdinal("CategoryPK"));
        newCat.CategoryName = reader.GetString(reader.GetOrdinal("CategoryName"));
        newCat.Parent = (!reader.IsDBNull(reader.GetOrdinal("ParentID")) ? reader.GetInt32(reader.GetOrdinal("ParentID")) : 0);
        product.Category = newCat;

        Order order = new Order();
        order.ID = (!reader.IsDBNull(reader.GetOrdinal("OrderID")) ? (int?)reader["OrderID"] : null);
        order.ClientCompanyID = (!reader.IsDBNull(reader.GetOrdinal("ClientCompanyID")) ? (int?)reader["ClientCompanyID"] : null);
        order.ProductID = (!reader.IsDBNull(reader.GetOrdinal("ProductID")) ? (int?)reader["ProductID"] : null);
        order.Quentity = (!reader.IsDBNull(reader.GetOrdinal("Quentity")) ? (int?)reader["Quentity"] : null);

        List<Order> listOrders = new List<Order>();
        listOrders.Add(order);

        product.Orders = listOrders;
        listProducts.Add(product);
    }
    reader.Close();
    return listProducts;
}

问题是:当我像这样从单个产品调用订单时，如何在不丢失任何订单的情况下删除重复项:

{
    foreach (var item in listProducts)
    {
        Console.WriteLine("Product Name : {0} Price : {1} Category : {2}", item.Name, item.Price, item.Category.CategoryName, item);

        foreach (var orders in item.Orders)
        {
            Console.WriteLine("Order : " + orders.Quentity + " Price : " + (float)item.Price * orders.Quentity);
        }
    }
}

我希望在调用 product.order.id 时显示该产品的所有订单。我应该如何建立一对多关系？

产品 |订单

产品 A|######X

.#########|######Y

产品 B|######Z

###|######K

我的意思是产品 A 可以有很多订单，产品 B 也一样。一对多如何实现这一点。

Answer 1

创建 C# 对象时，您只需检查产品是否已存在。
像这样:

static List<Product> GetProducts(SqlCommand command)
{
    SqlDataReader reader = command.ExecuteReader();
    List<Product> listProducts = new List<Product>();
    while (reader.Read())
    {
        int productId = reader.GetInt32(reader.GetOrdinal("ID"));
        Product product = null;
        //check if product exists
        foreach (Product p in listProducts)
        {
           if(p.ID = productId)
               product = p;//product already exists
        }

       if(product == null)
       {
          //product doesn't exist, create new
          product = new Product();
          product.ID = productId;

          product.Name = (!reader.IsDBNull(reader.GetOrdinal("Name")) ? (string)reader["Name"] : null);

          product.Price = reader.GetDecimal(reader.GetOrdinal("Price"));
          product.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
          Category newCat = new Category();
          newCat.ID = reader.GetInt32(reader.GetOrdinal("CategoryPK"));
          newCat.CategoryName = reader.GetString(reader.GetOrdinal("CategoryName"));
          newCat.Parent = (!reader.IsDBNull(reader.GetOrdinal("ParentID")) ? reader.GetInt32(reader.GetOrdinal("ParentID")) : 0);
          product.Category = newCat;
          product.Orders = new List<Order>();
        }

        //add order to product
        Order order = new Order();
        order.ID = (!reader.IsDBNull(reader.GetOrdinal("OrderID")) ? (int?)reader["OrderID"] : null);
        order.ClientCompanyID = (!reader.IsDBNull(reader.GetOrdinal("ClientCompanyID")) ? (int?)reader["ClientCompanyID"] : null);
        order.ProductID = (!reader.IsDBNull(reader.GetOrdinal("ProductID")) ? (int?)reader["ProductID"] : null);
        order.Quentity = (!reader.IsDBNull(reader.GetOrdinal("Quentity")) ? (int?)reader["Quentity"] : null);
        product.Orders.Add(order);
        listProducts.Add(product);
    }
    reader.Close();
    return listProducts;
}

如果您的 SQL 结果是按产品排序的，您可以加快速度，这样您就可以简单地检查它是否与上一个产品不同，而不是检查当前产品是否已经存在。 (如果不同，则制作新产品，如果没有不同，只需将订单添加到上一个产品即可。)