Linq Query Join to Subquery with In clause

c# entity-framework-core linq sql

Question

I have a query from an old application that I am attempting to convert to an Entity Framework Core application. The relationship I am working with is a simple one to many, where one Order can have many OrderEvents. The old query looks something like this:

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'

I am struggling with figuring out how to write this query in LINQ. It doesnt seem that one can use anything other than equals when doing using join, so I first attempted something like:

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();    

Whatever sql this spit out, it appears to be so slow that I cant even run it connected to Sql Server. I am however able to run this query when using Sqlite and it generates the following 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"

This is close do what I am going for, but Im not sure why the COALESCE function is being used.

Is it possible to represent the query I am trying to convert, in Linq query syntax?

1
2
2/22/2019 2:16:36 PM

Popular Answer

I'm not sure what your problem is, because you left out why it doesn't compile.

Bute here and simple example whith a join and choosing one event for an order:

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);
    }
}
0
2/22/2019 11:25:21 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow