Convert String To Int in LINQ

Question

I have a LINQ query that queries a DataTable. In the DataTable, the field is a string and I need to compare that to an integer, basically:

if ((electrical >= 100 && electrical <= 135) || electrical == 19)
{
    // The device passes
}

the problem is, I am trying to do this in LINQ like this:

        var eGoodCountQuery = 
            from row in singulationOne.Table.AsEnumerable()
            where (Int32.Parse(row.Field<String>("electrical")) >= 100 &&
                   Int32.Parse(row.Field<String>("electrical")) <= 135) &&
                   Int32.Parse(row.Field<String>("electrical")) != 19 &&
                   row.Field<String>("print") == printName
            select row;

I keep getting the exception:

Input string was not in a correct format

The main problem occurs when electrical == ""

Solution 1

I could not get anything to work, so I re-did the whole method:

    public bool GetElectricalStatus(string printName)
    {
        List<object> eGoodList = new List<object>();
        var eGoodCountQuery =
            from row in singulationOne.Table.AsEnumerable()
            where row.Field<String>("print") == printName
            select row.Field<String>("electrical");

        foreach (var eCode in eGoodCountQuery)
        {
            if (!string.IsNullOrEmpty(eCode.ToString()))
            {
                int? eCodeInt = Convert.ToInt32(eCode);
                if (eCodeInt != null &&
                    (eCodeInt >= 100 && eCodeInt <= 135) || eCodeInt == 19)
                {
                    eGoodList.Add(eCode);
                }
            }
        }
        if (eGoodList.Count() > 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

The main problem occurs when electrical == ""

Solution 2

Unfortunately, the framework doesn't provide a nice clean way to handle parsing scenarios where it fails. Of what's provided, they only throw exceptions or use out parameters, both of which does not work well with linq queries. If any one value you're parsing fails, the entire query fails and you just can't really use out parameters. You need to provide a method to handle the parsing without that does not throw and does not require using out parameters.

You can handle this in many ways. Implement it where upon failure, you return some default sentinel value.

public static int ParseInt32(string str, int defaultValue = 0)
{
    int result;
    return Int32.TryParse(str, out result) ? result : defaultValue;
}

Or what I would recommend, return a nullable value (null indicating it failed).

public static int? ParseInt32(string str)
{
    int result;
    return Int32.TryParse(str, out result) ? result : null;
}

This simplifies your query dramatically while still leaving it readable.

public bool GetElectricalStatus(string printName)
{
    var query =
        from row in singulationOne.Table.AsEnumerable()
        where row.Field<string>("print") == printName
        // using the nullable implementation
        let electrical = ParseInt32(row.Field<string>("electrical"))
        where electrical != null
        where electrical == 19 || electrical >= 100 && electrical <= 135
        select row;
    return !query.Any();
}

p.s., your use of the Convert.ToInt32() method is incorrect. It is the same as calling Int32.Parse() and does not return a nullable, it will throw on failure.

Solution 3

I would check if the data in the column does not contain leading/trailing whitespaces - i.e. "15 " rather than "15" and if it does (or might do) trim it before trying to convert:

Int32.Parse(row.Field<String>("electrical").Trim())

BTW: not related to the error but I'd use let statement to introduce a local variable and do the conversion once:

let x = Int32.Parse(row.Field<String>("electrical").Trim()) 
where x >= 100...

Comments

[kformeck]

this does not fix the problem

[Anthony Pegram]

Note that if you updated your question with the useful tidbit about the empty string, people could make your original query work.

[spikey]

This does not work and throws: Not Implemented Inner exception message: LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression.

[John Lord]

it would work if you finalized the dataset before doing it. That would switch it to linq to objects.