Linq to Entities Group By(外部申请)"oracle 11.2.0.3.0不支持适用". [英] Linq to Entities Group By (OUTER APPLY) "oracle 11.2.0.3.0 does not support apply"

问题描述

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

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

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

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

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

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

该数据库是Oracle 11.2.0.3.0数据库.

The database is an Oracle 11.2.0.3.0 Database.

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

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?

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

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

这似乎是一个已知问题( SqlClient for Entity Framework中的已知问题):

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

以下是一些典型的情况，可能会导致 输出中是否存在CROSS APPLY和/或OUTER APPLY运算符 查询:

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查询.

在我创建视图之前(必须在多个数据库上创建视图)，任何人都可以看到另一种解决方案吗?

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

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

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

代码最终将在Web API中传递给OData控制器的服务类中. 下面的示例使用了演示数据，真实的数据库有700,000条记录， 因此我想避免执行查询，而让OData处理页面限制和进一步过滤.

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

推荐答案

因为您可以自己编写查询.也许您可以使用它创建一个存储过程，然后从Entity Framework中调用SP.

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

这篇关于Linq to Entities Group By(外部申请)"oracle 11.2.0.3.0不支持适用".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！