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

Question

我似乎无法想出正确对应的 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()方法。 - 格特·阿诺德
答:实际上，GetAll() , 当一直追溯时，返回 Table<TEntity> System.Data.Linq.GetTable<TEntity>()和 Table<TEntity>执行IQueryable .

然而，DefaultIfEmpty()返回 IEnumerable<Address> ，这就是抛出异常的原因，如果我没记错的话，正如我在第一个 L2S 代码部分中提到的那样。

解决方案更新

好吧，我知道我可以退回到简单地直接加入表格并放弃使用导航属性，在这种情况下，我现在知道应该如何完成。现在一切都说得通了。我只是习惯于使用导航属性，但在这里，最好直接连接表格。

第二个 L2S 代码部分生成的 T-SQL 之所以如此低效，是因为为了获取地址表，它需要包含付款/客户数据。

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

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

Answer 1

当尝试类似的查询时，结果证明此 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 的明显问题。