none
ID based Linq queries makes repeated Database requests, instead of using DataContext RRS feed

  • Question

  • I am using ASP.NEt 3.5 with SP1 with Visual Studio 2008

    As suggested from

    http://blogs.msdn.com/dinesh.kulkarni/archive/2008/08/10/net-fx-3-5-sp1-two-perf-improvements-linq-to-objects-and-linq-to-sql.aspx

    the queries requrested using Single(ID) or SingleOrDefault(ID), First(ID) or FirstOrDefault(ID), should maintain the entities on the DataContext and repeated requests should be fetched from Datacontext itself, instead of making repeated trip to Database.

    But unfortunately its not. My query looks like this

    public static readonly Func<DataContext, int, Entity> qGetById =

    CompiledQuery.Compile<DataContext, int, Entity>

    ((DataContext oDb, int nID) => oDb.Entities.FirstOrDefault(item => item.PrimaryKey== nID));

    public virtual Entity GetById(int nId){return qGetById (MyDataContext, nId);}


    everytime I do GetById, it goes to DB for the same Entity.  The ObjectTracking is Enabled and the DBML looks fine.

    Can anyone please help me on this. as this is urgent.

     
    Thanks
    Vij

    Saturday, January 10, 2009 8:06 AM

All replies

  • LINQ to SQL implements this by recognizing specific query patterns.  My guess is that the compiled query form of the query is not being recognized.
    Wayward LINQ Lacky
    Monday, January 12, 2009 8:56 PM
    Moderator
  • Vij -- can you test to see if the problem still occurs without CompiledQuery? I think it will, but I want to confirm that is the case for you. We are aware of a bug with this functionality that occurs with First and Single (and FirstOrDefault/SingleOrDefault, I think, but I'm not 100% sure about that). So you can test by removing the CompiledQuery just to see if that has any effect on it, and also even with CompiledQuery, use Where to apply your query condition, instead of First/Single. If the query is producing a single result anyway, you will be able to get the basically the same result without using First.

    Thanks,
    Sarah Parra
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 12, 2009 9:48 PM
    Moderator
  • Hi Sarah

    I tried all possible options (First(), Single(), FirstOrDefault(), SingleOrDefault()) even with Compiled or Non-Compiled query. But nothing worked out.

    The only way I resolved this is to manually check the cache everytime before i make a request. Not efficient but solved for single Entity requests.

    public TEntity GetFromDataContextCache(TId nID)

    {

    //Extract from http://www.eggheadcafe.com/software/aspnet/32051697/cache-in-dlinq.aspx

    const BindingFlags FLAGS = BindingFlags.Instance |

    BindingFlags.Public | BindingFlags.NonPublic;

    object services = MyDataContext.GetType().GetProperty("Services",

    FLAGS).GetValue(MyDataContext, null);

    object[] args = { MyDataContext.Mapping.GetMetaType(typeof(TEntity)), new object[] { nID } };

    Type[] paramTypes = { typeof(MetaType), typeof(object[]) };

    return (TEntity)services.GetType().GetMethod("GetCachedObject",

    FLAGS, null, paramTypes, null).Invoke(services, args);

    }

    Thanks
    Vijai

    Tuesday, January 13, 2009 8:07 AM
  • Vij -- did you try just executing the same query, but use ToList() instead of Single/First? If you are querying on the primary key anyway, there will only be one result.

    Using your original query as an example, I mean something like this:

    public static readonly Func<DataContext, int, Entity> qGetById =

    CompiledQuery.Compile<DataContext, int, Entity>

    ((DataContext oDb, int nID) => oDb.Entities.Where(item => item.PrimaryKey== nID));

    public virtual Entity GetById(int nId){return qGetById (MyDataContext, nId).ToList()[0];}

    Thanks,
    Sarah




    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 14, 2009 5:00 PM
    Moderator
  • Hi Sarah

    I just modified the query as per your request and it still has repeated database requests

    var oDataContext = GetDataContext();

    Func<RatingsDataContext, int, IEnumerable<User>> qGetById =

    CompiledQuery.Compile<RatingsDataContext, int, IEnumerable<User>>(

    (RatingsDataContext oDb, int nID) => oDb.Users.Where(item => item.UserId == nID));

    var oUser1 = qGetById(oDataContext , 471).ToList()[0];

    oUser1 = qGetById(oDataContext , 471).ToList()[0];

    oUser1 = qGetById(oDataContext , 471).ToList()[0];

    oUser1 = qGetById(oDataContext , 471).ToList()[0];

    Did you have some sample code where it actually uses the cache instead of repeated DB requests. Because from all the samples i tried nothing seems to work/

    Thanks
    Vijai

    Thursday, January 15, 2009 1:43 AM
  • Hi Vij,

    Thanks for trying that out. I verified what you are seeing. Here is what I am finding:

    (1) With CompiledQuery, the cache lookup never occurs regardless of how I execute the query. I always an actual database hit with Where/ToList or Single/First.
    (2) Without CompiledQuery, the cache lookup still doesn't happen with Single/First.
    (3) Without CompiledQuery, using Where/ToList does work.

    The last case was fixed in SP1. We have an existing bug tracking the second case (http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=362313). I have now filed another bug to track the first case, and we will investigate it along with the existing bug.

    Thanks,
    Sarah


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 19, 2009 8:42 PM
    Moderator
  • Hi Sarah, is there any update on this bug?  I cannot seem to find it on connect, and I have not seen any mention of it in the changes and fixes for .net 4.0

    This is a major bug to any application that needs to utilize the benefits of both complied queries AND identity caching!  I read that 

    "Pre-compiled queries will not return objects from the identity cache. For more information about pre-compiled queries, see CompiledQuery and How to: Store and Reuse Queries (LINQ to SQL)."

    From this page, http://msdn.microsoft.com/en-us/library/dd627203(VS.100).aspx, so I really need to know whether a fix is in the works or not.

    Thanks,
    Dan
    Monday, September 14, 2009 4:56 PM