The SQL server doing queries based COLLATE option, so you can define how comparision will be performed (case sensitive or not). You can do it when you creating table or during query execution.
How can I control collation during my LINQ to SQL queries? Will my queries be allways case insensitive when I will do table.Column == stringValue comparison?
You have to remember that L2S is a Object Relational Mapping system, so its trying to to compare objects, and translate to SQL. In L2S, if you want to compare two strings you have to ToLower() both of them for comparison.
Another thing that was a 'gotcha' for me was that in L2S, string comparison will not evaluate correctly if the comparison value you supply is null. So, in your example, if table.Column is null and stringValue is too, your query will not return the correct results (I am basing this on the assumption that stringValue is a variable defined in your code). In order to compare a string to null in L2S, you have to compare it explicitly to null: table.Column == null.
check out this article
I don't work with the COLLATE option much, but will take my best stab at this question.
According to this article:
LINQ to SQL does not consider server settings when it translates queries.
If COLLATE is a database/table/column setting, it should just be set in the database and be ready to go when you connect.
If COLLATE is a connection setting, you can acquire the connection of your datacontext and run the command to set it. A good place to do this might be in the partial void OnCreated method.