实体框架案例敏感查询

本文是小编为大家收集整理的关于实体框架案例敏感查询的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

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

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

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

当我使用以下代码执行查询时:

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

我以一个值(WM和WM)传递key，但要获得一个结果.第二个(wm).

我已经搜索了该解决方案，并找到了此问题和此答案.在搜索wm时，我尝试使用答案并实现[CaseSensitive]数据注释后，我会回到第一篇文章，当我搜索WM时，我会得到null. 我如何解决此问题并使用钥匙获得足够的帖子?

更新: 生成的SQL查询:

   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]

推荐答案

我以相同的更改解决了这个问题:

更改Key数据类型varchar

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

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

本文地址：https://www.itbaoku.cn/post/1556982.html

百度翻译此文有道翻译此文

问题描述

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]

推荐答案

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

上一篇：在列表中获取列表的不同值

下一篇：帮助使用LINQ查询数据表