4

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.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
a4ashiq
  • 79
  • 2
  • 8
  • 3
    Why do you store numbers in a varchar column at all? Solve that problem first, best solution :) – fejesjoco Feb 19 '14 at 11:16
  • thats not a prob buddy, may be i need to add chars also in to it. get it? – a4ashiq Feb 19 '14 at 11:20
  • You're not supposed to keep strings and ints in the same column and resolve each row's type at runtime. No exceptions. – Tarec Feb 19 '14 at 11:26

2 Answers2

5

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

Luc Morin
  • 5,302
  • 20
  • 39
  • thanks brother. Of course it is a problem with more data. But, I will use this for now until I find another solution. I am not good at this EF. – a4ashiq Feb 19 '14 at 12:21
  • There might be a way to get this to be processed on the server though, but I must admit I'm not familiar enough with writing my own Linq provider stuff (if that's even the way to name this). – Luc Morin Feb 19 '14 at 12:25
  • I wanted to do the same as the OP, so i tried your snippet, your code is okay but what should i do if i wanted to increment the varchar id by 1 and then use that id to add a new row in database along with some other entities? – Nurul Mar 06 '17 at 01:30
1

If you are working with MSSQL, you may try to use SqlFunctions static class that specially designed to be used in LINQ queries and to be translated to the native server functions. Looks a bit weird, but the following may work:

    dbContext.InvoiceMaster.Max(e =>SqlFunctions.Replicate("0", 16-e.InvoiceNo.Length) + e.InvoiceNo);

What I'm trying to do here is to emulate PadLeft function that is not available in SqlFunctions, so having all strings normalized by the length you may select max value and then convert it to actual number.

Pavel Kutakov
  • 971
  • 1
  • 7
  • 11
  • I'm working with MSSQL, But I cant find any SqlFunctions. Am I missing something? – a4ashiq Feb 19 '14 at 12:23
  • Probably you have to specify namespace and/or reference System.Data.Entity. Here is the official help: http://msdn.microsoft.com/library/system.data.objects.sqlclient.sqlfunctions(v=vs.110).aspx – Pavel Kutakov Feb 19 '14 at 12:48