I do not seem to get LINQ2SQL queries back with mini-profiler?

Question

I have a 2 tier web application. DataAccess and WebSite.

So in my dataContext.cs on the DataAccess tier I added the wrapper...

  //The autogenreated context when I made the linq2sql class
  public static MyDataContext DataContext
    {
        get
        {
            //We are in a web app, use a request scope
            if (HttpContext.Current != null)
            {
                if (HttpContext.Current.Items["dc"] == null)
                {
                    MyDataContext dc = new MyDataContext ();
                    HttpContext.Current.Items["dc"] = dc;
                    return dc;
                }
                else
                    return (MyDataContext )HttpContext.Current.Items["dc"];
            }
            else
            {
                if (dataContext == null)
                    dataContext = new MyDataContext ();



                return dataContext;
            }
        }
    }

    //the method I added to the autogenreated contex in 
    //an attempt to wrap the profiler around it
    public static MyDataContext Get()
    {
        var sqlConnection = new MyDataContext().Connection;
        var profiledConnection = new StackExchange.Profiling.Data.ProfiledDbConnection(sqlConnection, MiniProfiler.Current);

        return new MyDataContext(profiledConnection);
    }

So this is what the profileConnection looks like when it gets called but before the return New MyDataContext(porofiledConnection)

and in my business logic also in the DataAccess tier I made sure that the db context is all created with db = MyDataContext.Get() in stead of db = new MyDataContext();

public class MyOrders(){
  private static  MyDataContext db = MyDataContext.Get();

  public static List<model> GetOrderHistory(){
      var = db.MyStoredProcedure(args) //Inspecting here before execution
      //proces result and return list of model
      }

 }

Now, on some pages I used to get SQL lines and I could click on them and inspect them. But after I browsed the site it just shows this - no SQL lines any more? Like this page just randomly shows me SQL duplication- But if I reload it is gone.

And on this page that I never ran with the profiler before that has loading time issues I cannot identify the SQL it used.

Did I miss something? Is the SQL cached? I always want to see the SQL even if Linq2Sql caches it or whatever. What did I do wrong?

Answer 1

You have a static data context in your MyOrders class. DataContext has an internal cache inside to track changes of entities and avoid round trip to database in one business transaction. Keeping it as static, means internal cache will be increasing for the time being and is not released properly. This may be the reason of disappeared queries in profiler. Also you may face a problem when multiple users will access the same context from multiple threads, and probably memory leaks.

A note from MSDN:

In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.

one more:

Do not try to reuse instances of DataContext. Each DataContext maintains state (including an identity cache) for one particular edit/query session. To obtain new instances based on the current state of the database, use a new DataContext.

More details you can find in the Rick Strahl's article Linq to SQL DataContext Lifetime Management.