Row_number over (Partition by yyy) in Linq?

Question

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

I am trying to adapt the accepted answer in this Stack Over question.

I have written it thus:

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.

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

Any help would be greatly appreciated.

May problem be at Enumerable.Range(1, t.count)? I think it is not supported by LINQ to SQL — Adil Mammadov, Dec 12 '14 at 13:57
Your answer is here: `method cannot be translated into a store expression.` Linq to SQL isn't able to translate part of your query into an SQL command. You may have to get the results from the database first (a simple `.ToList` usually does the trick) and then process them. — Matt Burland, Dec 12 '14 at 14:01
When you say cheapest price from different suppliers, do you mean the result will have one entry for each unique `ItemID`, along with the `WebPrice` and `PriceID` representing the cheapest price? — Rhumborl, Dec 12 '14 at 14:08
Looking again, doesn't `var q = ctx.tblPrices.OrderBy(x => x.ItemID).ThenByDescending(x => x.Cost);` tell you what you need? — Rhumborl, Dec 12 '14 at 14:51
There will be a price to a supplierId and an itemId. There could be server itemIds but with different suppliers and different prices. The query below looks like its doing its work. Bur how I'm not too clear!! — ComfortablyNumb, Dec 16 '14 at 11:03

teo van kot · Accepted Answer · 2017-07-25T06:24:55.780

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

To avoid this problem you have several solutions:

Specify MS SQL computed column and just grab it with LINQ
Write your ows translation to SQL
How @Matt Burland says - get result and then process it

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

Or even like this:

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

You also can check this my answer.

ToList is usually the easiest way to solve these problems. Also, using ToList closes the DataReader, so you won't run into locked dataReader issues when running Linq in a web application — Lee Harrison, Dec 12 '14 at 14:13

Row_number over (Partition by yyy) in Linq?

1 Answers1

Linked