Case Sensitive Linq to SQL Queries

By default,  Linq to SQL queries are case insensitive.  The C# convention is that all comparisons are case sensitive.  This is a problem and begs the question ‘How to perform a case sensistive query using Linq to Sql’?

To solve this problem declare ‘string’ fields of the linq-to-sql data class to be case sensitive by specifying the server data type by using one of the following;

varchar(4000) COLLATE SQL_Latin1_General_CP1_CS_AS
or
nvarchar(Max) COLLATE SQL_Latin1_General_CP1_CS_AS

Note: The ‘CS’ in the above collation types means ‘Case Sensitive’.

This can be entered in the “Server Data Type” field when viewing a property using Visual Studio DBML Designer.

The following code demonstrates how to perform both case insensitive and case sensitive queries.  First set the ‘Server Data Type’ as shown above.   The code creates 2 Contact objects (database records) with the ‘Name’ field set to ‘Bob’ and ‘bob’.  Performing a case sensitive query returns 1 ‘Contact’ object and performing a case insensitive search returns 2 ‘Contact’ objects.

 

using (SciProjectTestDataContext dc = new SciProjectTestDataContext(
    @"Data Source=Blackoak\SQLEXPRESS2008;AttachDbFilename=C:\DbSciProjectTest.mdf;Integrated Security=True"))
{
   if (dc.DatabaseExists())
      dc.DeleteDatabase();
   dc.CreateDatabase();
   Table<Contact> contact = dc.GetTable<Contact>();

   //
   // Create ‘Contact’ records in the database
   //
   Contact c1 = new Contact
   {
       Name = "Bob"
   };
   dc.Contacts.InsertOnSubmit(c1);
   Contact c2 = new Contact
   {
      Name = "bob"
   };
   dc.Contacts.InsertOnSubmit(c2);
   dc.SubmitChanges();
   //
   // Case sensitive query
   //
   Contact[] cArray = dc.Contacts.Where(c => c.Name == "bob").ToArray();
   Assert.AreEqual(1, cArray.Length);
   //
   // Case insensitive query
   //
   cArray = dc.Contacts.Where(c => c.Name.ToLower() == "bob").ToArray();
   Assert.AreEqual(2, cArray.Length);
}

 

See Also:
Tips to prevent memory leaks.