How to use EXIST and NOT EXIST clause using lambda expression

Question

I am trying to filter out orders that do not have an event type Delivered and that works fine.

List<Order> matchingOrders = xyzContext.Orders.Where(
                    o => o.FacilityId == facility.MasterFacilityId
                   && rxNumberList.Contains(o.RxNumber)
                   && o.OrderEvents.All(oe => oe.EventType.Code != EventTypeCode.Delivered)).ToList();

After that I am trying to filter Orders which has Event type "Ordered" and does not have event type "Delivered" and I am not getting expected result.

List<Order> matchingOrders = xyzContext.Orders.Where(
                    o => o.FacilityId == facility.MasterFacilityId
                   && rxNumberList.Contains(o.RxNumber)
                   && o.OrderEvents.All(oe => oe.EventType.Code != EventTypeCode.Delivered && oe.EventType.Code == EventTypeCode.Ordered)).ToList();

Answer 1

try this out :

List<Order> matchingOrders = xyzContext.Orders.Where(
                o => o.FacilityId == facility.MasterFacilityId
               && rxNumberList.Contains(o.RxNumber)
               && o.OrderEvents.All(oe => oe.EventType.Code != EventTypeCode.Delivered)
               &&  o.OrderEvents.Any(oe.EventType.Code == EventTypeCode.Ordered)).ToList();