使用导航属性将T-SQL转换为LINQ to SQL

Question

使用导航属性将T-SQL转换为LINQ to SQL

3

我似乎无法想出正确的相应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 答：实际上，当追溯到底层时，GetAll() 返回 Table System.Data.Linq.GetTable()，而 Table 确实实现了 IQueryable。

但是，如果我没有弄错的话，DefaultIfEmpty() 确实返回 IEnumerable

，这就是抛出异常的原因，正如我在第一个 L2S 代码部分中提到的那样。

解决方案更新：

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

第二个 L2S 代码部分生成的 T-SQL 如此低效的原因是因为为了进入 Address 表，必须包含 Payment/Customer 数据。

当我直接加入表时，生成的 T-SQL 虽然不理想，但更接近所需的脚本代码部分。这是因为它不需要包含 Payment/Customer 数据。这时“嗯，傻瓜”灯泡亮了。

感谢所有帮助我找到答案的人！

- DeuceyPoo's Dad

1

你为什么要使用SelectMany而不是仅仅使用Select？ - jdweng

1

由于地址表示0:m关系，我认为我需要使用SelectMany来实质上展开该集合，然后根据排序仅返回第一个。这也允许我返回付款和地址的列，而Select只会返回地址信息。如果我错了，请随时指正我。 - DeuceyPoo's Dad

1

看起来你是正确的。我会使用.FirstOrDefault()而不是.Take(1).DefaultIfEmpty。即使只有一个项目，Take也会返回数组对象。所以你会得到SelectMany(p => p.Where(a => a.PersonID == p.Customer.PersonID).FirstOrDefault()).Select(a =>new ......)ToList();。FirstOrDefault给你单身汉，而Take(1)仍然给你一个数组。 - jdweng

我认为这里的问题在于GetAll()返回的是IEnumerable而非IQueryable，因此无法将查询转换为SQL（这不敏感于null引用）。若能看到GetAll()方法的代码便会更有帮助。 - Gert Arnold

1

@GertArnold - 请看上面更新的问题。哈！我不能完全回答那个问题，否则会惹麻烦，但你刚刚被加入到我收集的语录中，我偶尔向老板展示以证明使用EF是有必要的（好像它需要证明一样）。 - DeuceyPoo's Dad

显示剩余3条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gert Arnold · Accepted Answer

当尝试类似的查询时，发现这个 DefaultIfEpty() 调用会使 LINQ-to-SQL 崩溃。异常的堆栈跟踪显示，在 System.Data.Linq.SqlClient.SqlBinder.Visitor.IsOuterDependent 中出现问题，即在 SQL 查询构建期间出现问题。

与您的结论相反，不建议放弃使用导航属性并返回到显式连接。问题是：如何使用 LINQ 的最佳部分（包括 nav 属性），而不影响 LINQ-to-SQL。顺便说一下，这对于每个支持 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 问题。