ROW_NUMBER超过（由YYY分区）Linq中？ [英] Row_number over (Partition by yyy) in Linq?

问题描述

我很新的LINQ和我第一次尝试使用时它必须是超级复杂！

I'm very new to LINQ and one of my first attempts at using it had to be super complicated!

我在努力适应这个堆栈溢出问题。

我已经这样写的：

using (var ctx = new MyEntities())
        {
            var q = ctx.tblPrices.OrderByDescending(x => x.Cost)
                .GroupBy(x => x.ItemID)
                .Select(g => new {g, count = g.Count()})
                .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new {j.WebPrice, j.PriceID, j.ItemID}));

            foreach (var i in q)
            {
                sb.AppendFormat("Id = {0}, Name = {1}, ItemId = {2}<hr/>", i.WebPrice, i.PriceID, i.ItemID);
            }

        }

基本上，我想回到最便宜的价格从不同的供应商相同的项目。

Basically, I want to return the cheapest price for the same items from different suppliers.

然而，当我运行该项目，我得到以下很长的错误消息，我不知道这意味着什么。但它看起来pretty严重！

However, when I run the project I get the following very long error message and I have no idea what it means. But it looks pretty serious!

LINQ实体无法识别方法
  'System.Collections.Generic.IEnumerable<$c$c>1[<>f__AnonymousType53[System.Nullable<$c$c>1[System.Decimal],
  System.Int32，System.Int32]
  Zip[tblPrice,Int32,<>f__AnonymousType53](System.Collections.Generic.IEnumerable<$c$c>1[MyProjectMVC.Models.tblPrice],
  System.Collections.Generic.IEnumerable 1 [System.Int32]
  System.Func 3 [MyProjectMVC.Models.tblPrice，System.Int32，
  ＆LT;＆GT; f__AnonymousType5 3 [System.Nullable`1 [System.Decimal]，System.Int32，System.Int32]）
  法，这种方法不能被翻译成店前pression。

LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable1[<>f__AnonymousType53[System.Nullable1[System.Decimal], System.Int32,System.Int32]] Zip[tblPrice,Int32,<>f__AnonymousType53](System.Collections.Generic.IEnumerable1[MyProjectMVC.Models.tblPrice], System.Collections.Generic.IEnumerable1[System.Int32], System.Func3[MyProjectMVC.Models.tblPrice,System.Int32, <>f__AnonymousType53[System.Nullable`1[System.Decimal],System.Int32,System.Int32]])' method, and this method cannot be translated into a store expression.

任何帮助将大大AP preciated。

Any help would be greatly appreciated.

推荐答案

@Adil马迈多夫权，这是becouse LINQ不能到SQL翻译方法。

@Adil Mammadov right, it's becouse of LINQ can't translate method to SQL.

要避免这个问题，你有几种解决方案：

To avoid this problem you have several solutions:

1. 指定MS SQL计算列，只是与LINQ抓住它


2. 写您的OWS转换为SQL


3. @马特Burland怎么说 - 获得结果，然后处理它

我在您的情况假设的问题就可以解决这样的：

I suppose in your situation problem can be solved like this:

var q = ctx.tblPrices.OrderByDescending(x => x.Cost)
                .ToList()
                .GroupBy(x => x.ItemID)
                .Select(g => new {g, count = g.Count()})
                .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new {j.WebPrice, j.PriceID, j.ItemID}));

甚至是这样的：

var q = ctx.tblPrices.OrderByDescending(x => x.Cost)
                .GroupBy(x => x.ItemID)
                .Select(g => new {g, count = g.Count()})
                .ToList()
                .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new {j.WebPrice, j.PriceID, j.ItemID}));

这篇关于ROW_NUMBER超过（由YYY分区）Linq中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！