gpt4 book ai didi

c# - Linq查询问题(ado.net请求太多)

转载 作者:太空宇宙 更新时间:2023-11-03 14:26:31 26 4
gpt4 key购买 nike

我的 linq 应用程序有问题。

我有OrdersOrderChangeLogOrderItems 表。在 1 个查询中,我想加载订单和相关表。我使用 Linq。

 return from p in _db.dbOrders
select new Order
{
ID = p.ID,
OrderStatusChangelog = new List<OrderStatusChangelog>( GetOrderStatusChangelog().Where(x => x.OrderID == p.ID)),
Items = new List<OrderItem>(GetOrderItems(p.ID)), };

在这个变体中,它需要太多的 ADO.NET 请求(见图 1) image1 full size image1

但是。如果我评论

Items = new List<OrderItem>(GetOrderItems(p.ID))

完美结果 ( image2 full size ) image2

为什么一个人这样工作?

附言我的 T-SQL(由 LINQ 生成):

{SELECT [t0].[ID], [t0].[UserID], [t0].[DateOrder] AS [DateCreated], [t0].[ControlGUID] AS [Guid], [t0].[StatusID], [t1].[ID] AS [ID2], [t1].[OrderID], [t1].[StatusID] AS [OrderStatusID], [t1].[Comment] AS [StatusMessage], [t1].[UserID] AS [UserID2], [t1].[Date], [t2].[FullName] AS [UserName], (
SELECT COUNT(*)
FROM [dbo].[dbOrderStatusChangelog] AS [t3]
INNER JOIN [dbo].[dbUsers] AS [t4] ON [t4].[ID] = [t3].[UserID]
WHERE [t3].[OrderID] = [t0].[ID]
) AS [value], [t0].[ShippingFLP], [t0].[ShippingAddress] AS [ShippingAddressContent], [t0].[ShippingRegionID], [t0].[ShippingCity], [t0].[ShippingZIPCode], [t0].[ShippingPhone], [t0].[ShippingMetroID], [t0].[PaymentFLP], [t0].[PaymentAddress] AS [PaymentAddressContent], [t0].[PaymentRegionID], [t0].[PaymentCity], [t0].[PaymentZIPCode], [t0].[PaymentPhone], [t0].[TrackingNumber], [t0].[DateShipped], [t0].[ShippingCost] AS [Rate], [t0].[ShippingName] AS [Name], [t0].[ShippingTypeID], [t0].[PaymentName] AS [Name2], [t0].[PaymentTypeID], [t0].[SourceID], [t0].[CustomerComment], [t0].[CustomerEmail], [t0].[CustomerFLP], [t0].[DiscountAmount] AS [discountAmount], [t0].[DiscountReason] AS [discountReason], [t0].[Amount]
FROM [dbo].[dbOrders] AS [t0]
LEFT OUTER JOIN ([dbo].[dbOrderStatusChangelog] AS [t1]
INNER JOIN [dbo].[dbUsers] AS [t2] ON [t2].[ID] = [t1].[UserID]) ON [t1].[OrderID] = [t0].[ID]
WHERE (CONVERT(Int,[t0].[StatusID])) IN (@p0, @p1, @p2)
ORDER BY [t0].[ID] DESC, [t1].[ID], [t2].[ID]}

表格图 tables

UPD1

 private IQueryable<OrderItem> GetOrderItems(int orderID)
{
return from p in _db.dbOrderItems
where p.OrderID == orderID
select new OrderItem
{
ID = p.ID,
ItemPrice = p.Price,
OrderID = p.OrderID,
Quantity = p.Quantity,
Product = new Product
{
ID = p.ProductID,
Name = p.ProductName,
Brand = new Brand { Name = p.dbProduct.dbBrand.Name }
}
};
}


private IQueryable<OrderStatusChangelog> GetOrderStatusChangelog()
{
return from p in _db.dbOrderStatusChangelogs
select new OrderStatusChangelog
{
Date = p.Date,
ID = p.ID,
OrderID = p.OrderID,
OrderStatusID = p.StatusID,
StatusMessage = p.Comment,
UserID = p.UserID,
UserName = p.dbUser.FullName
};
}

最佳答案

Items = new List...

这将导致您正在查询的项目的枚举,强制为每个创建的列表(和复制的元素)调用数据库。

您可以推迟枚举和对消费者的调用,让他们将项目转换为列表,或者您可以创建一个方法来按需为他们创建列表,或者通过将您的定义定义为IEnumerable 并返回 IQueryable

真正的问题是,是否要求每个项目都是一个列表并在主查询中完全填充,或者您是否可以延迟执行直到对数据采取行动?

关于c# - Linq查询问题(ado.net请求太多),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3832773/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com