C# Convert.ToDouble(value) in Lambda LINQ Statement

I have the following LINQ statement:

Items = Items.Where(p => p.LeadDatas.Any(
                         q =>
                         q.LeadField.Name == descriptor.Name &&
                         Convert.ToDouble(q.Value) == Convert.ToDouble(value)));

The q.Value is a String value of a double, and value is also a String value of a double, both of these needed to be converted to doubles so that they can be compared for equality.

When I d开发者_StackOverflowebug over this LINQ statement, I am getting the following SQLException:

Error Converting data type varchar to float

I am not sure why it is not allowing me to do this, but I am wondering what the fix is, I need my two values to be compared for equality here.

To start with, I would extract the Convert.ToDouble(value) out to a local variable:

double target = Convert.ToDouble(value);
Items = Items.Where(p => p.LeadDatas.Any(q =>
                             q.LeadField.Name == descriptor.Name &&
                             Convert.ToDouble(q.Value) == target));

It's quite possible that it's the attempted conversion of value that's the problem, rather than the attempted conversion of q.Value for some row. Alternatively, it could be that you've got a row which doesn't have a valid value. (You could try using double.TryParse but I'm not sure how well that's giong to work...)

However, it's also generally a bad idea to compare binary floating point values with simple equality in the first place. You may want to use some degree of tolerance (and exactly how that works with LINQ to SQL is another matter...)

Check to see that the string values you are converting to float in SQL Server are valid (e.g., they aren't blank or they have a valid symbol for decimal point).

It sounds like one of the values can't be successfully converted to a float.

It looks like you're using LINQ to SQL. That error is coming directly from the SQL Server executing the query (not your code). My guess is that you have SOME row with a Value that is not a valid numeric value.

I would run the following query in SSMS and I think you're find it fails with the error

Error Converting data type varchar to float

select convert(float, value) from leaddata

EDIT:

If you want to add some fault tolerance as Jon suggested, you could map the IsNumeric function and do the below:

In your DBML (reference How to know if a field is numeric in Linq To SQL)

<Function Name="ISNUMERIC" IsComposable="true">
    <Parameter Name="Expression" Parameter="Expression" Type="System.String" DbType="NVarChar(4000)" />
    <Return Type="System.Boolean" DbType="BIT NOT NULL"/>
</Function>

In your code:

double target = Convert.ToDouble(value);
Items = Items.Where(p => p.LeadDatas.Where(i => myDataContext.IsNumeric(i)).Any(q =>
                             q.LeadField.Name == descriptor.Name &&
                             Convert.ToDouble(q.Value) == target));