none
with(nolock) statement in LINQ to SQL

    Question

  • Hi,

    First steps with LINQ to SQL and I'm finding some issues when trying to adapt WITH(NOLOCK) performance to my LINQ sintax.

    Researching on the internet, I found a little of controversy and don't know what solution fits better.

    My example is:

    I want to select values from the tables v_gestione, d_product_price and d_vettura and in any case want to block those tables.

     var requestInfo = (from tour in db.v_gestione
                                   join price in db.d_product_price on tour.price_id equals price.price_id
                                   join carWay in db.d_vettura on price.vettore_service_id equals carWay.vettore_service_id
                                   select new
                                   {
                                       tour.price_id,
                                       tour.totPax,
                                       tour.booking_dateTour,
                                       carWay.vettura_price,
                                       carWay.vettura_id
                                   });

                foreach (var request in requestInfo)
                {
                    tourInfo oTourInfo = new tourInfo( tour.price_id, tour.totPax, tour.booking_dateTour));
                    LinkedTours.Add(oTourInfo);
                }

    Would the System.Transactions.IsolationLevel.ReadUncommitted statement be a hint?

    Thanks very much in advance.

    Tuesday, February 21, 2012 11:16 PM

Answers

  •  

    In order to tell you what YourType is you need to post the complete Linq query for it will determine the type.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by blinkins0n Friday, February 24, 2012 5:05 PM
    Wednesday, February 22, 2012 7:03 PM
  • In the code you wrote "YourType" is an anonymous type (http://msdn.microsoft.com/en-us/library/bb397696.aspx). But I also noticed that you take this anonymous type and construct an object of type tourInfo. So your code can be changed as follows:

    IEnumerable<tourInfo> requestInfo;
    
    using (var t = new TransactionScope(.... System.Transactions.IsolationLevel.ReadUncommitted }))
    {
                 requestInfo = (from tour in db.v_gestione                      
                               select new tourInfo( tour.price_id, tour.totPax, tour.booking_dateTour)).Take(1).ToArray();    
    }
    
    foreach (var oTourInfo in requestInfo)
    {
                    LinkedTours.Add(oTourInfo);
    }




    Thursday, February 23, 2012 9:44 AM
  • Hi blinkins0n;

    The only way to assign an Anonymous type to a variable name is to use var. This is because Anonymous type are assign a type name at run time and so you will not know it while you are writing code. Also Anonymous types are not very easy to work with outside of the function / method it was created in because you will need to use .Net Reflections classes to find out what is in the Anonymous type. Therefore you are better off creating a small class to hold the result, for example.

    public class TourInfo 
    {
        public int PriceId { get; set; }
        public string TotPax { get; set; }
        public DateTime BookingDateTour { get; set; }    
    }
    
    // ...
    
    List<TourInfo> requestInfo;
    
    using (var t = new TransactionScope(System.Transactions.IsolationLevel.ReadUncommitted }))
    {
        requestInfo = (from tour in db.v_gestione
                       select new TourInfo()
                       {
                           PriceId = tour.price_id,
                           TotPax = tour.totPax,
                           BookingDateTour = tour.booking_dateTour   
                       }).ToList();    
    }


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by blinkins0n Friday, February 24, 2012 5:05 PM
    Thursday, February 23, 2012 4:13 PM
  • If you want to use a variable outside the scope it's defined, you cannot use an anonymous type. In that case you would have to define your own class.

    Actually an anonymous type would define it's own class as well, but the C# compiler hides that implementation detail from you (or in other words save you the trouble of definining the class yourself and does it for you). 

    • Marked as answer by blinkins0n Friday, February 24, 2012 5:05 PM
    Thursday, February 23, 2012 4:36 PM
  • Personally I don't think it's more complicated. The class you define doesn't have to have any code. Just some properties declared. So a very simple class would suffice.

    You are right, the scope should be as short as possible.

    But in your specific example you are using Take(1). Which means you are returning 1 object, then you add that object to a collection (I am assuming something like List<tourInfo>). Adding one object to a collection is not a big deal. The impact on the application if the scope lifetime was a little bit longer for the few CPU cycles need to add that object to the collection, it's not a bad idea.

    So it really depends on what you are doing. If it's a long operation, it's a bad idea. 

    • Marked as answer by blinkins0n Friday, February 24, 2012 3:23 PM
    Friday, February 24, 2012 1:14 AM
  • Hi blinkins0n;

    Creating a class to hold the result is not complicated, see my last post, that is all that is needed.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by blinkins0n Friday, February 24, 2012 3:23 PM
    Friday, February 24, 2012 2:14 PM

