Passing parameter to LINQ query

Question

I have a method like below:

public void GetUserIdByCode(string userCode)
{
    var query = from u in db.Users
                     where u.Code == userCode // userCode = "LRAZAK"
                     select u.Id;

    var userId = query.FirstOrDefault(); // userId = 0 :(
}

When I ran the code, I got the default value of 0 assigned to userId meaning the Id was not found. However, if I changed the userCode with a string like below, I will get the value I want.

public void GetUserIdByCode(string userCode)
{
    var query = from u in db.Users
                     where u.Code == "LRAZAK" // Hard-coded string into the query
                     select u.Id;

    var userId = query.FirstOrDefault(); // userId = 123 Happy days!!
}

My question is why passing the parameter into the LINQ query does not work? When I stepped into the code, I got the SQL statement like so:

// Does not work...
{SELECT "Extent1"."LOGONNO" AS "LOGONNO"FROM "DEBTORSLIVE"."DEBTORS_LOGONS" "Extent1"WHERE ("Extent1"."LOGONCODE" = :p__linq__0)}

The hard-coded LINQ query (the working one) gives an SQL statement as below:

// Working just fine
{SELECT "Extent1"."LOGONNO" AS "LOGONNO"FROM "DEBTORSLIVE"."DEBTORS_LOGONS" "Extent1"WHERE ('LRAZAK' = "Extent1"."LOGONCODE")}

What would be the solution?

Answer 1

As a work-around, I use Dynamic Linq. The code below is working for me.

public void GetUserIdByCode(string userCode)
{
    string clause = String.Format("Code=\"{0}\"", userCode);

    var userId = db.Users
                        .Where(clause)
                        .Select(u => u.Id)
                        .FirstOrDefault();    
}

The database query returns an object of User with Code and Id as properties. This is defined in one of my classes.