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

Question

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

q.Value is the string value of double, and value also the string value of double, both of which must be converted to doubles so that they can be compared for equality.

When I debug this LINQ statement, I get the following SQLException:

Error Converting varchar data type to float

I'm not sure why this does not allow me to do this, but I wonder what the fix is, I need my two values ​​to compare here.

Answer 1

To start, I will extract Convert.ToDouble(value) out into 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 is possible that this is an attempt to convert value to a problem, and not an attempt to convert q.Value to some string. Alternatively, maybe you have a string that does not have a valid value. (You can try using double.TryParse , but I'm not sure how well this giong works ...)

However, it is usually a bad idea to compare binary floating point values ​​with simple equality first and foremost. You can use some degree of tolerance (and exactly how it works with LINQ to SQL, that's another matter ...)

Answer 2

It looks like you are using LINQ to SQL. This error comes directly from the SQL Server executing the query (and not from your code). I assume that you have a SOME string with a value that is not a valid numeric value.

I would execute the following query in SSMS, and I think you will find that it fails with an error

 Error Converting data type varchar to float select convert(float, value) from leaddata 

EDIT:

If you want to add some fault tolerance, as John suggested, you can map the IsNumeric function and do the following:

In your DBML (link How to find out 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)); 

Answer 3

Make sure the string values ​​you convert to float in SQL Server are valid (for example, they are not empty or they have a valid decimal point character).

Answer 4

It seems that one of the values ​​cannot be successfully converted to float.