Do Compiled Queries Cache?

Question

Much of my application uses complied queries to retrieve data. In these queries I'll often refer to the current user. I'm noticing that if a user, B, logs in after another user, A, then user B will see user A's information.

I have queries much like this all through out the application

public static Func<DataContext, MyRecord> CurrentUserRecords =
            CompiledQuery.Compile<DataContext, MyRecord>(
                (DataContext db) =>
                    (from r in db.MyRecords
                     where
                        r.User == User.Current 
                     select r).SingleOrDefault());

User.Current is a static property that changes depending on who's logged in.

public static User Current 
{
    get { return MyBase<User>.Get((int)(HttpContext.Current.Session["CurrentUserID"] ?? 0)); }
}

When I login for the first time with User A, the above compiled query returns User A's records. It follows that User.Current also returns the proper reference to User A. However, when I log in as User B, the above compiled query still returns User A's records, despite the fact that User.Current is returning a reference to User B.

I ran Profiler for SQL Server, and noticed when the compiled query was executed the generated TSQL referenced User A's ID both times.

So my question is this:

Do compiled queries somehow cache?

If so, what is there life span, and can I control it?

Is referencing a "current user" in a compiled query bad design for an ASP.net application?

Thanks all!

Answer 1

You need to allow a string parameter in the compiled query. Otherwise it will resolve the string's value during .Compile(). Try this:

public static Func<DataContext, string, MyRecord> UserRecordByParam =
  CompiledQuery.Compile<DataContext, string, MyRecord>
(
  (DataContext db, string UserName) =>
   db.MyRecords.Where( r => r.User == UserName ).SingleOrDefault()
);

public static Func<DataContext, MyRecord> CurrentUserRecord =
  (DataContext db) => UserRecordByParam(db, User.Current);