CODEWITHSUNDEEP
c#.netlinq
JayJay
JayJay

Reputation: 1068

How get the biggest number?

I am trying to get the biggest number from a field in my database using this code: 

var bb=(from c in Office_TBLs select c.CodeNumber).Max();

When i run the query I receive the result "999" which is wrong because the biggest number in the databse is 1601. Does the datatype being Nvarchar in the field of database matter?

How could I fix my code to get the correct max number.

Upvotes: 0

Views: 230

Answers (4)

Alexei Levenkov
Alexei Levenkov

Reputation: 100547

"999" > "1601" as string comparison - so to get result you want you need to convert string values to numbers.

The easiest approach would be to use .Select(s => int.Parse(s)).Max()) (or .Max(s => int.Parse(s))) instead of .Max() which ends up using regular string comparison.

Note that depending on where data is coming from there could be much better ways to get integer results (including changing field type in database). Using .Select on query most likely force query to return all rows from DB and only than compute Max in memory.

Upvotes: 12

Habib
Habib

Reputation: 223277

Since you are using LINQ to SQL you should use Convert.ToInt32 for converting to string to number, so your query would be:

var bb =(from c in Office_TBLs select Convert.ToInt32(c.CodeNumber)).Max();

See: Standard Query Operator Translation

C# casts are supported only in projection. Casts that are used elsewhere are not translated and are ignored. Aside from SQL function names, SQL really only performs the equivalent of the common language runtime (CLR) Convert. That is, SQL can change the value of one type to another. There is no equivalent of CLR cast because there is no concept of reinterpreting the same bits as those of another type. That is why a C# cast works only locally. It is not remoted.

Upvotes: 3

HABO
HABO

Reputation: 15816

If you use NVarChar then you are asking to have the values sorted alphabetically. "999" comes after "1601" just like "ZZZ" comes after "HUGS".

If the column is supposed to only contain numeric values then the best fix is to change the datatype to a more appropriate choice.

Upvotes: 0

Amit
Amit

Reputation: 15387

Try this

var bb=(from c in Office_TBLs select (e => e.CodeNumber).Max())

Upvotes: 0

Related Questions