Ability to specify NoLock and other query hints
Table lock, update lock ( WITH(UPDLOCK) / FOR UPDATE ).
LockMode something like NHibernate.
Also should support controlling parameter sniffing
I need this option to to a pesismistic concurrency control, and it would be very useful if I can use the UPDLOCK in the related entities that I need to load in the include method.
@Marius Vorster: Thanks for the feedback. This is a feature we would like to add in the future. In EF7 we are laying off some of the necessary infrastructure in the form of query annotations, and we would accept a contribution in this area.
In the meanwhile for EF6, at least for very simple cases it is possible to append OPTIONS() at the end of queries using a query interceptor, e.g.:
class SimpleOptimizeForUnknownInterceptor : DbCommandInterceptor
public override void ReaderExecuting(
command.CommandText = command.CommandText +
" OPTION (OPTIMIZE FOR UNKNOWN)";
Of course you will want to add some logic to control when this transformation applies, e.g. add a bool flag to your custom DbContext and then check for the state of that flag inside the interceptor.
Marius Vorster commented
So we can do some query hints from TransactionScope but there is no way to provide 'Optimize for' hints (Parameter sniffing configuration). I have a case in production now where I expect the big standard deviation (Random slow queries) is due to bad parameter sniffing and my hands are tied. Can we please give it priority to include these configurations in Entity Framework in the future.
I have a dynamic query that changes the number of joins based upon the number of keywords passed in and the query timesout...it never completes via EF. Without even specifying an option the same SQL running in SSMS executes in 3 seconds. We must have the ability to pass query hints or this ORM like many others will be useful only in silo projects with small databases. We need a solution for real world problems.
Eric Piazza commented
Need hints to control parameter sniffing, we have a query that takes 0 seconds with proper index usage, but the EF query uses a bad execution plan and it takes 20 seconds!
Xiaodong Huang commented
Without table hints support, it is almost impossible to write a high concurrrent service without resorting to stored procedure which is what we want to avoid by using EF.
Cosmin Onea commented
I think this is so important to have. I have a long running workflow and when a new message comes to it I need to lock it for a bit. I can query and use change tracker to set the entity as modified and save changes but that requires me to work around the problem instead of having that out of the box.
until then - you can use TransactionScope with ReadUncommitted isolation level.