Follow the code:
Example:
using (var ctx = new dbEntities())
{
var resultado = ctx.Table.SqlQuery("Select * from Table WHERE id = 0 ").ToList<Table>();
}
Another 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 command or insert or delete?
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 query syntax or method syntax , this makes the code cleaner, eases 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 , .Select(x => x) is not available, it is only for demonstration purposes.
Want to know the difference between the two? none, query syntax will be transformed into method syntax , see it as a syntactic sugar , as well as 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 SGBD , you can opt for 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 do not know the current versions of EF , but in older versions, the ExpressionTree generated by IQueryable<T> was first translated to Entity SQL , and then converted to SQL Final.
In this case you would be jumping one step in the process, but in particular, I do not see a positive balance when switching from LINQ to Entity SQL .
SQL
Leave this option only if you need 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 that using SQL Puro within a ORM will kill a lot of its advantages and features, for example, SQL written to SQL Server might not work for PosgreSQL , while LINQ and Entity SQL will not face this type of problem.
Conclusion
Give preference to LINQ , despite the potential of Entity SQL , I do not see much space for it on current days and reserve SQL , be ANSI SQL , TSQL or PL/SQL for special occasions .