All replies

  • yes, read uncommitted is equivalent to having NOLOCK on all select statements within a transaction.
    Tuesday, February 21, 2012 11:40 PM
  • That's great.

    But I have two more questions and these ones maybe more c# oriented.

    In the code below:

            using (var t = new TransactionScope( ..... System.Transactions.IsolationLevel.ReadUncommitted }))
            {

                var requestInfo = (LINQ statement......);
            }

             foreach (var request in requestInfo)
             {
                    tourInfo oTourInfo = new tourInfo( tour.price_id, tour.totPax, tour.booking_dateTour));
                    LinkedTours.Add(oTourInfo);
             }

          

            1)  I guess the TransactionScope will be only wrapping the "LINQ query" and never the "FOR" statement reading the values, isn't?

            2)  And second question: how can I declare/initialize "requestInfo" var in order to be accesed both within the "LINQ select" and within the "FOR" structure?

    Thanks again and have nice day.

    Wednesday, February 22, 2012 12:06 AM
  • IEnumerable<YourType> requestInfo;
    
    using (var t = new TransactionScope( ..... System.Transactions.IsolationLevel.ReadUncommitted }))
    {
                requestInfo = (LINQ statement......).ToArray();
    }
    
    foreach (var request in requestInfo)
    {
                    tourInfo oTourInfo = new tourInfo( tour.price_id, tour.totPax, tour.booking_dateTour));
                    LinkedTours.Add(oTourInfo);
    }
    Basically declaring requestInfo outside the transaction scope allows you to use the variable in the foreach statement after leaving the scope. Using ToArray() will make LINQ to SQL perform the actual SELECT query on the SQL server while still within the scope. However, you can't use the var keyword here because you are not initializing the variable on the same line it's declared. This also means you can't use an anonymous type.


    Wednesday, February 22, 2012 12:40 AM
  • Sorry, but I don't completely understand what kind of "YourType" I should be using.

    requestInfo will be a simple list of records retrieved from a LINQ query.

    Am I make it too complicated?

    Would it be so bad to include the "FOR" statement within the TransactionScope?

    Thanks again!

    Wednesday, February 22, 2012 8:41 AM
  •  

    In order to tell you what YourType is you need to post the complete Linq query for it will determine the type.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by blinkins0n Friday, February 24, 2012 5:05 PM
    Wednesday, February 22, 2012 7:03 PM
  • Hi Fernando,

    Here it is the whole code. How Can I know the type from the Query?

    IEnumerable<YourType> requestInfo;
    
    using (var t = new TransactionScope( ..... System.Transactions.IsolationLevel.ReadUncommitted }))
    {
                 requestInfo = (from tour in db.v_gestione                      
                               select new
                               {
                                   tour.price_id,
                                   tour.totPax,
                                   tour.booking_dateTour                          
                               }).Take(1);     } foreach (var request in requestInfo) { tourInfo oTourInfo = new tourInfo( tour.price_id, tour.totPax, tour.booking_dateTour)); LinkedTours.Add(oTourInfo); }

    Thanks!

    Wednesday, February 22, 2012 10:29 PM
  • In the code you wrote "YourType" is an anonymous type (http://msdn.microsoft.com/en-us/library/bb397696.aspx). But I also noticed that you take this anonymous type and construct an object of type tourInfo. So your code can be changed as follows:

    IEnumerable<tourInfo> requestInfo;
    
    using (var t = new TransactionScope(.... System.Transactions.IsolationLevel.ReadUncommitted }))
    {
                 requestInfo = (from tour in db.v_gestione                      
                               select new tourInfo( tour.price_id, tour.totPax, tour.booking_dateTour)).Take(1).ToArray();    
    }
    
    foreach (var oTourInfo in requestInfo)
    {
                    LinkedTours.Add(oTourInfo);
    }




    Thursday, February 23, 2012 9:44 AM
  • It makes sense.

    Perfect.

    But in the event I had no tourInfo class? How could I be using the anonymous type?

    Thanks!

    Thursday, February 23, 2012 3:15 PM
  • Hi blinkins0n;

    The only way to assign an Anonymous type to a variable name is to use var. This is because Anonymous type are assign a type name at run time and so you will not know it while you are writing code. Also Anonymous types are not very easy to work with outside of the function / method it was created in because you will need to use .Net Reflections classes to find out what is in the Anonymous type. Therefore you are better off creating a small class to hold the result, for example.

    public class TourInfo 
    {
        public int PriceId { get; set; }
        public string TotPax { get; set; }
        public DateTime BookingDateTour { get; set; }    
    }
    
    // ...
    
    List<TourInfo> requestInfo;
    
    using (var t = new TransactionScope(System.Transactions.IsolationLevel.ReadUncommitted }))
    {
        requestInfo = (from tour in db.v_gestione
                       select new TourInfo()
                       {
                           PriceId = tour.price_id,
                           TotPax = tour.totPax,
                           BookingDateTour = tour.booking_dateTour   
                       }).ToList();    
    }


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by blinkins0n Friday, February 24, 2012 5:05 PM
    Thursday, February 23, 2012 4:13 PM
  • If you want to use a variable outside the scope it's defined, you cannot use an anonymous type. In that case you would have to define your own class.

    Actually an anonymous type would define it's own class as well, but the C# compiler hides that implementation detail from you (or in other words save you the trouble of definining the class yourself and does it for you). 

    • Marked as answer by blinkins0n Friday, February 24, 2012 5:05 PM
    Thursday, February 23, 2012 4:36 PM
  • Ok,

    I see that the only way to use the variable outside the scope is using a defined class.

    My question is: since defining this new extra class makes the implementation a bit more "complicated", what would you do in my situation? I'm just trying to simulate the former with(nolock) execution when querying some tables.

    Is a bad a choice using the var within the scope? (I assume the scope should be as short as possible).

    Thanks both for sharing your knowledge.

    Thursday, February 23, 2012 11:36 PM
  • Personally I don't think it's more complicated. The class you define doesn't have to have any code. Just some properties declared. So a very simple class would suffice.

    You are right, the scope should be as short as possible.

    But in your specific example you are using Take(1). Which means you are returning 1 object, then you add that object to a collection (I am assuming something like List<tourInfo>). Adding one object to a collection is not a big deal. The impact on the application if the scope lifetime was a little bit longer for the few CPU cycles need to add that object to the collection, it's not a bad idea.

    So it really depends on what you are doing. If it's a long operation, it's a bad idea. 

    • Marked as answer by blinkins0n Friday, February 24, 2012 3:23 PM
    Friday, February 24, 2012 1:14 AM
  • Hi blinkins0n;

    Creating a class to hold the result is not complicated, see my last post, that is all that is needed.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by blinkins0n Friday, February 24, 2012 3:23 PM
    Friday, February 24, 2012 2:14 PM
  • Thanks Fernando and Sherif.

    I get the point and I'll be using different methods depending on the situation.

    Have a nice day.

    Friday, February 24, 2012 3:24 PM
  •  

    Not a problem, glad to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, February 24, 2012 3:36 PM