none
Using in Statement with DataTable.Select in ADO.Net

    Question

  •  

    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.

    Thursday, May 04, 2006 8:16 PM

Answers

  • 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 06, 2006 12:45 AM

All replies

  • 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 06, 2006 12:45 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

    Saturday, May 06, 2006 1:24 AM
  • 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

    Monday, May 08, 2006 5:07 PM
  • 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 09, 2006 1:09 AM
  • I used it in the format of ("RefID ='' or RefID= ''")

    and it's working perfectly.

    Thanks

     

    Tuesday, May 09, 2006 4:21 PM
  •  

    Try using convert on the column name. 

     

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

     

    DataTable

    Guid

    Select

    Convert

     

     

    Saturday, August 11, 2007 2:02 AM