What’s the difference between the two select?

Question

Follows the code:

Example:

        using (var ctx = new dbEntities())
        {
            var resultado = ctx.Table.SqlQuery("Select * from Table WHERE id = 0 ").ToList<Table>();                
        }

Other Example:

private dbEntities db = new dbEntities();

        var query = from m in db.Table
                    where m.Id == 1
                    select m;

Which of the 2 is most recommended to do select or update or Insert or delete ?

Answer 1

Matheus, your doubt is not necessarily about ASP.NET MVC, but about Entity Framework but could be applied to whatever ORM.

LINQ

I would say that most of the time, you should opt for LINQ, be it query syntax or method syntax, This makes your code cleaner, facilitates maintenance, etc.

LINQ - Query Syntax

using (var db = new dbEntities())
{
    var query = 
        from m in db.Table
        where m.Id == 1
        select m;
}

LINQ - Method Syntax

using (var db = new dbEntities())
{
    var query = db.Table.Where(x => x.Id == 1).Select(x => x);
}

In the case of method syntax, the .Select(x => x) is dispensable, is there only for demonstration purposes.

You want to know the difference between the two? No, the query syntax will be transformed into method syntax, see him as a syntactic sugar, as well as the foreach.

Entity SQL

But if you want to have a little more control, have the possibility to write your own query and that it works in whatever you want SGBD, may choose to Entity SQL.

using (var db = new dbEntities())
{
    var esqlQuery = @"SELECT VALUE Entity FROM dbEntities.Table as Entity where Entity.Id = 1";
    var query = new ObjectQuery<Contact>(esqlQuery, db, MergeOption.NoTracking);
}

I don’t know about the more current versions of EF, but in older versions, the ExpressionTree generated by IQueryable<T> was first translated into Entity SQL, to be converted to SQL Final.

In this case you would be skipping a step in the process, but particularly, I do not see a positive balance when changing the LINQ for Entity SQL.

SQL

Leave this option only if you need a fine-tuning in SQL, either because the query is too complex, the ORM is not delivering a satisfactory result, etc.

using (var ctx = new dbEntities())
{
    var resultado = ctx.Table.SqlQuery("Select * from Table WHERE id = 1").ToList<Table>();                
}

But keep in mind to use SQL Puro within a ORM will kill most of the advantages and resources of it, for example, a SQL written for SQL Server may not work for PosgreSQL, while the LINQ and the Entity SQL will not face this kind of problem.

Completion

Give preference to the LINQ, despite the potential of Entity SQL, I don’t see much space for the same nowadays and reserve the SQL, be it ANSI SQL, TSQL or PL/SQL for special occasions.