我必须重写一个巨大的SQL查询，该查询使用实体框架和Linq从大约10个表中获取数据。简而言之，查询从主表中获取产品列表，然后从这些产品的其他表中获取其他信息，如下载计数、销售计数、作者等。
我决定做的是将其分解为多个部分，以便其他人易于理解和编辑。
首先，我得到产品清单。然后，我得到其他列的列表数据，并在最后一步中将所有内容合并在一起。
var mainTableQuery = from p in context.mains
select new
{
p.ProductID,
p.title,
p.department_id,
p.TotalInStock
};
//Getting the top author by priority for every product ID
var authorLinkQuery= from authorLink in context.AuthorLinks
group authorLink by authorLink.ProductID
into groups
select groups.OrderBy(p=>p.Priority).FirstOrDefault();
// Link productIDs with primary author names
var authorsFinalQuery = from author in context.Authors
join authorLink in authorLinkQuery
on author.AuthorID equals authorLink.AuthorID
select new
{
authorLink.ProductID,
Name = (String.IsNullOrEmpty(author.Company) ? author.FirstName + " " + author.LastName : author.Company)
};
var finalQuery = from main in mainTableQuery
join author in authorsFinalQuery
on main.ProductID equals author.ProductID
select new
{
main,
author.Name
};
最终查询获取正确的数据，但大约需要 30 秒。最初的SQL服务器查询只花了6秒。问题是实体框架生成的 SQL 服务器查询效率非常低。有没有办法优化这一点？
编辑：我尝试替换的完整存储过程是：
CREATE PROCEDURE [dbo].[usp_AllProducts] (@ProgramAreaID int,
@CustomDateStart datetime,
@CustomDateEnd datetime,
@ShowNLA nvarchar(50))
AS
BEGIN
SET NOCOUNT ON;
IF @ProgramAreaID = 1000
BEGIN
SELECT TOP (100) PERCENT
BaseInventory.ProductID,
BaseInventory.ReferenceNumber,
BaseInventory.Title,
BaseInventory.StoredBy,
BaseInventory.TotalInStock,
BaseInventory.StockAtEDC,
BaseInventory.Cost,
BaseInventory.RetailPrice,
BaseInventory.PubDate,
BaseInventory.DepartmentCode,
vwAuthor.PrimaryAuthor,
ISNULL(Sales30Days.QuantitySold, 0) AS Last30Days,
ISNULL(SalesPastYear.QuantitySold, 0) AS Last365Days,
ISNULL(CustomRangeSales.QuantitySold, 0) AS CustomRange,
ISNULL(SalesPastYear.ZeroStock, '12/31/9999') AS ZeroStock,
ISNULL(SalesPastYear.ZeroStockFullDate, CONVERT(datetime, '12/31/9999', 102)) AS ZeroStockFullDate,
BaseInventory.FullPubDate,
BaseInventory.ProgramArea,
BaseInventory.Spaces,
BaseInventory.DigitalFileExists,
BaseInventory.DownloadCount,
BaseInventory.DownloadCountCustom,
ISNULL(SalesPastYear.SpacesSold, 0.000) AS SpacesSold,
BaseInventory.DateReceived,
BaseInventory.ProductWeight,
BaseInventory.NumberOfPages,
BaseInventory.DigitalFileCount,
BaseInventory.ActiveDigitalFileCount
FROM (SELECT TOP (100) PERCENT
m.ProductWeight,
m.ProductID,
m.reference_number AS ReferenceNumber,
m.title AS Title,
MAX(sb.StoredBy) AS StoredBy,
m.TotalInStock,
m.edc_copies AS StockAtEDC,
m.pub_cost AS Cost,
m.RetailPrice,
m.pub_date AS PubDate,
d.department_code AS DepartmentCode,
m.department_id AS DepartmentId,
dg.department_group AS ProgramArea,
m.FullPubDate,
ISNULL(Boxes.Spaces, CASE
WHEN sb.StoredBy = 'WWW' THEN 0
ELSE 1
END) AS Spaces,
CASE
WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
ELSE 'Active'
END AS DigitalFileExists,
ISNULL(DLs.DownloadCount, 0) AS DownloadCount,
ISNULL(DLsCustom.DownloadCountCustom, 0) AS DownloadCountCustom,
MAX(history.DateReceived) AS DateReceived,
ISNULL(m.number_of_pages, 'N/A') AS NumberOfPages,
(SELECT
COUNT(*)
FROM tblDigitalMedia tD
WHERE tD.ProductID = m.ProductID)
AS DigitalFileCount,
(SELECT
COUNT(*)
FROM tblDigitalMedia tD
WHERE tD.ProductID = m.ProductID
AND tD.Active = 1)
AS ActiveDigitalFileCount
FROM dbo.main m
INNER JOIN dbo.departments d
ON m.department_id = d.department_id
INNER JOIN dbo.department_groups dg
ON d.department_group_id = dg.department_group_id
/* InventoryTemp should be called InventoryByLocation */
LEFT OUTER JOIN (SELECT
m1.ProductID,
SUM(ISNULL(it.NumberOfBoxes, 0)) + 1 AS Spaces
FROM dbo.main AS m1
INNER JOIN dbo.InventoryTemp AS it
ON m1.ProductID = it.ProductID
GROUP BY m1.ProductID) AS Boxes
ON m.ProductID = Boxes.ProductID
/* JobsTemp should be called JobHistory */
LEFT OUTER JOIN dbo.JobsTemp history
ON m.ProductID = history.ProductID
LEFT OUTER JOIN (
/* If dates are null then fetch for previous year. */
SELECT TOP (100) PERCENT
dm1.ProductID,
COUNT(dl1.TimeStamp) AS DownloadCountCustom
FROM dbo.tblDigitalMedia AS dm1
LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl1
ON dm1.FileID = dl1.FileID
WHERE ((@CustomDateStart IS NOT NULL)
AND (dl1.TimeStamp >= @CustomDateStart)
AND (dl1.TimeStamp <= @CustomDateEnd))
OR ((@CustomDateStart IS NULL)
AND (dl1.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()))
GROUP BY dm1.ProductID) AS DLsCustom
ON m.ProductID = DLsCustom.ProductID
LEFT OUTER JOIN (
/* If dates are null then fetch for previous year. */
SELECT TOP (100) PERCENT
dm2.ProductID,
COUNT(dl2.TimeStamp) AS DownloadCount
FROM dbo.tblDigitalMedia AS dm2
LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl2
ON dm2.FileID = dl2.FileID
WHERE dl2.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()
GROUP BY dm2.ProductID) AS DLs
ON m.ProductID = DLs.ProductID
LEFT OUTER JOIN dbo.tblDigitalMedia AS dm
ON m.ProductID = dm.ProductID
/* InventoryTemp should be called InventoryByLocation */
LEFT OUTER JOIN dbo.InventoryTemp it2
ON m.ProductID = it2.ProductID
INNER JOIN dbo.StoredBy sb
ON m.StoredByID = sb.StoredByID
WHERE (m.edc_isle LIKE N'Aisle' + N'%'
OR m.edc_isle = N'Digital Media'
OR m.edc_isle = N'Duplicator')
AND NOT (d.department_id BETWEEN 995 AND 999)
GROUP BY m.ProductWeight,
m.ProductID,
m.reference_number,
m.title,
m.TotalInStock,
dg.department_group_id,
m.pub_date,
m.department_id,
d.department_code,
m.pub_cost,
m.RetailPrice,
m.FullPubDate,
m.edc_isle,
sb.StoredBy,
dg.department_group,
CASE
WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
ELSE 'Active'
END,
ISNULL(DLs.DownloadCount, 0),
ISNULL(DLsCustom.DownloadCountCustom, 0),
ISNULL(Boxes.Spaces, CASE
WHEN sb.StoredBy = 'WWW' THEN 0
ELSE 1
END),
m.edc_copies,
ISNULL(m.number_of_pages, 'N/A')
HAVING (NOT (MAX(sb.StoredBy) = @ShowNLA))
ORDER BY m.FullPubDate) AS BaseInventory /* ***** Table A ***** */
LEFT OUTER JOIN (SELECT
dbo.shopping_cart.ProductID,
SUM(dbo.shopping_cart.quantity) AS QuantitySold
FROM dbo.orders
INNER JOIN dbo.shopping_cart
ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
WHERE dbo.orders.OrderTimeStamp >= DATEADD(DAY, -30, GETDATE())
AND dbo.orders.OrderTimeStamp < GETDATE()
AND dbo.shopping_cart.ProductID IS NOT NULL
GROUP BY dbo.shopping_cart.ProductID) AS Sales30Days /* ***** Table B ***** */
ON BaseInventory.ProductID = Sales30Days.ProductID
LEFT OUTER JOIN (SELECT
dbo.shopping_cart.ProductID,
SUM(dbo.shopping_cart.quantity) AS QuantitySold
FROM dbo.orders
INNER JOIN dbo.shopping_cart
ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
WHERE @CustomDateStart IS NOT NULL
AND dbo.orders.OrderTimeStamp >= @CustomDateStart
AND dbo.orders.OrderTimeStamp <= @CustomDateEnd
AND dbo.shopping_cart.ProductID IS NOT NULL
GROUP BY dbo.shopping_cart.ProductID) AS CustomRangeSales /* ***** CustomRangeSales ***** */
ON BaseInventory.ProductID = CustomRangeSales.ProductID
LEFT OUTER JOIN (SELECT TOP (100) PERCENT
cart3.ProductID,
SUM(cart3.quantity) AS QuantitySold,
m3.TotalInStock,
CASE
WHEN
CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
ELSE CONVERT(varchar, DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE()), 101)
END AS ZeroStock,
CASE
WHEN
CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
ELSE DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE())
END AS ZeroStockFullDate,
CONVERT(decimal(18, 3), SUM(cart3.quantity) / ISNULL(CONVERT(decimal(18, 2), AvgInventory.NumberPerBox), SUM(cart3.quantity) + m3.TotalInStock)) AS SpacesSold,
AvgInventory.NumberPerBox
FROM dbo.orders AS o1
INNER JOIN dbo.shopping_cart AS cart3
ON o1.receiving_number = cart3.receiving_number
INNER JOIN dbo.main AS m3
ON cart3.ProductID = m3.ProductID
LEFT OUTER JOIN (SELECT TOP (100) PERCENT
ProductID,
AVG(NumberPerBox) AS NumberPerBox
FROM dbo.InventoryTemp
GROUP BY ProductID
ORDER BY ProductID) AS AvgInventory
ON m3.ProductID = AvgInventory.ProductID
WHERE (o1.OrderTimeStamp BETWEEN DATEADD(DAY, -365, GETDATE()) AND GETDATE() + 1)
GROUP BY cart3.ProductID,
m3.TotalInStock,
AvgInventory.NumberPerBox
HAVING (NOT (cart3.ProductID IS NULL))) AS SalesPastYear /* ***** Table C ***** */
ON BaseInventory.ProductID = SalesPastYear.ProductID
LEFT OUTER JOIN (SELECT
ProductID,
CASE
WHEN ((Auth.Company = '') OR
(Auth.Company IS NULL)) THEN LTRIM(ISNULL(Auth.FirstName, '') + ' ' + ISNULL(Auth.LastName, ''))
ELSE Auth.Company
END AS PrimaryAuthor
FROM dbo.Author AS Auth
INNER JOIN (SELECT
AUTHLINK.AuthorID,
AUTHLINK.Priority,
AUTHLINK.ProductID
FROM (SELECT TOP (100) PERCENT
ProductID,
MIN(ISNULL(Priority, 0)) AS Priority
FROM dbo.AuthorLink
GROUP BY ProductID
ORDER BY ProductID) AS AuthMinPrty
INNER JOIN dbo.AuthorLink AS AUTHLINK
ON (AuthMinPrty.ProductID = AUTHLINK.ProductID)
AND (ISNULL(AuthMinPrty.Priority, 0) = ISNULL(AUTHLINK.Priority, 0))) AS PrimaryAuthor
ON Auth.AuthorID = PrimaryAuthor.AuthorID) AS vwAuthor /* Table Author */
ON BaseInventory.ProductID = vwAuthor.ProductID
ORDER BY BaseInventory.ReferenceNumber
END
ELSE
BEGIN
SELECT TOP (100) PERCENT
BaseInventory.ProductID,
BaseInventory.ReferenceNumber,
BaseInventory.Title,
BaseInventory.StoredBy,
BaseInventory.TotalInStock,
BaseInventory.StockAtEDC,
BaseInventory.Cost,
BaseInventory.RetailPrice,
BaseInventory.PubDate,
BaseInventory.DepartmentCode,
vwAuthor.PrimaryAuthor,
ISNULL(Sales30Days.QuantitySold, 0) AS Last30Days,
ISNULL(SalesPastYear.QuantitySold, 0) AS Last365Days,
ISNULL(CustomRangeSales.QuantitySold, 0) AS CustomRange,
ISNULL(SalesPastYear.ZeroStock, '12/31/9999') AS ZeroStock,
ISNULL(SalesPastYear.ZeroStockFullDate, CONVERT(datetime, '12/31/9999', 102)) AS ZeroStockFullDate,
BaseInventory.FullPubDate,
BaseInventory.ProgramArea,
BaseInventory.Spaces,
BaseInventory.DigitalFileExists,
BaseInventory.DownloadCount,
BaseInventory.DownloadCountCustom,
ISNULL(SalesPastYear.SpacesSold, 0.000) AS SpacesSold,
BaseInventory.DateReceived,
BaseInventory.ProductWeight,
BaseInventory.NumberOfPages,
BaseInventory.DigitalFileCount,
BaseInventory.ActiveDigitalFileCount
FROM (SELECT TOP (100) PERCENT
m.ProductWeight,
m.ProductID,
m.reference_number AS ReferenceNumber,
m.title AS Title,
MAX(sb.StoredBy) AS StoredBy,
m.TotalInStock,
m.edc_copies AS StockAtEDC,
m.pub_cost AS Cost,
m.RetailPrice,
m.pub_date AS PubDate,
d.department_code AS DepartmentCode,
m.department_id AS DepartmentId,
dg.department_group AS ProgramArea,
m.FullPubDate,
ISNULL(Boxes.Spaces, CASE
WHEN sb.StoredBy = 'WWW' THEN 0
ELSE 1
END) AS Spaces,
CASE
WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
ELSE 'Active'
END AS DigitalFileExists,
ISNULL(DLs.DownloadCount, 0) AS DownloadCount,
ISNULL(DLsCustom.DownloadCountCustom, 0) AS DownloadCountCustom,
MAX(history.DateReceived) AS DateReceived,
ISNULL(m.number_of_pages, 'N/A') AS NumberOfPages,
(SELECT
COUNT(*)
FROM tblDigitalMedia tD
WHERE tD.ProductID = m.ProductID)
AS DigitalFileCount,
(SELECT
COUNT(*)
FROM tblDigitalMedia tD
WHERE tD.ProductID = m.ProductID
AND tD.Active = 1)
AS ActiveDigitalFileCount
FROM dbo.main m
INNER JOIN dbo.departments d
ON m.department_id = d.department_id
INNER JOIN dbo.department_groups dg
ON d.department_group_id = dg.department_group_id
/* InventoryTemp should be called InventoryByLocation */
LEFT OUTER JOIN (SELECT
m1.ProductID,
SUM(ISNULL(it.NumberOfBoxes, 0)) + 1 AS Spaces
FROM dbo.main AS m1
INNER JOIN dbo.InventoryTemp AS it
ON m1.ProductID = it.ProductID
GROUP BY m1.ProductID) AS Boxes
ON m.ProductID = Boxes.ProductID
/* JobsTemp should be called JobHistory */
LEFT OUTER JOIN dbo.JobsTemp history
ON m.ProductID = history.ProductID
LEFT OUTER JOIN (
/* If dates are null then fetch for previous year. */
SELECT TOP (100) PERCENT
dm1.ProductID,
COUNT(dl1.TimeStamp) AS DownloadCountCustom
FROM dbo.tblDigitalMedia AS dm1
LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl1
ON dm1.FileID = dl1.FileID
WHERE ((@CustomDateStart IS NOT NULL)
AND (dl1.TimeStamp >= @CustomDateStart)
AND (dl1.TimeStamp <= @CustomDateEnd))
OR ((@CustomDateStart IS NULL)
AND (dl1.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()))
GROUP BY dm1.ProductID) AS DLsCustom
ON m.ProductID = DLsCustom.ProductID
LEFT OUTER JOIN (
/* If dates are null then fetch for previous year. */
SELECT TOP (100) PERCENT
dm2.ProductID,
COUNT(dl2.TimeStamp) AS DownloadCount
FROM dbo.tblDigitalMedia AS dm2
LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl2
ON dm2.FileID = dl2.FileID
WHERE dl2.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()
GROUP BY dm2.ProductID) AS DLs
ON m.ProductID = DLs.ProductID
LEFT OUTER JOIN dbo.tblDigitalMedia AS dm
ON m.ProductID = dm.ProductID
/* InventoryTemp should be called InventoryByLocation */
LEFT OUTER JOIN dbo.InventoryTemp it2
ON m.ProductID = it2.ProductID
INNER JOIN dbo.StoredBy sb
ON m.StoredByID = sb.StoredByID
WHERE (m.edc_isle LIKE N'Aisle' + N'%'
OR m.edc_isle = N'Digital Media'
OR m.edc_isle = N'Duplicator')
AND (dg.department_group_id = @ProgramAreaID)
GROUP BY m.ProductWeight,
m.ProductID,
m.reference_number,
m.title,
m.TotalInStock,
dg.department_group_id,
m.pub_date,
m.department_id,
d.department_code,
m.pub_cost,
m.RetailPrice,
m.FullPubDate,
m.edc_isle,
sb.StoredBy,
dg.department_group,
CASE
WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
ELSE 'Active'
END,
ISNULL(DLs.DownloadCount, 0),
ISNULL(DLsCustom.DownloadCountCustom, 0),
ISNULL(Boxes.Spaces, CASE
WHEN sb.StoredBy = 'WWW' THEN 0
ELSE 1
END),
m.edc_copies,
ISNULL(m.number_of_pages, 'N/A')
HAVING (NOT (MAX(sb.StoredBy) = @ShowNLA))
ORDER BY m.FullPubDate) AS BaseInventory /* ***** Table A ***** */
LEFT OUTER JOIN (SELECT
dbo.shopping_cart.ProductID,
SUM(dbo.shopping_cart.quantity) AS QuantitySold
FROM dbo.orders
INNER JOIN dbo.shopping_cart
ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
WHERE dbo.orders.OrderTimeStamp >= DATEADD(DAY, -30, GETDATE())
AND dbo.orders.OrderTimeStamp < GETDATE()
AND dbo.shopping_cart.ProductID IS NOT NULL
GROUP BY dbo.shopping_cart.ProductID) AS Sales30Days /* ***** Table B ***** */
ON BaseInventory.ProductID = Sales30Days.ProductID
LEFT OUTER JOIN (SELECT
dbo.shopping_cart.ProductID,
SUM(dbo.shopping_cart.quantity) AS QuantitySold
FROM dbo.orders
INNER JOIN dbo.shopping_cart
ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
WHERE @CustomDateStart IS NOT NULL
AND dbo.orders.OrderTimeStamp >= @CustomDateStart
AND dbo.orders.OrderTimeStamp <= @CustomDateEnd
AND dbo.shopping_cart.ProductID IS NOT NULL
GROUP BY dbo.shopping_cart.ProductID) AS CustomRangeSales /* ***** CustomRangeSales ***** */
ON BaseInventory.ProductID = CustomRangeSales.ProductID
LEFT OUTER JOIN (SELECT TOP (100) PERCENT
cart3.ProductID,
SUM(cart3.quantity) AS QuantitySold,
m3.TotalInStock,
CASE
WHEN
CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
ELSE CONVERT(varchar, DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE()), 101)
END AS ZeroStock,
CASE
WHEN
CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
ELSE DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE())
END AS ZeroStockFullDate,
CONVERT(decimal(18, 3), SUM(cart3.quantity) / ISNULL(CONVERT(decimal(18, 2), AvgInventory.NumberPerBox), SUM(cart3.quantity) + m3.TotalInStock)) AS SpacesSold,
AvgInventory.NumberPerBox
FROM dbo.orders AS o1
INNER JOIN dbo.shopping_cart AS cart3
ON o1.receiving_number = cart3.receiving_number
INNER JOIN dbo.main AS m3
ON cart3.ProductID = m3.ProductID
LEFT OUTER JOIN (SELECT TOP (100) PERCENT
ProductID,
AVG(NumberPerBox) AS NumberPerBox
FROM dbo.InventoryTemp
GROUP BY ProductID
ORDER BY ProductID) AS AvgInventory
ON m3.ProductID = AvgInventory.ProductID
WHERE (o1.OrderTimeStamp BETWEEN DATEADD(DAY, -365, GETDATE()) AND GETDATE() + 1)
GROUP BY cart3.ProductID,
m3.TotalInStock,
AvgInventory.NumberPerBox
HAVING (NOT (cart3.ProductID IS NULL))) AS SalesPastYear /* ***** Table C ***** */
ON BaseInventory.ProductID = SalesPastYear.ProductID
LEFT OUTER JOIN (SELECT
ProductID,
CASE
WHEN ((Auth.Company = '') OR
(Auth.Company IS NULL)) THEN LTRIM(ISNULL(Auth.FirstName, '') + ' ' + ISNULL(Auth.LastName, ''))
ELSE Auth.Company
END AS PrimaryAuthor
FROM dbo.Author AS Auth
INNER JOIN (SELECT
AUTHLINK.AuthorID,
AUTHLINK.Priority,
AUTHLINK.ProductID
FROM (SELECT TOP (100) PERCENT
ProductID,
MIN(ISNULL(Priority, 0)) AS Priority
FROM dbo.AuthorLink
GROUP BY ProductID
ORDER BY ProductID) AS AuthMinPrty
INNER JOIN dbo.AuthorLink AS AUTHLINK
ON (AuthMinPrty.ProductID = AUTHLINK.ProductID)
AND (ISNULL(AuthMinPrty.Priority, 0) = ISNULL(AUTHLINK.Priority, 0))) AS PrimaryAuthor
ON Auth.AuthorID = PrimaryAuthor.AuthorID) AS vwAuthor /* Table Author */
ON BaseInventory.ProductID = vwAuthor.ProductID
ORDER BY BaseInventory.ReferenceNumber
END
END /* stored procedure */
GO
忽略所有告诉你以其他方式做这件事的人，看看你是否可以调整你所拥有的。 声称这是 4 个查询的人是不正确的。 LINQ 将组合成单个 SQL 语句。您需要做的是尝试找出：
- 生成的 SQL 是什么样子的(尝试使用 SQL Server 探查器捕获它(
- 为什么它效率低下(再次使用 SQL Server 事件探查器，或粘贴到 SSMS 中并显示查询计划(
完成此操作后，可能会更清楚需要对 LINQ 执行哪些操作才能使其执行。
为了简化一次需要查看的数量，您可以尝试一件事：单独执行每个"内部"查询，看看它们中的任何一个本身看起来是否很糟糕。