CODEWITHSUNDEEP
c#sql-serverentity-framework
Abolfazl Pourmohammad
Abolfazl Pourmohammad

Reputation: 21

How to force Linq to generate sql satements with exact with(nolock) statement

I'm using IsolationLevel ReadUncommited to create a with(nolock) SQL statement but linq does not generate nolock in SQL queries.

I need a way to generate with(nolock) on liq generated SQL statements. My code is

    var transactionOptions = new System.Transactions.TransactionOptions();

    transactionOptions.IsolationLevel=
    System.Transactions.IsolationLevel.ReadUncommitted;

    using (var transactionScope = new System.Transactions.TransactionScope(
    System.Transactions.TransactionScopeOption.Required,
    transactionOptions)
    )
    {
        var x=from b in TBLAgents where b.AgentID!=2 select b;
        x.Dump();
        transactionScope.Complete();
    }

but it generates SQL query without block statement

-- Region Parameters
    DECLARE @p0 Int = 2
    -- EndRegion
    SELECT [t0].[AgentID], [t0].[ParentID], [t0].[AgentType], [t0].[AgentName], [to].        [AgentUsername], [t0].[AgentPassword], [t0].[CreateDate], [t0].[Status], [t0].[SecurityPublicKey], [t0].[SecurityPrivateKey], [t0].[OfflineProposalDetailInfo], [t0].[IsPlatformService], [t0].[PlatformServiceURL], [t0].[ServiceClassName], [t0].[PlatformServiceUserId], [t0].[PlatformServiceUserName], [t0].[PlatformServicePassword], [t0].[BranchId], [t0].[OfflineMailingDisabled], [t0].[WebApiKey], [t0].[AlternateId]

FROM [TBLAgent] AS [t0] WHERE [t0].[AgentID] <> @p0

Upvotes: 1

Views: 100

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89166

A READ_UNCOMMITTED transaction and a NOLOCK hint are alternative mechanisms for requesting dirty reads. NOLOCK has no effect if you're running in a READ_UNCOMMITTED transaction.

Upvotes: 4

Related Questions