'Name' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near simple identifier, line 6, column 1.
I've got the above error while running the following code (actually the code was a more complex but I reduced it to essentials):
DbTestEntities db = new DbTestEntities();
var query = db.People.Where("Name = 'John'");
Console.WriteLine(query.Count());
(I think it is obvious that I have a test db with a Person table)
There seems to be something wrong with the 'Name' from my expression, but what exactly could it be on such a simple expression? The Name column does exist.
I googled the error and found some people that encountered this error but in different circumstances which didn't get me the solution but put me on the right track: a guy had the same problem with an OrderBy("Column") so it become clear to me that using just the column name isn't going to work.
Next I looked at the sql expression that gets generated and found what was wrong:
Console.WriteLine(query.CommandText);
-Sql+Expression.png)
Yes, that "it" alias was the problem so the following code works:
DbTestEntities db = new DbTestEntities();
var query = db.People.Where("it.Name = 'John'");
Console.WriteLine(query.Count());
Next the question: can it be always used as it.Column?
After running a few tests the answer is positive.
Can it be changed to other alias name?
Actually it can - looking to ObjectSet's properties I quickly identified the property: Name (dooh! ).
So now I can do this:
db.People.Name = "People";
var query = db.People.Where("People.Name = 'John'");
Console.WriteLine(query.CommandText);
-Sql+Expression+2.png)
And no, it doesn't work to set the Name to empty string (or null).
I remembered vaguely that long time ago I read a post about using string c# code instead of string sql code. A quick search on my favorite blogs found this article of Scott Guthrie - Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library) in which he points to an example on how to do that.
IQueryable was extended for this. I’ve tried and worked as expected:
var query = db.People.AsQueryable().Where("Name == \"John\"");
//or I can use parameters
var query2 = db.People.AsQueryable().Where("Name == @0", "John");