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

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 debug 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.

score 7 · Accepted Answer · answered Aug 30 '11 at 15:18

7

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

answered Aug 30 '11 at 15:18

Jon Skeet

1,421,763
867
9,128
9,194

It's more than quite possible it's trying to perform it in SQL - it's 100% absolutely certain (see the error message) – Jeff Aug 30 '11 at 15:19
@JeffN825: My point is that it's possible that the attempt to perform the conversion of the "fixed" value is the problem, not the attempt to convert the data that's in the database. Will edit to clarify. – Jon Skeet Aug 30 '11 at 15:20
For my own education, why is it bad to compare doubles? Because of the age old "decimal" problem? (http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems) – Jeff Aug 30 '11 at 15:23
double.TryParse won't work inside the IQueryable with LINQ to SQL – Jeff Aug 30 '11 at 15:25
just to add an update, I have verified the values in the database, and there are no rows in the database that have an invalid numeric value. – TheJediCowboy Aug 30 '11 at 15:26
did you run the exact query I posted with the Convert statement? – Jeff Aug 30 '11 at 15:34
@Jon, I am pretty sure LINQ to SQL's funcletizer will evaluate the Convert.ToDouble(value) in memory and put it as a ConstantExpression in the tree. – Jeff Aug 30 '11 at 15:35
@JeffN825: It would be nice to think so, yes. I'd perform the conversion first anyway, mind you... – Jon Skeet Aug 30 '11 at 16:04
@CitadelCSAlum: Have you looked at the log to see what query is being executed, and tried it for yourself? – Jon Skeet Aug 30 '11 at 16:05

score 0 · Answer 2 · edited May 23 '17 at 12:07

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

I like this solution, the downside is, Items is an IQueryable so would I have access to this function? — TheJediCowboy, Aug 30 '11 at 15:43
Once you map it in your DBML, it should generate the IsNumeric function on your DataContext. — Jeff, Aug 30 '11 at 15:44
If you want to declare it completely independent of your DataContext, add to the DBML, but map to a static method like here http://msdn.microsoft.com/en-us/library/bb386973.aspx — Jeff, Aug 30 '11 at 15:50

score 0 · Answer 3 · answered Aug 30 '11 at 15:19

0

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

answered Aug 30 '11 at 15:19

Mark Cidade

98,437
31
224
236

score 0 · Answer 4 · answered Aug 30 '11 at 15:19

0

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

answered Aug 30 '11 at 15:19

Joel C

5,547
1
21
31

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

4 Answers4