时间戳

首页 > 解决方案 > 使用导航属性的 T-SQL 到 LINQ 到 SQL

c# - 使用导航属性的 T-SQL 到 LINQ 到 SQL

问题描述

我似乎无法想出正确的相应 LINQ to SQL 语句来生成以下 T-SQL。本质上，我试图只返回一个客户地址的付款信息...... AR 地址，如果存在，然后是主地址，如果存在，然后是任何地址。

SELECT < payment and address columns >
FROM   Payment AS p
       INNER JOIN Customer AS c ON c.CustomerID = p.CustomerID
       OUTER APPLY (
                       SELECT   TOP 1 < address columns >
                       FROM     Address AS a
                       WHERE    a.person_id = c.PersonID
                       ORDER BY CASE WHEN a.BusinessType = 'AR' THEN 0
                                     ELSE 1
                                END
                              , a.IsPrimary DESC
                                END
                   ) AS pa
WHERE  p.Posted = 1

我们使用存储库模式来访问数据库，所以在支付存储库的方法中，我尝试过：

var q = GetAll()
            .Where(p => p.Posted == true)
            .SelectMany(p => p.Customer
                              .Address
                              .OrderBy(a => a.BusinessType != "AR")
                              .ThenBy(a => a.Primary != true)
                              .Take(1)
                              .DefaultIfEmpty()
            .Select(a => new
            {
                < only the columns I need from p and a >
            });

但是当我执行时.ToList()，它会在客户没有设置地址的记录上抛出NullReferenceException（对象引用未设置为对象的实例）。所以，我尝试了：

var q1 = GetAll().Where(p => p.Posted == true);

var q2 = q11.SelectMany(p => p.Customer
                              .Address
                              .OrderBy(a => a.BusinessType != "AR")
                              .ThenBy(a => a.Primary != true));

var q3 = q1.SelectMany(p => q2.Where(a => a.PersonID == p.Customer.PersonID)
                              .Take(1)
                              .DefaultIfEmpty()
           .Select(a => new
           {
                < only the columns I need from p and a >
           });

这将返回正确的结果，但它生成的 T-SQL 将整个 T-SQL 从上面放入外部应用程序，然后再次连接到Payment和Customer。这似乎有点低效，我想知道是否可以提高效率，因为上面的 T-SQL 对于我正在使用的测试用例在 6 毫秒内返回。

附加信息：
问：我认为这里的问题是GetAll()返回IEnumerable，而不是IQueryable......看看这个GetAll()方法会有所帮助。- Gert Arnold
A：实际上，GetAll()当一直追溯时，返回Table<TEntity> System.Data.Linq.GetTable<TEntity>()并Table<TEntity>执行IQueryable。

但是，如果我没记错的话，DefaultIfEmpty()确实 return IEnumerable<Address>，这就是引发异常的原因，正如我在第一个 L2S 代码部分中提到的那样。

解决方案更新

好的，我知道我可以退回到直接加入表格并放弃使用导航属性，在这种情况下，我现在知道应该这样做。现在一切都说得通了。我刚刚习惯了更喜欢使用导航属性，但在这里，最好直接加入表格。

第二个 L2S 代码部分生成的 T-SQL 效率如此之低的原因是，为了访问 Address 表，它需要包含 Payment/Customer 数据。

当我直接加入表时，生成的 T-SQL 虽然不理想，但更接近所需的脚本代码部分。那是因为它不需要包含付款/客户数据。就在那时，“嗯，呃”的灯泡亮了。

感谢所有在这条发现之路上提供帮助的人！

标签： c#linqlinq-to-sqlleft-joinouter-apply

解决方案

当尝试一个类似的查询时，结果证明这个DefaultIfEpty()调用会破坏 LINQ-to-SQL。异常的堆栈跟踪表明事情出错了System.Data.Linq.SqlClient.SqlBinder.Visitor.IsOuterDependent，即在 SQL 查询构建期间。

与您的结论相反，不建议放弃使用导航属性并返回显式连接。问题是：如何在不影响 LINQ-to-SQL 的情况下使用 LINQ 的最佳部分（包括导航属性）。顺便说一句，这对于每个支持 LINQ 的 ORM 都是正确的。

在这种特殊情况下，我将切换到主查询的查询语法并使用关键字let。就像是：

from p in context.Payments
let address = p.Customer
    .Addresses
    .OrderBy(a => a.BusinessType != "AR")
    .ThenBy(a => a.Primary != true)
    .FirstOrDefault()
select new 
{
    p.PropertyX,
    address.PropertyY
    ...
}

这将被翻译成一个 SQL 语句，它避免了 LINQ-to-SQL 与DefaultIfEmpty.

推荐阅读