Linq to Entities Group By (OUTER APPLY) "oracle 11.2.0.3.0 does not support apply"

Question

I have the code sample below which queries a list of Products.

 var productResults = Products.Where((p) => refFilterSequence.Contains(p.Ref))
                .GroupBy(g => g.Code, (key, g) => g.OrderBy(whp => whp.Ref).First()).ToList();

This works exactly as expected and returns the 4 rows I want when using an in memory collection, but when running against the Oracle database:

.GroupBy(g => g.Code, (key, g) => g.OrderBy(whp => whp.Ref).First())

This throws an error saying I should use FirstOrDefault which is not supported in an Oracle database. The error oracle 11.2.0.3.0 does not support apply gets thrown. Googleing reveals this on CodePlex: https://entityframework.codeplex.com/workitem/910.

This occurs when using the following binaries:

• EntityFramework 6.0.0.0
• Oracle.ManagedDataAccess 4.121.2.0
• Oracle.ManagedDataAccess.EntityFramework 6.121.2.0
• .Net Framework 4.5.1

The database is an Oracle 11.2.0.3.0 Database.

The sql generated uses OUTER APPLY (see image below) which is not supported by the 11.2.0.3.0 version of Oracle so why is EF/Oracle.ManagedDataAccess trying to use it? Is there a way to tell EF not to use the APPLY keyword?

The page below says that APPLY support was added in Oracle 12c Release 1, but I can't update all my databases just to make a GROUP BY work. http://www.oracle.com/technetwork/database/windows/newfeatures-084113.html

It appears that this is a known issue (Known Issues in SqlClient for Entity Framework):

The following are some typical scenarios that might lead to the presence of CROSS APPLY and/or OUTER APPLY operators in the output query:

• LINQ queries that use grouping methods that accept an element selector.

Before I resort to creating a view (I would have to create the view on several databases), can anyone see another solution?

For anyone interested, the SQL that would do what I want against this database version would look something like the following:

select *
from ( select  RANK() OVER (PARTITION BY sm.product ORDER BY refs.map)      ranking, sm.*
            from    schema.table sm,
                    (
                        select 'R9' ref, 0 map from dual
                        union all
                        select 'R1' ref, 1 map from dual
                        union all
                        select 'R6' ref, 2 map from dual
                    ) refs
            where   sm.ref= refs.ref                               
          ) stock
where ranking  = 1

The code will eventually be in a service class passed to and OData controller in Web API. The example below uses demo data, the real database has 700,000 records, so I would like to avoid executing the query and let OData handle page limits and further filtering.

using System;
using System.Collections.Generic;
using System.Linq;

namespace DemoApp
{
    class Program
    {
        public class Product
        {
            public string Ref { get; set; }
            public string Code { get; set; }
            public int Quantity { get; set; }
        }

        //demo data
        static readonly List<Product> Products = new List<Product>
        {
            new Product { Ref = "B2", Code = "ITEM1", Quantity = 1},
            new Product { Ref = "R1", Code = "ITEM1", Quantity = 2},
            new Product { Ref = "R9", Code = "ITEM1", Quantity = 3},
            new Product { Ref = "R9", Code = "ITEM2", Quantity = 4},
            new Product { Ref = "R6", Code = "ITEM2", Quantity = 5},
            new Product { Ref = "B2", Code = "ITEM3", Quantity = 6},
            new Product { Ref = "R1", Code = "ITEM3", Quantity = 7},
            new Product { Ref = "R9", Code = "ITEM3", Quantity = 8},
            new Product { Ref = "B2", Code = "ITEM4", Quantity = 9},
            new Product { Ref = "X3", Code = "ITEM4", Quantity = 10},
            new Product { Ref = "B8", Code = "ITEM5", Quantity = 10},
            new Product { Ref = "R6", Code = "ITEM5", Quantity = 12},
            new Product { Ref = "M2", Code = "ITEM5", Quantity = 13},
            new Product { Ref = "R1", Code = "ITEM5", Quantity = 14},
        };

    static void Main(string[] args)
    {
        // this array is of variable length, and will not always contain 3 items.
        var refFilterSequence = new List<string> {"R9", "R1", "R6"};

        var results = GetProductsForODataProcessing(refFilterSequence);

        // some further filtering may occur after the queryable is returned.
        // the actual implmentation is an OData Web API, so filters, expansions etc could be added.

        //results = results.Where(p => p.Quantity > 2);

        results.ToList().ForEach(p => Console.WriteLine("RANK:{0}\tREF:{1}\tCode:{2}\tQty:{3}", "?", p.Ref, p.Code, p.Quantity));
        Console.ReadLine();
    }

    static IQueryable<Product> GetProductsForODataProcessing(List<string> filterSequence )
    {
        var productResults = Products.Where((p) => filterSequence.Contains(p.Ref))
            .GroupBy(g => g.Code, (key, g) => g.OrderBy(whp => whp.Ref).First()).AsQueryable();

        return productResults;               
    }
}


// Example Output
// .......................
// REF:R1 Code:ITEM1 Qty:2
// REF:R6 Code:ITEM2 Qty:3
// REF:R1 Code:ITEM3 Qty:7
// REF:R1 Code:ITEM5 Qty:14

Answer 1

Since you could write the query yourself. Maybe you can create a stored procedure with it and call the SP from Entity Framework.