linq - 向我推荐合适的 LINQ 提供程序(SQL 服务器、复杂查询)

Question

我一直在使用 LINQ to SQL & to entity 一段时间，总体上对它们非常满意。
但是，我知道它们的局限性，尤其是其中一个对我来说已成为一个大问题。
当您以以下形式进行复杂的嵌套查询时

MyContext.SomeTable
.Select(item=>new{
    item.SomeProperty1,
    item.SomeProperty2,
    item.NavigationProperty1
        .Select(nav1=> new {// retrieve some properties}), // This triggers a single query as long as don't have more than one subquery
    item.NavigationProperty2
        .Select(nav2=> new {// retrieve some properties}) // This triggers one query PER ROW in the original query
});

我测试过的提供程序是 LINQ TO SQL/LINQ TO 实体(更糟糕的是，取消了性能更差的 LINQConnect 并在第一个导航属性上每行生成 1 个)

我现在得到的是生成的(伪代码):

select t1.a,t1.b,t2.c,t2.d from mytable as t1
join navproperty1table as t2

和 100 万个(如果第一组中有 100 万个结果)这样的查询: select t3.e,t3.f from navproperty2table as t3 where id = X (X 将 X 查询更改为第一个查询返回的下一个元素)

我想要的是:

select t1.a,t1.b,t2.c,t2.d,t3.e,t3.f from mytable as t1 
join navproperty1table as t2
join navproperty2table as t3

现在当然，如果原始表中有 3 行，那就不是问题，但是我的表中有成千上万到数百万行“并且”我需要在单个选择中进行更复杂的查询(我想一次得到一个复杂的图表)。考虑 20 + 3-6 层嵌套访问额外的 2-5 个表每个表。

我的 SQL 服务器可以完美地应付它，我也不关心带宽，它在一个由千兆连接链接的实例上，我无法以延迟的方式获取该数据，我实际上立即“使用”了所有这些这不仅仅是懒惰。现在出于性能原因，我不得不将查询拆分为许多小查询，并在 LINQ 上手动将它们连接到对象大小，这为维护它的人提供了一些非常讨厌的代码，但这是我拥有的唯一实际解决方案，因此总体而言包括所有小查询和最终加入，我在一个完全无法维护的单一方法中拥有超过 600 行不可分割的代码。

在我去评估它们之前，今天是否真的有“任何”LINQ 提供程序准备好以这种心态进行评估，还是我最好编写自己的代码并将其商业化？ (我很惊讶它们实际上并不是全部都这样工作，我看不到一个实例，你会用 foreach 案例和我尝试过的那些声称摆脱 n +1 与 loadwith，不要摆脱它，因为他们仍然进行 n+1 次查询，但只需在一次调用中批量处理，1 次往返和 n+1 次查询在 1 是 10 000 然后是 10 000 000 时不令人满意然后是 10 000 000 000)

(请注意，我正在推测究竟是什么触发了这个，但这不是问题，无论是什么“确切地”触发了这个，我肯定会在我当前的上下文中击中它)

PS:请注意，我在 Windows Server 2008 或更高版本和 SQL Server 2008 或更高版本上运行 .NET 4.0 完整配置文件，不支持其他任何东西的提供程序就可以了，我对迁移、可移植性的要求为零，较低的 .net 版本，较低的 sql server 支持等。如果需要，可以选择迁移到更新的版本。我也没有任何建模或高级功能的先决条件，数据库已经存在，我只想查询表，所以没有建模/ View /DML/存储过程/函数支持的东西很好，我唯一的要求在复杂查询和对象图上生成合理的 SQL

编辑:为了澄清这里是每个人都可以获得的数据库问题的实际示例，adventureworks

为每个联系人查询员工

Contacts
.Select(cont=>new 
{
    cont.EmailAddress,
    cont.EmailPromotion,
    Employees = cont.Employees
        .Select(emp=>new
        {
            emp.Gender,
            emp.HireDate
        }).ToList()
}).ToList()

生成

SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], (
SELECT COUNT(*)
FROM [HumanResources].[Employee] AS [t2]
WHERE [t2].[ContactID] = [t0].[ContactID]
) AS [value]

发件人 [人].[联系人] AS [t0]
LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]
ORDER BY [t0].[ContactID], [t1].[EmployeeID]

现在只查询每个联系人的供应商
联系人
.Select(cont=>new
{
续电子邮件地址，
续电子邮件促销，
供应商 = cont.VendorContacts.Select(vend=>new
{
vend.ContactTypeID,
修改日期
}).ToList()
}).ToList()

还可以:

SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[ContactTypeID], [t1].[ModifiedDate], (
SELECT COUNT(*)
FROM [Purchasing].[VendorContact] AS [t2]
WHERE [t2].[ContactID] = [t0].[ContactID]
) AS [value]

发件人 [人].[联系人] AS [t0]
LEFT OUTER JOIN [采购].[供应商联系方式] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]
ORDER BY [t0].[ContactID], [t1].[VendorID]

现在同时查询(触发 X 行查询)

Contacts
.Select(cont=>new 
{
    cont.EmailAddress,
    cont.EmailPromotion,
    Employees = cont.Employees
        .Select(emp=>new
        {
            emp.Gender,
            emp.HireDate
        }).ToList(),
    Vendors = cont.VendorContacts.Select(vend=>new
    {
        vend.ContactTypeID,
        vend.ModifiedDate
    }).ToList()
}).ToList()

生成丑陋和缓慢的(不是出于显而易见的原因将其全部粘贴，但您明白了):

SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], (
SELECT COUNT(*)
FROM [HumanResources].[Employee] AS [t2]
WHERE [t2].[ContactID] = [t0].[ContactID]
) AS [value], [t0].[ContactID]
FROM [Person].[Contact] AS [t0]
LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]
ORDER BY [t0].[ContactID], [t1].[EmployeeID]
GO

-- Region Parameters
DECLARE @x1 Int = 1
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

 -- Region Parameters
DECLARE @x1 Int = 2
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 3
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 4
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 5
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 6
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 7
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 8
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 9
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

-- Region Parameters
DECLARE @x1 Int = 10
-- EndRegion
SELECT [t0].[ContactTypeID], [t0].[ModifiedDate]
FROM [Purchasing].[VendorContact] AS [t0]
WHERE [t0].[ContactID] = @x1
GO

我期望/希望看到的生成:

SELECT [t0].[EmailAddress], [t0].[EmailPromotion], [t1].[Gender], [t1].[HireDate], [t2].[ContactTypeID], [t2].[ModifiedDate] ,[t0].[ContactID]
FROM [Person].[Contact] AS [t0]
LEFT OUTER JOIN [HumanResources].[Employee] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]
LEFT OUTER JOIN [Purchasing].[VendorContact] AS [t2] ON [t2].[ContactID] = [t0].[ContactID]
GO

Answer 1

一种解决方法是创建一个 view
从你的定义

select t1.a,t1.b,t2.c,t2.d,t3.e,t3.f from mytable as t1 join navproperty1table as t2 join navproperty2table as t3

并使用 linq-2-sql 查询该 View 。

不确定我是否完全理解你的查询，但你可能会这样做

from x in MyContext.Sometable
Select new { x.a, x.b, x.t2.c, x.t2.d, x.t3.f } 

等等..我现在无法测试它，但我很确定你会创建你想要的选择(并且只有一个)。