Using in Statement with DataTable.Select in ADO.Net

_{Thursday, May 4, 2006 8:16 PM}

I am using this code to get rows from a datatable but it throws an exception

(Cannot perform '=' operation on System.Guid and System.String)

DataRow[] drows = dt.Select("RefID in('1e1cde8e-28b6-493d-b6ce-d1b81b69e83f','1e1cde8e-28b6-493d-b6ce-d1b81b69e83f')");

PS it works with strings and fails when working with guids.

Your help is appreciated.

_{Saturday, May 6, 2006 12:45 AM ✅Answered | 1 vote}

I think this may be a bug in the DataSet. You can use a Guid column in a simple comparison like this:

DataRow[] drows = dt.Select("RefID = '1e1cde8e-28b6-493d-b6ce-d1b81b69e83f'");

But not with an IN clause. You should be able to build a series of OR statements instead of IN to work around this, or you can use Convert to explicitly convert a .NET Guid type:

DataRow[] drows = dt.Select("RefID IN (CONVERT('1e1cde8e-28b6-493d-b6ce-d1b81b69e83f', 'System.Guid'), CONVERT('1e1cde8e-28b6-493d-b6ce-d1b81b69e83f', 'System.Guid'))");

Since you can use the = operator without having to Convert, I would expect this to work the same way, but there may be some reason why it doesn't work like that. I will look into this further and post back next week.

Thanks,

Sarah

_{Saturday, May 6, 2006 1:24 AM}

I Created a string of Guids with or...like

(RefID =' ' or RefID ='' or ...) and it works fine. But still I wonder why it works with strings.

Thanks sarah

_{Monday, May 8, 2006 5:07 PM}

It works with strings because it goes through a different code path that performs the comparison differently than if you use Guid. If your DataColumn is of type string, there is no problem doing the comparison of string to string. The same thing applies if you use the = operator instead of IN. In that case, it converts the Guid to a string before comparing to the string on the right side of the = sign. It would be helpful if it could do the same thing in the IN case, but it doesn't. That's what I still need to investigate further. Either the comparison code would need to be changed to allow this, or there is some way to make it work today that I just haven't found yet.

Thanks,

Sarah

_{Tuesday, May 9, 2006 1:09 AM}

Okay, I talked to one of the DataSet developers today, and this is by design. The Guid data type just doesn't have a native comparision to string, so that's why the failure occurs. In the = case, as I mentioned, we are doing some extra work under the covers, and are actually converting the Guid to a string before doing the comparison. This is confusing because the end result is different behavior than the IN case (and probably other operators that we haven't discussed), but basically the = is just a special case.

Your best bet is to use one the workarounds I mentioned before, as you have already discovered this works.

Thanks,
Sarah

_{Tuesday, May 9, 2006 4:21 PM}

I used it in the format of ("RefID ='' or RefID= ''")

and it's working perfectly.

Thanks

_{Saturday, August 11, 2007 2:02 AM}

Try using convert on the column name. 

Convert([YourGuidCOlumnName], 'System.String') in ('...', '...' )

DataTable

Guid

Select

Convert