LINQ查询不能计算StartsWith中的GET参数

I'm trying to make a query through a GET request (in Angular.js) using .NET Web API and Entity Framework using LINQ and MySQL db.

我正在尝试使用。net Web API和实体框架(使用LINQ和MySQL db)通过GET请求(使用Angular.js)进行查询。

http://localhost/ProductsApp/api/clientes/GetByName/M

The problems is that the parameter passed by GET is not evaluated and I don't get any results (even though name is being set correctly to "M" according to the debugger). However, if I hardcode a string, I get the expected result.

问题是GET传递的参数没有被计算，我也没有得到任何结果(尽管根据调试器，名称被正确地设置为“M”)。但是，如果我硬编码一个字符串，就会得到预期的结果。

    [ActionName("GetByName")]
    public IEnumerable<cliente> GetByName(string name)
    {

        var query = from c in context.clientes where c.nome.StartsWith(name) select c;
        var query2 = from c in context.clientes where c.nome.StartsWith("M") select c;
        var query3 = context.clientes.Where(c => c.nome.StartsWith(name));
        var query4 = context.clientes.Where(c => c.nome.StartsWith("M"));

        return query.ToList();
    }

My WebApiConfig.cs has the following lines:

我的WebApiConfig。cs有以下几行:

config.Routes.MapHttpRoute(
    name: "ActionApi",
    routeTemplate: "api/{controller}/{action}/{name}",
    defaults: null
);

I suppose that LINQ resolves the variables when it is executed, as hinted below (I removed the unimportant part of the query):

我假设LINQ在执行时解析变量，如下所示(我删除了查询中不重要的部分):

query.ToString()
"SELECT [...] WHERE `Extent1`.`nome` LIKE 'p__linq__0%'"
query2.ToString()
"SELECT [...] WHERE `Extent1`.`nome` LIKE 'M%'"
query3.ToString()
"SELECT [...] WHERE `Extent1`.`nome` LIKE 'p__linq__0%'"
query4.ToString()
"SELECT [...] WHERE `Extent1`.`nome` LIKE 'M%'"

Both query2 and query4 return correct values while query and query3 do not. Why? How can I make it work?

query2和query4都返回正确的值，而query和query3都不返回正确的值。为什么?我如何让它工作?

3 个解决方案

#1

This is a reported bug with MySQL Entity Framework 6.9.5

这是MySQL实体框架6.9.5中报告的错误

Bug #74918 : Incorrect query result with Entity Framework 6: https://bugs.mysql.com/bug.php?id=74918

错误的查询结果与实体框架6:https://bugs.mysql.com/bug.php?id=74918

It has been fixed in MySQL Connector/Net 6.7.7 / 6.8.5 / 6.9.6 releases.

它在MySQL Connector/Net 6.7.7 / 6.8.5 / 6.9.6版本中得到了修复。

Changelog: With Entity Framework 6, passing in a string reference to the "StartWith" clause would return incorrect results.

Changelog:使用实体框架6，向“StartWith”子句传递字符串引用将返回不正确的结果。

Alternatively, a workaround is to use .Substring(0) which forces Entity not to use LIKE (might affect performance).

或者，一个变通方法是使用. substring(0)，它强制实体不使用LIKE(可能会影响性能)。

var query = context.clientes.Where(c => c.nome.StartsWith(name.Substring(0)));

#2

Your name parameter does not equal to "M", it is possible that this value is null. WebApi probably expects a "id" parameter and not "name"

您的name参数不等于“M”，这个值可能为null。WebApi可能需要一个“id”参数而不是“name”

Add a route attribute to make sure that the correct value is passed to the name parameter

添加route属性以确保将正确的值传递给name参数。

[Route("api/clientes/GetByName/{name}")]
public IEnumerable<cliente> GetByName(string name)
{
    ....
}

#3

If you have more than one GET method with parameter so you have to change your WebApiConfig.cs

如果您有多个GET方法和参数，那么您必须更改WebApiConfig.cs

           config.Routes.MapHttpRoute(
            name: "ActionApi",
            routeTemplate: "api/{controller}/{action}/{id}",
            defaults: new { id = RouteParameter.Optional });

or another way to do this you have to hardcode your controller and action in WebApiConfig.cs like this

另一种方法是在WebApiConfig中硬编码控制器和动作。cs这样

            config.Routes.MapHttpRoute(
             name: "GetApi",
             routeTemplate: "api/{controller}/{action}/{id}",
             defaults: new { action = "GetByName", controller = "Your Controller" });

Your Action:

你的行动:

[HttpGet]
public IEnumerable<cliente> GetByName(string name)
{
}

Call it like this

这样叫它

        $.ajax({
        type: 'GET',
        url: 'localhost/ProductsApp/api/clientes/GetByName?Name='+ M,
        data: JSON.stringify({}),
        contentType: 'application/json',
        dataType: 'json',
        success: function (data) {

            }
        }
    });

#1