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
– Adil Mammadov, Commented Dec 12, 2014 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
– Matt Burland, Commented Dec 12, 2014 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
– Rhumborl, Commented Dec 12, 2014 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
– Rhumborl, Commented Dec 12, 2014 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
– ComfortablyNumb, Commented Dec 16, 2014 at 11:03

teo van kot · Accepted Answer · 2017-07-25 06:24:55Z

6

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

edited Jul 25, 2017 at 6:24

answered Dec 12, 2014 at 14:06

teo van kot

12.5k10 gold badges41 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lee Harrison Over a year ago

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

Collectives™ on Stack Overflow

Row_number over (Partition by yyy) in Linq?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related