Entity Framework Case Sensitive Query

Question

I have a table that named Post which has a column named key:

  Id | Key | Title
 --------------------
   1 | WM  | First
 --------------------
   2 | wm  | Second

As you can see the first key value of a Post is WM (Uppercase) and the second key value is wm (lowercase).

When I execute the query with following code:

var post = await _posts.Where(o => o.Key == key).Select(o => new
    {
        id = o.Id,
        title = o.Title
    }).SingleOrDefaultAsync();

I pass the key with a value (wm and WM) but get one result. The second one (wm).

I've searched for the solution and found this question and this answer. After I tried to use answer and implement [CaseSensitive] data annotation when I search for wm I get back the first post and when I search for WM I get null. How can I solve this and get an adequate post with key?

Update: Generated SQL Query:

   SELECT [Limit1].[C1]    AS [C1],
       [Limit1].[Id]    AS [Id],
       [Limit1].[Title] AS [Title]
FROM   (SELECT TOP (2) [Extent1].[Id]    AS [Id],
                       [Extent1].[Title] AS [Title],
                       1                 AS [C1]
        FROM   [dbo].[Posts] AS [Extent1]
        WHERE  ([Extent1].[Key] = 'wm' /* @p__linq__0 */)
                OR (([Extent1].[Key] IS NULL)
                    AND ('wm' /* @p__linq__0 */ IS NULL))) AS [Limit1]

Did you check to make sure your DB column was setup with the correct collation? Personally I'd stick to doing .Where(o => o.Key == key).AsEnumerable().Where(o => o.Key == key). — juharr
– juharr, Commented Sep 15, 2017 at 12:28
Check in your database what is the type of your Key column. — mrogal.ski
– mrogal.ski, Commented Sep 15, 2017 at 12:31
@juharr yes, I try this code but my code result doesn't change. — Soheil Alizadeh
– Soheil Alizadeh, Commented Sep 15, 2017 at 12:42

Soheil Alizadeh · Accepted Answer · 2017-09-15 15:49:12Z

1

I Solve this issue with same change:

change Key data type to varchar

and execute SQL Query with SqlQuery<T>:

var post = await _uow.Database
            .SqlQuery<PostUrlDto>(
                "SELECT Id , Title FROM Posts WHERE [Key] = @postkey COLLATE SQL_Latin1_General_CP1_CS_AS",
                new SqlParameter("postkey", postkey)).SingleOrDefaultAsync()

answered Sep 15, 2017 at 15:49

Soheil Alizadeh

3,06611 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gert Arnold Over a year ago

Can't believe that this helps. You use a case-sensitive collation (CS)..

mjwills Over a year ago

Case sensitive is what he / she wants @GertArnold .

Gert Arnold Over a year ago

Hmm, probably. The problem description isn't the clearest I ever saw.

Collectives™ on Stack Overflow

Entity Framework Case Sensitive Query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related