In what situations should I use Entity SQL?

Question

I was wondering whether there are situation that's more advisable to use ESQL?

Generally, I find ESQL frustrating (specially with all the special cases where you need to fully qualify an entity type) & probably anything done using ESQL can be done through SQL or LINQ.

But I was wondering of situations where ESQL is a better fit for a solution or would have a competitive edge over using SQL or LINQ (either easier/faster to code, or better performance, etc.)

So, what's the compromise here? When is it better to use each one of the three approaches in querying over EF4?

Answer 1

I find that ESQL to be good for edge cases, for example:

1. Where it's just really hard to express something in LINQ.
2. For building very dynamic searches.
3. If you want to use a database specific function that is exposed by the provider you are using.

Also, if you know Entity SQL, you will be able to express QueryViews and Model-Defined Queries.

Answer 2

I came across these pretty similar questions (which didn't show up when I typed mine, earlier) on stack over flow, I guess they have deeper discussions (though T-SQL is not mentioned a lot, but it's kinda pretty obvious though):

• Linq to Entities vs ESQL - Stack Overflow
• Entity framework entity sql vs linq to entities

Note that the two questions are a bit "old" so you might validate some of data based on your current understanding of EF4