what's the purpose of entity sql, i mean if you have linq to entities why would you need to write queries in string, are there any performance reasons or something ?
Add a comment
|
2 Answers
LINQ to Entities does not allow you access to every feature of your database. Being able to "reach into" the database is sometimes necessary for advanced queries, either to pull them off in the first place or to improve the sometimes horrible choices that the LINQ to Entities system will make about your query.
That said, I believe that LINQ to Entities should be the first tool reached for. If the performance becomes a problem, or you have something more complex I would then encapsulate that problem piece in a stored procedure and call that. There is no reason for strings being used as the basis of queries these days.
Comments
ESQL does allow you to choose a collation on a where clause, something which isn't supported in LINQ-to-Anything. This can be genuinely useful. ESQL also allows you to specify the precise type you want returned when types inherit from each other (as opposed to LINQ's OfType, which returns instances of a certain type and any subtype). Beyond that, I can't think of a great reason to use it. It's occasionally nice to be able to build queries in strings, but DynamicQuery/Dynamic LINQ is generally good enough in the very rare cases where this is necessary.
I think (perhaps cynically) that the "real" purpose of ESQL is "it predates LINQ."
Regarding Godeke's point of fixing non-optimal queries, I have yet to see one I couldn't fix by changing the LINQ expression. Both ESQL and L2E end up as CCTs, so the SQL generation pipeline is the same.
3 Comments
Godeke
My point about suboptimal queries is that I can do things in TSQL that I can't in LINQ to Entities. Take a tour on the Google with "parameter sniffing entities" for some examples (or, if you have the fix, please let me know and help all those others in need!) The general workaround I have found reliable is to use T-SQL to avoid the sniffing errors.
Craig Stuntz
T-SQL, yes. ESQL, no, for the most part. The question was LINQ vs. ESQL, not T-SQL.
Craig Stuntz
ESQL = "Entity SQL" (EF-specific query language). T-SQL = "Transact SQL" (SQL Server-specific query language). They are completely different, and both are different than LINQ to Entities.