How can we improve Entity Framework Core?

Ability to specify query and table hints in SQL Server

Table lock, update lock ( WITH(UPDLOCK) / FOR UPDATE ).
LockMode something like NHibernate.

Also should support controlling parameter sniffing

http://stackoverflow.com/q/9890699/85196

436 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Usa*UsaUsa*Usa shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    12 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • AlvaroAlvaro commented  ·   ·  Flag as inappropriate

        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.

      • Diego VegaAdminDiego Vega (Program Manager, Microsoft Entity Framework) commented  ·   ·  Flag as inappropriate

        @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(
        DbCommand command,
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        command.CommandText = command.CommandText +
        " OPTION (OPTIMIZE FOR UNKNOWN)";
        base.ReaderExecuting(command, interceptionContext);
        }
        }

        Usage is:

        DbInterception.Add(new SimpleOptimizeForUnknownInterceptor());

        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 VorsterMarius Vorster commented  ·   ·  Flag as inappropriate

        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.

      • BGBG commented  ·   ·  Flag as inappropriate

        There are many scenarios where parameter sniffing in SQL server creates serious performance problems. This is very problematic with many EF generated queries, since there is no control that allows you to tell the database optimizer not to use the parameter values or cache a plan. A simple mechanism to hint OPTION (RECOMPILE) on the generated queries would solve most of these issues.

        A more sophisticated solution that would allow the generated sql to leverage (Optimize for Unknown) would also be useful.

        Parameter sniffing and the related performance problems are a very prevalent issue with SQL server and if you have ever run a real production app you will encounter it time and time again. I'm shocked there is still no workaround in EF (besides creating plan guides in the DB which are very fragile and impossible to manage in a dynamic filter scenario)

        If I'm missing another workaround please advise.

      • EricEric commented  ·   ·  Flag as inappropriate

        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 PiazzaEric Piazza commented  ·   ·  Flag as inappropriate

        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 HuangXiaodong Huang commented  ·   ·  Flag as inappropriate

        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 OneaCosmin Onea commented  ·   ·  Flag as inappropriate

        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.

      • ShlomiShlomi commented  ·   ·  Flag as inappropriate

        until then - you can use TransactionScope with ReadUncommitted isolation level.

      Feedback and Knowledge Base