Case Sensitive Where Clause in LINQtoSQL?

Question

I want to check whether a Tag (Case-Sensitive) exists in SQL Server 2005 table using LINQtoSQL. Let's say if a tag 'BEYONCE' exists in tags, then I want that I can add 'beyonce' or 'BeYOnce' but not 'BEYONCE' again. Here is the LINQ query i have written:

From t In Context.Tags
Where String.Equals(t.Tag, myTag, StringComparison.Ordinal) = True

But it says Method 'Boolean Equals(System.String, System.String, System.StringComparison)' has no supported translation to SQL. What's the other way to find case sensitive tag?

Answer 1

I don't think you can do it natively with linqtosql.

You could try a bit of ExecuteExpression and some case sensitive techniques. It's not pretty, but it should get the job done.

---

edit Not sure if you're using c# or vb, but here's some c# code. I'm assuming your base table is called Tag

IEnumerable<Tag> matches = Context.ExecuteQuery<Tag>(
    "Select * from Tag where Tag = {0} COLLATE Latin1_General_CS_AS", myTag
);

Heck, just for completeness, here's it in vb.net too :-)

dim matches as IEnumerable(Of Tag) = Context.ExecuteQuery(Of Tag)( _
    "Select * from Tag where Tag = {0} COLLATE Latin1_General_CS_AS", myTag _
)

This is much the same as Chalkey's answer.

Answer 2

If the database has the default collation settings then 'blah' and 'BLAH' will be equal. I dont think you can do this natively with LinqToSQL but you could execute a query that forces collation at a column level... i.e.

String tag = "beYONCE";
IEnumerable result = db.ExecuteQuery(typeof(Int32), 
    "Select * From Table1 Where Value COLLATE Latin1_General_CS_AS = {0}", tag);

That query wouldn't return any results because the tag in the table has 'beyonce' in it and not 'beYONCE'. Changing the string tag to 'beyonce' would return a result.

If you want to alter your database's collation options try reading this article.

Hope this helps.