Linq外连接使用不等式? [英] Linq outer join using inequality?

问题描述

在SQL中，我会说:

select a.*
from TableA a 
left join TableB b on a.Type = b.Type and a.SomeDate < b.AnotherDate
where b.ID is null

这将选择TableA中的所有记录，而在TableB中没有相同 Type 和更高日期的记录.

This would select all records in TableA where no record exists in TableB of the same Type and later date.

在Linq中，您如何做到这一点?

In Linq, how do you do this?

from a in TableA
join b in TableB on a.Type equals b.Type into j // what about the comparator?
from x in j.DefaultIfEmpty()
where x == null
select a;

谢谢！

已经提出了一些好的答案，所有这些答案都可以解决此问题中表达的特定需求，但基本上都是解决方法.它们都以一种或另一种方式转换为嵌套的存在"查询，而问题中的SQL是一个没有任何嵌套的简洁查询.这里给出的案例只是一般原则的一个示例；我真正想看到的是一个Linq表达式，它将转换为(大致)上述SQL查询的语法.

A few good answers have been proposed, all of which address the specific need expressed in this question, but they're all basically workarounds. They all translate to a nested "exists" queries in one way or another, whereas the SQL in the question is one neat query without any nesting. The case given here is just an example of a general principle; what I'd really like to see is a Linq expression that will translate to (roughly) the syntax of the above SQL query.

推荐答案

var results = TableA.Where(a => 
                 !TableB.Any(b => a.Type == b.Type && a.Date < b.Date))

如果您希望linq查询与您的SQL完全相同，则可以编写:

If you want the linq query to be exactly as your SQL you can write:

var result = from a in TableA
             from b in TableB.Where(b => a.Type = b.Type && a.SomeDate < b.AnotherDate).DefaultIfEmpty()
             where b == null
             select a;

但是我要说第一种解决方案更好，因为 where b == null 会导致查询计划中的过滤操作.

But I would say that the first solution is better as the where b == null would result in a filter operation in the queryplan.

这篇关于Linq外连接使用不等式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！