LINQ avoid OUTER APPLY Oracle 11g

Question

When running the LINQ Query below against Oracle 11g instance, it will throw an OUTER APPLY not supported error.

var shipmentDetails = (
    from r in _db.XXF_SHIPMENT_DETAILs 
    where r.SHIP_TO == tradingPartnerId && r.PICKUP_DATE >= pickUpDate 
    select r)
    .GroupBy(x => x.HEADERID)
    .Select(x => x.FirstOrDefault());

"OUTER APPLY is not supported by Oracle Database 11g and lower. Oracle 12c or higher is required to run this LINQ statement correctly. If you need to run this statement with Oracle Database 11g or lower, rewrite it so that it can be converted to SQL, supported by the version of Oracle you use."

Which ORM do you use? EF Core 6 should translate this query to do not use OUTER APPLY. — Svyatoslav Danyliv, Nov 12 '22 at 19:40
using LinqConnect from devArt https://www.devart.com/linqconnect/ — Geovani Martinez, Nov 12 '22 at 21:01

score 0 · Answer 1 · answered Nov 12 '22 at 19:02

The solution is to use simple statements to achieve the results you are after. Referencing the query above, we...

First, get all the shipments. Use the .ToList() to force query execution

var shipmentDetails = (from r in _db.XXF_SHIPMENT_DETAILs where r.SHIP_TO == tradingPartnerId && r.PICKUP_DATE >= pickUpDate select r).ToList();

Now .GroupBy() and .Select() to filter - but this will be done in memory and not at the server level therefore avoiding the unsupported OUTER APPLY

var uniqueShipmentsWithDistinctHeaderIds = shipmentDetails.GroupBy(x => x.HEADERID).Select(x => x.FirstOrDefault());

score 0 · Answer 2 · answered Nov 12 '22 at 19:59

You can use the following query which will get latest record from the group:

var filtered = db.XXF_SHIPMENT_DETAILs 
    .Where(r => r.SHIP_TO == tradingPartnerId && r.PICKUP_DATE >= pickUpDate);

var grouped = fltered
    .GroupBy(r => r.HEADERID)
    .Select(g => new
    {
        HEADERID = g.Key,
        LastId = g.Max(x => x.Id)
    });

var shipmentDetails = 
    from s in filtered
    join g in grouped on s.LastId equals g.Id
    select s;

Still not the best as raw SQL and window functions, but should give much better performance than processing data on the client side.

LINQ avoid OUTER APPLY Oracle 11g

2 Answers2

Linked