我正在尝试将c#中的两个表组合用于orderManagement.where“SalesOrderDetails”表返回查询结果1逗号分隔的产品代码和数量
“SalesOrder”表返回订单明细。我有写存储过程
ALTER procedure [dbo].[SP_GetAllOrders]
AS
BEGIN
SELECT
SalesOrder.OrderDate,
SalesOrder.OrderTitle,
SalesOrder.OrderPriority,
Customer.Lname,
Customer.Fname,
OrderType.OrderTypeName,
(
SELECT
CAST( ProductCode AS varchar ) + ':' + CAST( Quantity AS varchar ) + ','
FROM
SalesOrderDetails
WHERE
SalesOrderDetails.SalesOrderId = SalesOrder.SalesOrderId
FOR xml path('')
) AS 'Product'
FROM
SalesOrder
INNER JOIN Customer ON SalesOrder.CustomerId = Customer.CustomerId
INNER JOIN OrderType ON SalesOrder.OrderTypeId = OrderType.OrderTypeId
END
它给了我完美的output.same输出我想要的,而不使用存储过程。我想要使用实体框架,linq和c#asp.net的相同输出。我被困在这里
这是我试过的
TrainingDemoEntities entity = new TrainingDemoEntities();
var details = (from order in entity.SalesOrders
join cust in entity.Customers
on order.CustomerId equals cust.CustomerId
join ordertypevalue in entity.OrderTypes
on order.OrderTypeId equals ordertypevalue.OrderTypeId
select new
{
orderId = order.SalesOrderId,
orderDate = order.OrderDate,
orderTitle = order.OrderTitle,
orderPriority = order.OrderPriority,
orderType = order.OrderType,
productstr = string.Join(",", (from prod in entity.SalesOrderDetails
where prod.SalesOrderId == order.SalesOrderId
select prod.ProductCode + ":" + prod.Quantity))
}).ToList();
发布于 2019-03-29 09:52:24
如果您正在使用EF,那么您可以使用模型中的关系而无需考虑连接。首先得到数据:
var res = (from so in context.SalesOrder
let productStrings = so.SalesOrderDetails
.Select(d => d.ProductCode.ToString() + ":" + d.Quantity.ToString())
select new
{
OrderDate = so.OrderDate,
OrderTitle = so.OrderTitle,
OrderPriority = so.OrderPriority,
Lname = so.Customer.Lname,
Fname = so.Customer.Fname,
OrderTypeName = so.OrderType.OrderTypeName,
Products = productStrings
}).ToList();
Products是一个字符串列表。由于Linq-sql不接受string.join或aggregate,因此在调用数据库之后必须加入字符串。
上面的结果是匿名类型,但您也可以选择进入DTO。在这种情况下,您可以为ProductList(字符串)添加一个getter,您可以在其中加入Products的结果。在这种情况下,您不必像这样处理结果:
var details = res.Select(o => new DTO
{
OrderDate = o.OrderDate,
OrderTitle = o.OrderTitle,
OrderPriority = o.OrderPriority,
Lname = o.Lname,
Fname = o.Fname,
OrderTypeName = o.OrderTypeName,
Products = o.Products.Aggregate((a, b) => (a + ", " + b))
}).ToList();
请注意,top查询返回了大量记录。每个产品(如Excel)的一条记录(包括所有列)。因此,如果您两次查询数据库,您将获得更好的性能。一旦填写DTO记录,然后填写详细信息。
https://stackoverflow.com/questions/-100005167
复制相似问题