LINQ query cannot evaluate GET parameter in StartsWith

3
votes

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.

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.

    [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:

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

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?

3
c#asp.netlinqentity-frameworkasp.net-web-api
even though name is being set correctly to "M" You seem to have some pretty compelling evidence that that isn't the case. How do you know that name is being set to M? - Servy
@Servy because the debugger tells me so. Could it be mistaken? - jsallaberry
@user2753837 can you please supply the fill query generated by query.ToString(). "LIKE 'p__linq__0%'" does not look correct. it should be something like "LIKE `@p__linq__0 + '%'". It looks like Entity framework is not creating a proper parameter. that is why you are getting noting back - Trekco
@Ockert "SELECT\r\nExtent1.cliente_id, \r\nExtent1.nome, \r\nExtent1.email, \r\nExtent1.endereco\r\nFROM cliente` AS Extent1\r\n WHERE Extent1.nome LIKE 'p__linq__0%'"` - jsallaberry
What version of EF are you using? I've done something similar with EF 6 and the query generated is not the same as yours. - Trekco

3 Answers

1
votes

This is a reported bug with MySQL Entity Framework 6.9.5

Bug #74918 : Incorrect query result with Entity Framework 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.

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

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

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

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"

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

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

If you have more than one GET method with parameter so you have to change your 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

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

            }
        }
    });