c# - Linq Query Join to Subquery with In 子句

Question

我有一个来自旧应用程序的查询，我正在尝试将其转换为 Entity Framework Core 应用程序。我正在使用的关系是简单的一对多关系，其中一个订单可以有多个 OrderEvents。旧查询看起来像这样:

select Order.Id, mostRecent.*
from Order
left join OrderEvent mostRecent 
on mostRecent.Id in (select top(1) Id 
                     from OrderEvent
                     where OrderEvent.OrdId = Order.Id 
                           and OrderEvent.PostDateTime is not null
                     order by OrderEvent.PostDateTime desc)
where Order.SomeColumn = 'some value'

我正在努力弄清楚如何在 LINQ 中编写此查询。在使用 join 时，似乎除了 equals 之外不能使用任何东西，所以我首先尝试了类似的东西:

var test = (from order in _context.Ord.AsNoTracking()
            join mostRecentQuery in _context.OrdEvt.AsNoTracking()
            on (from orderEvent in _context.OrdEvt.AsNoTracking()
                where orderEvent.PostDateTime != null && orderEvent.OrdId == order.Id
                orderby orderEvent.PostDateTime descending
                select orderEvent.Id).FirstOrDefault()
            equals mostRecentQuery.Id
            into mostRecentResults
            from mostRecent in mostRecentResults.DefaultIfEmpty()
            select new
            {
               OrderId = order.Id,
               OrderEvent = mostRecent
            }).ToList();    

无论这吐出什么 sql，它看起来都太慢了，我什至无法运行它连接到 Sql Server。但是，我可以在使用 Sqlite 时运行此查询，它会生成以下 sql:

SELECT 'big list of fields....'
  FROM "Ord" AS "order"
  LEFT JOIN "OrdEvt" AS "mostRecentQuery" ON COALESCE((
      SELECT "orderEvent0"."Id"
      FROM "OrdEvt" AS "orderEvent0"
      WHERE "orderEvent0"."PostDateTime" IS NOT NULL AND ("orderEvent0"."OrdId" = "order"."Id")
      ORDER BY "orderEvent0"."PostDateTime" DESC
      LIMIT 1
  ), X'00000000000000000000000000000000') = "mostRecentQuery"."Id"

这很接近我的目标，但我不确定为什么要使用 COALESCE 函数。

是否可以在 Linq 查询语法中表示我尝试转换的查询？

Answer 1

我不确定你的问题是什么，因为你遗漏了它无法编译的原因。

但这里有一个简单的例子，一个连接和为一个订单选择一个事件:

public class Order
{
    public int OrderId { get; set; }
    public string OrderName { get; set; }
}

public class OrderEvent
{
    public int OrderId { get; set; }
    public string EventName { get; set; }
    public DateTime Date { get; set; }
}

public static void Main(string[] args) // Here our Exe-Args are delivered to our program..
{

    List<Order> orders = new List<Order>()
    {
        new Order() { OrderId = 1, OrderName = "One" }
    };
    List<OrderEvent> orderEvents = new List<OrderEvent>()
    {
        new OrderEvent() { OrderId = 1, EventName = "EventTwo", Date = DateTime.Now.AddHours(-1) },
        new OrderEvent() { OrderId = 1, EventName = "EventOne", Date = DateTime.Now },
        new OrderEvent() { OrderId = 1, EventName = "EventThree", Date = DateTime.Now.AddHours(-2) },
        new OrderEvent() { OrderId = 2, EventName = "EventX", Date = DateTime.Now },
    };

    var tmp = orders.GroupJoin( // Join something to our orders
            orderEvents, // join all events
            o => o.OrderId, // key of our order
            e => e.OrderId, // foreign-key to order in our event
            (o, es) => // lambda-expression "selector". One order "o" and multiple events "es"
            es.OrderByDescending(e => e.Date) // We order our events
            .Select(s => new // we build a new object
            {
                OrderId = o.OrderId,
                OrderName = o.OrderName,
                RecentEvent = s.EventName
            }).First()); // we choose the object with biggest date (first after orderDesc)

    foreach (var item in tmp)
    {
        Console.WriteLine(item.OrderId + ", " + item.OrderName + ", " + item.RecentEvent);
    }

    tmp = from o in orders
               join e in orderEvents on o.OrderId equals e.OrderId into es
               select new
               {
                   OrderId = o.OrderId,
                   OrderName = o.OrderName,
                   RecentEvent = (from x in es orderby x.Date descending select x.EventName).First()
               };

    foreach (var item in tmp)
    {
        Console.WriteLine(item.OrderId + ", " + item.OrderName + ", " + item.RecentEvent);
    }
}