none
Existing rows not update when I execute the query again RRS feed

  • Question

  • Hi,

    I've a readonly DataGridView on a form,
    this DataGridView should update every <n> seconds.

    at form level I've declared:
    MSGDataContext Db = new MSGDataContext();

    In the Load event of the form I use a code like this:

    Db.Log = Console.Out;
    var myStatus = Db.ExecuteQuery<STATO_INXINVINI>(SqlSelect);
    STATO_INXINVINIBindingSource.DataSource = myStatus;

    Insiede a timer I execute the query again and again but I've noticed that existings rows are NOT UPDATED with new values readed from the db. New rows instead are correctly added... looking at the Log the Select statement is executed every time.

    Is it possible to update existings rows without instancing a new DataContext before calling again the select ?

    I've noticed that there's a way to update the rows calling the resetbinding on the bindingsource but it executes a new select for each row so it's slow.

    Similar code without LINQ (using a DataTable) works correctly (it updates the rows on the grid) but I've already defined a datacomtext including the STATO table for other purposes and I prefer not to use a dataset only to solve this problem.

    Thank you very much every help is appreciated.

    Wednesday, November 18, 2009 10:24 AM

Answers

  • Hello Manuel,

     

    Welcome to LINQ to SQL forum!

     

    Damien is definitely right and I agree on his suggestions.  Additionally, for the cache and databinding of LINQ to SQL, I had some research in this thread, http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/022d72e2-92a1-4645-af1a-59dbcfd85fff.   

     

    Manuel, from your description, do you want to track the state of the retrieved entities?  You are using a Timer to query the database again and again and if the data are read only in your application, we can turn off the DataContext’s tracking service by

    ===============================================================================
    db.ObjectTrackingEnabled = false;
    ===============================================================================

     

    Or the DataContext may need to track other entities state, but if here the STATO_INXINVINI entities are read only, you can consider creating another custom data class to hold the data.  For example, it is a Person table with column ID and Name, so the custom class can be:

    ===============================================================================
        public class MyPerson

        {

            public int ID { get; set; }

            public string Name { get; set; }

    }
    ===============================================================================

    And the query can be:

    ===============================================================================

    var query = db.ExecuteQuery<MyPerson>("select ID, Name from [Person]");
    ===============================================================================

     

     

    Have a great day, everyone!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Manuel Marini Thursday, November 19, 2009 9:21 AM
    Thursday, November 19, 2009 6:53 AM
    Moderator

All replies

  • Manuel,

    I ran into a similar issue a while back. Say you read a row from a database using L2S. You update this row and save it back to the database. You then attempt to read it again from the database. L2S will not read the new row from the database. It will detect it already has the row in its cache and use that one. This is despite the fact that the row in cache is out of date. Assuming the behavior I just described is correct, and I believe it is, IMO, this is incredibly bad design on Microsoft's part. I see no justification for it. L2S should at least be smart enough to know that the row it has in its cache is now dirty and should be replaced.

    The database context object has a Refresh method that is designed to deal with situations like this.

    Randy
    Wednesday, November 18, 2009 12:56 PM
  • There is a very good reason for it.

    LINQ to SQL like many ORM's uses the Identity Map pattern to ensure you only get a single instance of an entity for a given table/primary key within the current unit of work (a data context).

    These entities may have unsaved changes, be in the middle of an operation, be current displayed for viewing/editing or partaking in a new association.

    The act of running a database query should not update these objects as it would not be possible to resolve ambiguities or these issues in a reliable automatic manner.

    You have two options depending on whether you want to keep currently loaded entities.

    If you do not, throw away the current datacontext and start a new one to establish a whole new unit of work.

    If you do then you can call Refresh and specifying how you want these ambiguities resolved.

    [)amien
    Thursday, November 19, 2009 4:18 AM
    Moderator
  • Hello Manuel,

     

    Welcome to LINQ to SQL forum!

     

    Damien is definitely right and I agree on his suggestions.  Additionally, for the cache and databinding of LINQ to SQL, I had some research in this thread, http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/022d72e2-92a1-4645-af1a-59dbcfd85fff.   

     

    Manuel, from your description, do you want to track the state of the retrieved entities?  You are using a Timer to query the database again and again and if the data are read only in your application, we can turn off the DataContext’s tracking service by

    ===============================================================================
    db.ObjectTrackingEnabled = false;
    ===============================================================================

     

    Or the DataContext may need to track other entities state, but if here the STATO_INXINVINI entities are read only, you can consider creating another custom data class to hold the data.  For example, it is a Person table with column ID and Name, so the custom class can be:

    ===============================================================================
        public class MyPerson

        {

            public int ID { get; set; }

            public string Name { get; set; }

    }
    ===============================================================================

    And the query can be:

    ===============================================================================

    var query = db.ExecuteQuery<MyPerson>("select ID, Name from [Person]");
    ===============================================================================

     

     

    Have a great day, everyone!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Manuel Marini Thursday, November 19, 2009 9:21 AM
    Thursday, November 19, 2009 6:53 AM
    Moderator