这是我的 C# 代码
var result = db.Quotes_Items_Suppliers
.Include(tbl => tbl.tblItems)
.Include(tbl => tbl.tblQuotes)
.Include(tbl => tbl.tblSuppliers).GroupBy(tbl => new { tbl.tblQuotes, tbl.tblSuppliers, tbl.tblItems })
.Select(grouped => new QuoteViewModel
{
Quote = new Quote { ID = grouped.Key.tblQuotes.ID,
QuoteNo = grouped.Key.tblQuotes.QuoteNo ,
Date = grouped.Key.tblQuotes.RequestDate},
Supplier = new Supplier { ID = grouped.Key.tblSuppliers.ID,
Name = grouped.Key.tblSuppliers.Supplier,
TelNo = grouped.Key.tblSuppliers.Tel },
Item = new Item { ID = grouped.Key.tblItems.ID,
Name = grouped.Key.tblItems.Name,
PartNo = grouped.Key.tblItems.PartNo },
Quantity = grouped.Sum(tbl => tbl.Quantity),
Price = grouped.Sum(tbl => tbl.Price)
});
AllQuotes AllQuotes = new AllQuotes();
foreach (var quoteId in db.Quotes_Items_Suppliers.Select(qis => qis.QuoteID).Distinct())
{
int id = quoteId;
foreach (var row in result.Where(row => row.Quote.ID == id))
{
Request Req = new Request();
Req.Quote = new Quote { ID = row.Quote.ID,
QuoteNo = row.Quote.QuoteNo,
Date = row.Quote.Date };
// should this be another foreach loop to select all the suppliers for this quote?
Req.Suppliers = new Supplier { ID = row.Supplier.ID,
Name = row.Supplier.Name,
TelNo = row.Supplier.TelNo };
// again should this be another foreach loop to select all the items for this quote?
Req.Items = new Item { ID = row.Item.ID,
Name = row.Item.Name,
PartNo = row.Item.PartNo };
Req.Quantity = row.Quantity;
Req.Price = row.Price;
AllQuotes.Reqs.Add(Req);
}
}
这是我的类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace ITAPP.Models
{
public class Quote
{
public int ID { get; set; }
public string QuoteNo { get; set; }
public DateTime? Date { get; set; }
}
public class Item
{
public int ID { get; set; }
public string Name { get; set; }
public string PartNo { get; set; }
}
public class Supplier
{
public int ID { get; set; }
public string Name { get; set; }
public string TelNo { get; set; }
}
public class QuoteViewModel
{
public Quote Quote { get; set; }
public Item Item { get; set; }
public Supplier Supplier { get; set; }
public int Quantity { get; set; }
public decimal Price { get; set; }
}
public class Request
{
public Quote Quote { get; set; }
public Item Items { get; set; }
public Supplier Suppliers { get; set; }
public int Quantity { get; set; }
public decimal Price { get; set; }
}
public class AllQuotes
{
public List<Request> Reqs { get; set; }
}
}
我不确定自己是否理解正确,实际上我知道我没有。
我认为我需要一个供应商列表和每个请求中的项目列表,然后对于每个报价项目和每个报价供应商,我都要使用foreach将数据添加到这些列表中。是这样吗?
基本上我应该有:- 报价 x数量的报价项目 x数量的为每个项目提供报价及其价格的供应商
希望以上内容足以让人们理解我想做的事情?
谢谢
编辑:样例
以下是我希望数据看起来像的样子
IS0001 20/11/2013
| HP DL360p | Samsung 840 Pro 256GB | Total
ITSupplier | 1132 | 477 | 1609
ITComms | 1160 | 510 | 1670
ITSol | 1135 | 160 | 1295
这里是表格布局。
tblQuotes
ID | QuoteNo | Date
tblItems
ID | Itemname | Part No
tblSuppliers
ID | SupplierName | TelNo
tblQuotes_Items_Suppliers
ID | QuoteID | ItemID | SupplierID | Quantity | Price
每个报价应该显示每个供应商的每个项目的价格。