CODEWITHSUNDEEP
c#sqllinq-to-sqlsearchguid
baobab
baobab

Reputation: 1

Search uniqueidentifier value in SQL Server 2008 table

I have a table in SQL Server 2008 with uniqueidentifier field.

In my application (C#, WPF) I have a textbox which intended for search some data by GUID, user can enter guid by typing, ctrl+v, etc, so he/she can make an error.

What is the best and fastest approach to make a search by this field using linq2sql ignoring whitespaces, "-"... any other characters?

var d = from status in dc.FolderStatus.Where(status => status.Date <= DateTime.Now 
                    && status.Folder.TapeCode.ToString().Replace("-", "") == tapeCode.ToUpper().Replace("-", "").Replace(" ", "")
                    )

TapeCode is uniqueidentifier in SQL Server 2008 and Guid in dbml file. Is it ok?

Upvotes: 0

Views: 689

Answers (3)

RPM1984
RPM1984

Reputation: 73132

You shouldn't try and do .Replace on the SQL field, as this will actually get executed on the server, which is very slow.

You should massage the data coming in to your method.

I.e

public void GetFoo(string guid)
{
     string sqlGuid = guid.ToString().Trim().Replace("-", "");

     var d = from status in dc.FolderStatus.Where(status => status.Date <= DateTime.Now &&
                                                            status.Folder.TapeCode == sqlGuid);
}

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1501033

If it's a uniqueidentifier field in SQL, I'd expect native support in LINQ to SQL. I believe you should be able to do:

Guid tapeCode = new Guid(text);
var d = from status in dc.FolderStatus
        where status.Date <= DateTime.Now && status.Folder.TapeCode == tapeCode
        select ...;

Now of course that will throw an exception if text doesn't represent a valid GUID. If you're using .NET 4, you can use Guid.TryParse in the same way you would for parsing integers etc:

Guid tapeCode;
if (Guid.TryParse(text, out tapeCode))
{
    var d = from status in dc.FolderStatus
            where status.Date <= DateTime.Now &&
                  status.Folder.TapeCode == tapeCode
            select ...;
}
else
{
    // Handle invalid input
}

Upvotes: 0

Dean Harding
Dean Harding

Reputation: 72658

No, that will be extremely slow (it'll have to do a table scan, convert each identifier to a string and compare the string). Instead, you'll have to convert the input string to a Guid and compare that:

tapeCode = Regex.Replace(tapeCode, "[^0-9a-zA-Z]+", "");
Guid id = Guid.Parse(tapeCode);
var d = from status in dc.FolderStatus
        where status.Date <= DateTime.Now 
           && status.Folder.TapeCode == id
        select status;

(Note that Guid.Parse method accepts 32 hexadecimal digits without dashes as a valid input).

Upvotes: 3

Related Questions