Why doesn't LINQ to Entities recognize specific methods?

Question

Why can't I do this:

usuariosEntities usersDB = new usuariosEntities(); foreach (DataGridViewRow user in dgvUsuarios.Rows) { var rowtoupdate = usersDB.usuarios.Where( u => u.codigo_usuario == Convert.ToInt32(user.Cells[0].Value) ).First(); rowtoupdate.password = user.Cells[3].Value.ToString(); } usersDB.SaveChanges(); 

And you need to do this:

 usuariosEntities usersDB = new usuariosEntities(); foreach (DataGridViewRow user in dgvUsuarios.Rows) { int usercode = Convert.ToInt32(user.Cells[0].Value); var rowtoupdate = usersDB.usuarios.Where(u => u.codigo_usuario == usercode).First(); rowtoupdate.password = user.Cells[3].Value.ToString(); } usersDB.SaveChanges(); 

I must admit that this is more readable code, but why is it impossible to do this?

Answer 1

The fact is that LINQ queries are converted by the compiler into an expression tree. Then this expression tree is converted to T-SQL and transferred to the server. LINQ to SQL displays specific methods, such as String.Contains for T-SQL equivalents.

In the first example, LINQ does not seem to map Convert.ToInt32 to anything with an exception. The reason it works in the second example is because the Convert.ToInt32 call is made outside the query, so it is not part of the expression tree and does not need to be converted to T-SQL.

This MSDN page describes how LINQ to SQL translates various data types, operators, and methods into T-SQL. (Although Convert.ToInt32 is actually supported in the documentation, so I'm not sure what else could happen here.)

Sorry, only they realized that this is the ADO.NET Entity Framework, not LINQ to SQL. This page lists the ADO.NET Entity Framework mappings. This is a bit more restrictive, mainly because it needs to work with multiple providers.

Answer 2

Because your LINQ to Ent. It does not compile the request in MSIL with all metadata, but simply translates the request into several extantion methods and is limited to lambda analysis of the language absurdity. It means that

this code:
var results = from c in SomeCollection where c.SomeProperty < someValue * 2 select new {c.SomeProperty, c.OtherProperty};

same:

var results = SomeCollection
.Where(c => c.SomeProperty < someValue * 2)
.Select(c => new {c.SomeProperty, c.OtherProperty});