I recently discovered that LINQ isn't as versatile as I had hoped and
I hope someone can shed a light on why and maybe provide some
suggestions for workarounds.
The jist of it is I have a datatable with 2 columns, first column is
"Key0" and the second column is "Value". Inside the table is full of
legit data all decimal values in the Key0 field (0.00, 13.00,
etc...). However, that column's datatype in the datatable is actually
a string (Which is a point of confusion as the field in the database
that this datatable is being filled with is a decimal(8,2)).
The first problem is that LINQ seems to try and be too smart when
using the DataTable.select method and tries to predict how to convert
the value in the select statement. For example, if I do
tbl.Select("Key0 = 0") I receive the following exception:
Exception occured: Cannot perform '=' operation on System.String and
System.Int32.
at System.Data.BinaryNode.SetTypeMismatchError(Int32 op, Type left,
Type right)
at System.Data.BinaryNode.BinaryCompare(Object vLeft, Object
vRight, StorageType resultType, Int32 op)
at System.Data.Select.Eval(BinaryNode expr, DataRow row,
DataRowVersion version)
at System.Data.Select.Evaluate(Int32 record)
at System.Data.Select.FindFirstMatchingRecord()
at System.Data.Select.GetBinaryFilteredRecords()
at System.Data.Select.SelectRows()
at System.Data.DataTable.Select(String filterExpression)
at <My Code line calling the datatable.select() statement>
However tbl.Select("Key0 = 0.0") works (note: I did 0.0 even though
the string value is 0.00) and the correct row is returned. Does this
make any sense?
So as a bandaid I changed my query to cast that field as an int in my
SQL query (so now the value is 0 instead of 0.00 in the data table).
Now (My second problem) tbl.Select("Key0 = 0") works correctly,
however now other oddities are popping up. If I do tbl.Select("Key0 =
13") no values are returned even though tbl[13]["Key0"] == 13 (seen
through the debugger). if I instead do tbl.Select("Key0 = '13'") that
does return the correct row, however that restricts me in selecting
numerical data. This isn't the case for all data (0 works, 22 works,
etc...), most of the rows it can find perfectly fine, however I'm
finding a decent chunk of them that aren't working.
Finally, the last issue is when I do tbl.Select("Key0 = 121") I get
the following System.ArgumentException:
Exception occured: Min (40) must be less than or equal to max (-1) in
a Range object.
at System.Data.Select.GetBinaryFilteredRecords()
at System.Data.Select.SelectRows()
at System.Data.DataTable.Select(String filterExpression)
at <My Code line calling the datatable.select() statement>
Does this make sense to anyone? I don't even know where to look for
this issue.
For reference, the rowcount for the datatable is 425, which is the
exact same number of rows returned by the SQL query. Manually look at
the data shows that the data IS the same from the db results to the
datatable contents. If anyone could shed some light on this I would
be very grateful, as of right now it seems like I'm going to have to
implement my own .Select() type of method, which seems like a horrible
waste of resources.
