Convert string to int in an Entity Framework linq query and handling the parsing exception

Question

I've got this problem, I have a table for purchases

Purchases(Date DateTime, Number string)

I want is to create a new record, so I need the Max(Number), the problem here is that Number is a string, I've tried

Purchases.Select(X=>int.Parse(X.Number)).Max()

but it could throw an exception, I've create a custom ToInt() extension so when I use

Purchases.Select(X=>X.Number.ToInt()).Max()

it throws an exception saying that my ToInt() can't be used with linq query same as the famous ToString()

so my question is : is there a way to cast a string to int in linq query & handling exceptions at the same time or to integrate custom functions to a linq query !!

and this's my extension

    public static int ToInt(this string s)
    {
        try
        {
            return int.Parse(s);
        }
        catch
        {
        }
        return 0;
    }

Answer 1

First way:

var numbers = Purchases.Select(x => x.Number).ToList();

int temp;
int max = numbers.Select(n => int.TryParse(n, out temp) ? temp : 0).Max();

Console.WriteLine("Max: {0}", max);

Second way:

int temp2;
int max2 = Purchases.Select(x => x.Number).ToList().Select(n => int.TryParse(n, out temp2) ? temp2 : 0).Max();

Console.WriteLine("Max 2: {0}", max2);

The key is the .ToList() in those two ways. It gets all the string data from the database, so when you call int.TryParse on the results, the database query has already been run, so it is using pure CLR code, and not trying to convert int.TryParse into a SQL query. I made an EF context in one of my Sandbox projects and verified this works.

Answer 2

I don't understand why you don't want the exception, because if you failed the casting, then it means something wrong happened to your application (or at least, so i get it). I also don't understand why to save what supposed to be a number as a string in the database. Usually, you'll keep it as a number so you won't have to deal with problems like this later, and if someone try to insert wrong value you'll fail on insertion.

BUT - if you do want to make this line works you should use int.TryParse which will return you true if the value casted successfully and will put the value in 'out' member which will be out of the query. like this:

int tmp;
Purchases.Select(X=>int.TryParse(X.Number, out tmp) ? tmp : 0).Max()

and instead of "0" put some default value that won't mess with your logic.