Case Sensitive Comparison with LINQ RSS

• • Reply
  

  pbz

  Member

  310 Points

  161 Posts

  Case Sensitive Comparison with LINQ

  Nov 12, 2008 03:36 AM|LINK

  Hi guys,

  How do I force LINQ to SQL to make case sensitive comparison on the database side? When authenticating I need the password authentication to be case sensitive while the username to be case insensitive. The generated query does not make a distinction between the two and SQL server comparisons are case insensitive. Before LINQ I used to convert the fields to varbinary and compare the two that way. How can I do something similar with LINQ?

  Thanks.
  

• • Reply
  

  kashmirindot...

  Participant

  978 Points

  186 Posts

  Re: Case Sensitive Comparison with LINQ

  Nov 12, 2008 04:47 AM|LINK

  Hi,

  You can convert the password field to uppercase while inserting to database and at the time of login, you can do something like:

  var Query = from obj in context.Table
                    where obj.Name == txtName.Text && obj.Password == txtPassword.Text.ToUpper()
                    select obj;
  

  Regards,
  Tahir.
  • Marked as answer by Wencui Qian - MSFT on Nov 19, 2008 09:47 AM
• • Reply
  

  Naom

  All-Star

  36004 Points

  7901 Posts

  Re: Case Sensitive Comparison with LINQ

  Nov 12, 2008 04:48 AM|LINK

  Check http://msdn.microsoft.com/en-us/library/hyxc48dt.aspx 

  Found also this http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

  Beware of bugs in the above code; I have only proved it correct, not tried it.
  (Donald Knuth)
  
  Visit my blog
  
  Microsoft Community Contributor 2011-12
  • Edited by Naom on Nov 12, 2008 04:53 AM
  • Marked as answer by Wencui Qian - MSFT on Nov 19, 2008 09:47 AM
• • Reply
  

  callcopse

  Member

  2 Points

  1 Post

  Re: Case Sensitive Comparison with LINQ

  Feb 03, 2010 02:39 PM|LINK

  The only way I have got around this is a) using a stored proc with a collate hint or b) post retrieval case sensitive comparison. The latter is probably fine for limited data sets the former good for bigger.

• • Reply
  

  renjucool

  Member

  11 Points

  8 Posts

  Re: Case Sensitive Comparison with LINQ

  Jun 01, 2010 08:49 AM|LINK

  You can try this method by converting the search string ToLowerInvariant.

  string query = string.Empty;
          query = "searchString";
          string path = Server.MapPath("~/contacts.xml");
          XDocument xd = XDocument.Load(path);
          var results = (from items in xd.Elements("Company").Elements("Contact")
                         where items.Element("Name").Value.ToLowerInvariant().Contains(query.ToLowerInvariant())
                         select new
                         {
                             Id = items.Element("ID").Value,
                             Photo = (string)items.Element("photo").Value,
                             Name = (string)items.Element("Name").Value,
                             BloodGroup = (string)items.Element("Bg").Value,
                             Dob = (string)items.Element("dob").Value,
                             Anniversery = (string)items.Element("avd").Value,
                             Mobile = (string)items.Element("cnum").Value,
                             Designation = (string)items.Element("desig").Value,
                             Team = (string)items.Element("team").Value
                         });

  
  
  

  Renju
  Asp.net Developer
  www.renjucool.com
  http://blog.renjucool.com
  http://silverlightedworld.blogspot.com
• • Reply
  

  John Hansen

  Member

  2 Points

  1 Post

  Re: Case Sensitive Comparison with LINQ

  Jun 16, 2010 04:03 PM|LINK

  <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">To solve this problem declare ‘string’ fields to be case sensitive by specifying the server data type by using one of the following;</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">varchar(4000) COLLATE SQL_Latin1_General_CP1_CS_AS </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">or </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">nvarchar(Max) COLLATE SQL_Latin1_General_CP1_CS_AS</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">Note: The ‘CS’ in the above collation types means ‘Case Sensitive’.</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">This can be entered in the “Server Data Type” field when viewing a property using Visual Studio DBML Designer.</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"></div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">For more details see http://yourdotnetdesignteam.blogspot.com/2010/06/case-sensitive-linq-to-sql-queries.html</div>

  To perform case sensitive Linq to sql queries declare ‘string’ fields 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.

  For more details see http://yourdotnetdesignteam.blogspot.com/2010/06/case-sensitive-linq-to-sql-queries.html