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;
    }

Any reason the `Number` field is declared as `string` in your table? — Darin Dimitrov, Oct 01 '12 at 16:29
How you handle if exception throws? continues to sum or stop? — cuongle, Oct 01 '12 at 16:31
I have other operations to do with my Number using the string type @DarinDimitrov — S3ddi9, Oct 01 '12 at 16:32
when an exception occurs, it gives a ZERO as my extension @CuongLe — S3ddi9, Oct 01 '12 at 16:33
`Parse` only throws an exception if the value isn't a number... Why do you have a field called `Number` that is actually a string that might not even represent a number! — verdesmarald, Oct 01 '12 at 16:33
You haven't told us which LINQ provider you're using, which makes it harder to help you. — Jon Skeet, Oct 01 '12 at 16:35
Am sorry but people please don't ask why, and please answer how, cause i've given the simplest case, my application is too complicated, I didn't have a choice other then **Number(string)** — S3ddi9, Oct 01 '12 at 16:39
We cannot just "answer how" because strings and numbers have different behaviors. We need to know where you want string-behavior and where you want number-behavior. Whatever reason is forcing you to use strings instead of number will also constrain which methods we can use. — Dour High Arch, Oct 01 '12 at 16:54

score 20 · Accepted Answer · answered Oct 01 '12 at 16:52

20

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.

answered Oct 01 '12 at 16:52

Gromer

9,861
4
34
55

1

you should always add `DefaultIfEmpty` we don't want to see another exception – S3ddi9 Oct 01 '12 at 17:03
`int.TryParse` will not throw an exception. The ternary is giving you your default value if `int.TryParse` returns `false`, meaning the conversion failed. – Gromer Oct 01 '12 at 17:05
You can also use variations on those queries to figure out which ones do not convert. `int temp3; var noConvert = Purchases.Select(x => x.Number).ToList().Where(n => int.TryParse(n, out temp3) == false);` – Gromer Oct 01 '12 at 17:09
2

It should be noted that by enumerating the dataset, you are pulling all of the data for the query from the database into memory. If you were doing a lighter weight query in which you needed to convert to int, this would be inefficient and be doing further operations in memory, as opposed to on the database. – Carson Jan 12 '15 at 23:57
1

As @Carson has noted, this solution is extremely poor advice that is the root cause for developers new to EF experiencing poor performance in their applications. EF.Core has support for this, in EF6 just stick to strings, cast your numerics into strings for comparisons where you can, leave `.ToList()` or other `IEnuerable` solutions as a last resort. – Chris Schaller Jul 01 '21 at 06:27

score 2 · Answer 2 · answered Oct 01 '12 at 16:35

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.

answered Oct 01 '12 at 16:35

Shahar Gvirtz

2,418
1
14
17

7

You can't use `int.TryParse` in an _EF_ query. You will get this: `Unhandled Exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean TryParse(System.String, Int32 ByRef)' method, and this method cannot be translated into a store expression.` – Gromer Oct 01 '12 at 16:44
3

You can make it work, but adding Purchases.ToList(), but then you'll load the entire data which might be a lot of data, and it won't scale really good. You can also add view in the database, which does the conversion in SQL (if you insist to keep the original data as a string), and then returns only rows with valid integers. – Shahar Gvirtz Oct 01 '12 at 16:54

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

2 Answers2

Linked