实体框架区分大小写的查询

问题描述

我有一个名为Post的表，该表具有一个名为key的列:

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

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

您可以看到Post的第一个key值是WM(大写)，第二个key值是wm(小写).

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();

我给key传递了一个值(wm和WM)，但得到一个结果.第二个(wm).

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

我已经搜索了解决方案，并发现了这个问题以及答案.当我尝试使用答案并实现[CaseSensitive]数据注释后，当我搜索wm时，我会得到第一篇文章，而当我搜索WM时，我会得到null.我该如何解决这个问题并获得足够的秘诀?

I've searched for the solution and found this questionand 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?

更新:生成的SQL查询:

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]

推荐答案

我通过相同的更改解决了此问题:

I Solve this issue with same change:

1. 将Key数据类型更改为varchar

并使用SqlQuery<T>执行SQL查询:

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()

这篇关于实体框架区分大小写的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！