LINQ to Entities does not recognize the method (Partition/Rownumber)

Question

I can't for the life of me figure out why this query isn't running

It compiles correctly, but fails during runtime when trying to do the .ToList() with the error shown below after the code.

I've reviewed the similar answers but it seems each one is specific with their issues.

I split the Query into multiple lines to try to narrow down the offending code, and it seems to be on line after "// Partition Over and Produce RowCount for each partition" but I can't figure it out.

I borrowed the logic for the GroupBy/Partition from this: Row_number over (Partition by xxx) in Linq?

[DataContract(Name = "Checksum")]
public class Checksum
{
    [DataMember(Name = "SortColumn")]
    public DateTime SortColumn { get; set; }
    [DataMember(Name = "Identifier")]
    public string Identifier { get; set; }
    [DataMember(Name = "Seqnum")]
    public int Seqnum { get; set; }
}

public void TestLinQ()
{
    myObjectContext context = new myObjectContext();

    DateTime startDate = new DateTime();

    IQueryable<Signin> iQ = context.Signin;
      iQ = iQ.OrderBy(o => o.LastUpdateTimeStamp);
      iQ = iQ.Where(x=>x.LastUpdateTimeStamp == startDate);

    // Partition Over and Produce RowCount for each partition
    IQueryable<Checksum> iQ2 = iQ.GroupBy(x => x.LastUpdateTimeStamp).Select(g => new { g, count = g.Count() }).SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new Checksum { SortColumn = j.LastUpdateTimeStamp, Identifier = j.SigninId, Seqnum = i }));

    iQ2 = iQ2.Where(x => x.Seqnum < 1000);

    // Build Checksum Code

    List<Checksum> outlist = iQ2.ToList();

    // End Build Checksum Code
}

Result Message:

Test method WebRole1.Tests.ContinuationTokenTests.TestLinQ threw exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable'1[WebRole1.Tests.Checksum] Zip[Signin,Int32,Checksum](System.Collections.Generic.IEnumerable'1[iSignRepo.Models.Signin], System.Collections.Generic.IEnumerable'1[System.Int32], System.Func`3[iSignRepo.Models.Signin,System.Int32,WebRole1.Tests.Checksum])' method, and this method cannot be translated into a store expression.

It looks like the EF provider doesn't know how to convert `Zip` into SQL. You may have to fetch the data from the DB before that point so that the `Zip` is done in memory instead. — juharr, Nov 12 '15 at 14:46

score 1 · Accepted Answer · answered Nov 12 '15 at 14:49

1

Basically, it is unable to convert the Zip to SQL.

All you need to do is execute the query before using that method

IQueryable<Checksum> iQ2 = iQ.GroupBy(x => x.LastUpdateTimeStamp)
                             .Select(g => new { g, count = g.Count() })
                             .ToList() // Executes the Query
                             .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new Checksum { SortColumn = j.LastUpdateTimeStamp, Identifier = j.SigninId, Seqnum = i }));

answered Nov 12 '15 at 14:49

Vlad274

6,514
2
32
44

Yikes....I wanted to restrict the dataset based off the result of the ZIP. So if the query still has 150000 records at the time of the .ToList(), will it pull it down to the server, then the code reduces it with the next query part? Hopefully this isn't too costly. I'll try it. Thanks!! – Chris ten Den Nov 12 '15 at 15:08
Depending on what you're doing in the .Zip method, you might be able to. I'd recommend opening a new question about how to convert that to EF friendly code – Vlad274 Nov 12 '15 at 15:10
Yeah I think I'll have to, it's taking an inordinate time to run, when done in straight SQL it is completed in 400ms, with this its already over 100 seconds. – Chris ten Den Nov 12 '15 at 15:25

LINQ to Entities does not recognize the method (Partition/Rownumber)

1 Answers1