CODEWITHSUNDEEP
c#asp.netlinqentity-frameworkasp.net-web-api
jsallaberry
jsallaberry

Reputation: 318

LINQ query cannot evaluate GET parameter in StartsWith

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?

Upvotes: 3

Views: 425

Answers (3)

jsallaberry
jsallaberry

Reputation: 318

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

Upvotes: 1

DineshChauhan
DineshChauhan

Reputation: 121

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

            }
        }
    });

Upvotes: 0

Trekco
Trekco

Reputation: 1286

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

Upvotes: 0

Related Questions