How to get max value of a string column of numbers in Entity Framework [duplicate]

Question

I have a problem here. I was trying to get the max value of an sql field as,

LastInvoiceNo = Convert.ToInt64(dbContext.InvoiceMaster.Max(e => e.InvoiceNo));

But it gives me wrong value since the column type of "InvoiceNo" is varchar. So I tried to convert it to Int64 as

LastInvoiceNo = dbContext.InvoiceMaster.Max(e =>Convert.ToInt64(e.InvoiceNo));

and

LastInvoiceNo = dbContext.InvoiceMaster.Select(e => Int64.Parse(e.InvoiceNo)).Max();

But it throws an exception that

LINQ to Entities does not recognize the method 'Int64 Parse(System.String)' method, and this method cannot be translated into a store expression.

Answer 1

The error you get is because EF is trying to translate your linq query to actual SQL statements, but it can't.

What you could do is bring the data locally by forcing the query to execute, and then you will be able to convert to Int64 and check for Max.

Something like this:

LastInvoiceNo = dbContext.InvoiceMaster.ToList().Max(e => Convert.ToInt64(e.InvoiceNo));

Not sure if that's the best thing to do if you have millions of rows in that table though.

Cheers