c# - Linq 实体分组依据(外部应用) "oracle 11.2.0.3.0 does not support apply"

Question

我有下面的代码示例，它查询产品列表。

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

这完全符合预期，并在使用内存集合时返回我想要的 4 行，但在针对 Oracle 数据库运行时:

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

这会引发一个错误，提示我应该使用 Oracle 数据库不支持的 FirstOrDefault。抛出错误 oracle 11.2.0.3.0 不支持应用。 Googleing 在 CodePlex 上揭示了这一点:https://entityframework.codeplex.com/workitem/910 .

使用以下二进制文件时会发生这种情况:

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

数据库是 Oracle 11.2.0.3.0 数据库。

生成的 sql 使用 11.2.0.3.0 版本的 Oracle 不支持的 OUTER APPLY(见下图)，那么为什么 EF/Oracle.ManagedDataAccess 试图使用它？有没有办法告诉 EF 不要使用 APPLY 关键字？

下面的页面说在 Oracle 12c 第 1 版中添加了 APPLY 支持，但我不能更新我所有的数据库只是为了使 GROUP BY 工作。 http://www.oracle.com/technetwork/database/windows/newfeatures-084113.html

这似乎是一个已知问题 ( 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.

在我求助于创建 View 之前(我必须在多个数据库上创建 View )，任何人都可以看到另一个解决方案吗？

对于任何感兴趣的人，针对此数据库版本执行我想要的操作的 SQL 如下所示:

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

代码最终将在传递给 Web API 中的 OData Controller 的服务类中。下面的例子使用的是demo数据，真实数据库有70万条记录，所以我想避免执行查询并让 OData 处理页面限制和进一步过滤。

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

因为您可以自己编写查询。也许您可以用它创建一个存储过程并从 Entity Framework 调用 SP。