none
Extension Method and Expression Tree RRS feed

  • Question

  • Hi,

    Is there some way to create an extension method to include the nolock query hint over a table in entity framework ?

    I know I can include nolock using an interceptor, but doing this I haven't much control over the nolock. I would like to be able to add the nolock over each table.

    For example, it would be something like this:

    var result= (from x in ct.customers.nolock()).tolist();

    I know how to create the extension method, but I don't know how, inside the method, to use the expression tree to generate the nolock query hint. I could add some kind of "flag" to the expression tree, but I think I can't access the expression tree in the interceptor, the interceptor already receives the command.

    Is there a solution for this ?

    Thanks !


    Dennes [http://bufaloinfo.cloudapp.net] Inscreva-se em meu treinamento on-line de T-SQL - [http://bufaloinfo.cloudapp.net/Cursos/linguagemsql.aspx]

    Sunday, May 29, 2016 9:11 AM

All replies

  • Hi Dennes,

    >>Is there a solution for this ?

    As far as I know, it seems that EF Provider doesn't provide a method to generate a nolock statement. I think there are two workarounds to achieve it.

    1. custom a Command Interceptor, like below:

    public class NoLockInterceptor : DbCommandInterceptor
    {
        private static readonly Regex _tableAliasRegex = 
            new Regex(@"(?<tablealias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))", 
                RegexOptions.Multiline | RegexOptions.IgnoreCase);
     
        [ThreadStatic]
        public static bool ApplyNoLock;
     
        public override void ScalarExecuting(DbCommand command, 
            DbCommandInterceptionContext<object> interceptionContext)
        {
            if (ApplyNoLock)
            {
                command.CommandText = 
                    _tableAliasRegex.Replace(command.CommandText, 
                    "${tableAlias} WITH (NOLOCK)");
            }
        }
     
        public override void ReaderExecuting(DbCommand command, 
            DbCommandInterceptionContext<dbdatareader> interceptionContext)
        {
            if (ApplyNoLock)
            {
                command.CommandText = 
                    _tableAliasRegex.Replace(command.CommandText, 
                    "${tableAlias} WITH (NOLOCK)");
            }
        }
    }

    Usage:

    DbInterception.Add(new NoLockInterceptor());

    Fore more information, please refer to:

    http://www.gitshah.com/2014/08/how-to-add-nolock-hint-to.html

    2. Use transaction

    //declare the transaction options
    var transactionOptions = new System.Transactions.TransactionOptions();
    //set it to read uncommited
    transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
    //create the transaction scope, passing our options in
    using (var transactionScope = new System.Transactions.TransactionScope(
        System.Transactions.TransactionScopeOption.Required, 
        transactionOptions)
    )
    
    //declare our context
    using (var context = new MyEntityConnection())
    {
        //any reads we do here will also read uncomitted data
        //...
        //...
        //don't forget to complete the transaction scope
        transactionScope.Complete();
    }

    For more information, please refer to:

    http://stackoverflow.com/questions/926656/entity-framework-with-nolock

    Note:
    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. 

    Microsoft does not control these sites and has not tested any software or information found on these sites;Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.

    There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 30, 2016 1:24 AM
    Moderator