Max() in LINQ Query

Question

I have a Column with DataType as Nvarchar(max) Sample Records:

Advisor_Code
9001
9002
9003
100001
100001
9011

I have tried this Query :

var code = (from a in db.advisor_Registration select a ).Max(a=>a.advisor_Code);

It Returns 9011 but max Number is 100001 . How to fix it

Answer 1

Since one of the tags is entity-framework I assume it should be translatable into SQL. If you use e.g. Convert.ToInt32 in the LINQ statement, this won't succeed.

A common way to get the "numeric" max out of strings without conversion is to order by length and then by string:

var max = myquery.OrderByDescending(x => x.Length)
                 .ThenByDescending (x => x)
                 .First();

where myquery could be any query against a DbSet that returns strings. Of course the results will be meaningless if any of the strings is not numeric.

Answer 2

You can only perform a MAX function with your expected result on a numeric field. nvarchar(max) is not a numeric field. You would have to convert the strings to a long, for example, then take the MAX for MAX to work as you are expecting.