Linq To SQL weirdness ??

lukky posted on Sunday, January 04, 2009

Hi,

I have to admit that I'm getting my feet into something for which I have very little knowledge.

In my desire to find a set of working templates to build my application based on CSLA 3.6, I have tested the CSLAContrib/CodeSmith templates (amongst others).

Those templates make use of L2S as the data layer, and this is the part where I get lost easily.

When one updates changes to a BO (by Calling Save() ), the DataPortal_Update() method attaches a new entity to the context. It then fills the fields of this new entity from the BO fields, and finally calls DataContext.SubmitChanges().

When I ran the code for the first time, I kept receiving a ChangeConflictException on the call to DataContext.SubmitChanges(). After a bit of investigation, and after setting the DataContext.Log to the Console, I found that it had to do with the "Update Check" property of each Entity member (available in the L2S Designer Property pane). It seems that I have to set this property to "Never" in order for the Update to work properly.

A quick look at the ProjectTracker project confirmed that this is exactly what needs to be done, as each Entity Member in the PT L2S model is set to "Never".

It so happens that my current project doesn't require any type of concurrency checking (last write wins), so this is all fine for me.

For future reference, because I'm sure to need this some day, could someone explain how we could achieve Optimistic Concurrency with L2S ?

Thank you

ajj3085 replied on Monday, January 05, 2009

Do you have any timestamp fields?    Also, you mention that your code uses Attach.  What does your call look like?  It sounds like by default that L2S uses optimistic concurrency, but it can use timestamps to tell if the record in the database has changed. See: http://msdn.microsoft.com/en-us/library/bb399373.aspx

So... what does your Attach statement look like, and what is the definition of your DTO class?

lukky replied on Monday, January 05, 2009

Hi,

No, I don't have any Timestamp field, at least not in the application I'm currently working on, which doesn't require concurrency checking. I'm only trying to understand better the use of Linq to SQL.

I don't know if you're familiar with the CSLAContrib CodeSmith templates. My code is based on those.

Here is for example the DataPortal_Update method for a BusinessBase object:

        protected override void DataPortal_Update()
        {
            bool cancel = false;
            OnUpdating(ref cancel);
            if (cancel) return;

            using (var mgr = ContextManager<CentreClient.DAL.vittoria_prodDataContext>
                        .GetManager(Database.vittoria_prod))
            {
                var data = new CentreClient.DAL.Customer()
                {
                    CUST_id = ReadProperty<int>(CustIdProperty)
                };
                mgr.DataContext.Customers.Attach(data);

                OnMemberReading(data);
                if (IsSelfDirty)
                {
                    data.CUST_NO = ReadProperty<string>(CustNoProperty);
                    data.CUST_pallet = ReadProperty<int>(CustPalletProperty);
                    data.CUST_acombacp = ReadProperty<int>(CustAcombacpProperty);
                    data.CUST_NAME = ReadProperty<string>(CustNameProperty);
                    data.CUST_shipdelay = ReadProperty<int>(CustShipdelayProperty);
                    data.CUST_segment = ReadProperty<int>(CustSegmentProperty);
                    data.CUST_noteclient = ReadProperty<string>(CustNoteclientProperty);
                    data.CUST_notecommande = ReadProperty<string>(CustNotecommandeProperty);
                    data.CUST_notefacture = ReadProperty<string>(CustNotefactureProperty);
                    data.CUST_noteboncommande = ReadProperty<string>(CustNoteboncommandeProperty);
                    data.CUST_notebilltransport = ReadProperty<string>(CustNotebilltransportProperty);
                    data.CUST_noteSales = ReadProperty<string>(CustNotesalesProperty);
                    data.CUST_groupid = ReadProperty<int>(CustGroupidProperty);
                    data.CUST_groupno = ReadProperty<string>(CustGroupnoProperty);
                    data.CUST_devise = ReadProperty<string>(CustDeviseProperty);
                    data.CUST_noteFacPopup = ReadProperty<string>(CustNotefacpopupProperty);
                    data.CUST_datecreated = ReadProperty<SmartDate>(CustDatecreatedProperty);
                    data.CUST_dateupdated = ReadProperty<SmartDate>(CustDateupdatedProperty);
                    data.CUST_taxgroup = ReadProperty<string>(CustTaxgroupProperty);
                    data.CUST_taxidFed = ReadProperty<string>(CustTaxidfedProperty);
                    data.CUST_taxidProv = ReadProperty<string>(CustTaxidprovProperty);
                    data.CUST_termcp = ReadProperty<int>(CustTermcpProperty);
                    data.CUST_sales = ReadProperty<int>(CustSalesProperty);
                    data.ACTIVE = ReadProperty<bool>(ActiveProperty);
                }
                OnMemberRead();

                mgr.DataContext.SubmitChanges();
                if (IsSelfDirty)
                {
                    LoadProperty<int>(CustIdProperty, data.CUST_id);
                }
            }//using

            OnUpdated();
        }

Sorry if this is a bit long.

So, we can see that we create a new entity, and attach it to the DataContext. This code is different than the ProjectTracker sample code, which looks like this:

protected override void DataPortal_Update()
    {
      using (var ctx = ContextManager<ProjectTracker.DalLinq.PTrackerDataContext>.GetManager(ProjectTracker.DalLinq.Database.PTracker))
      {
        // insert project data
        System.Data.Linq.Binary lastChanged = null;
        ctx.DataContext.updateProject(
          ReadProperty(IdProperty),
          ReadProperty(NameProperty),
          ReadProperty(StartedProperty),
          ReadProperty(EndedProperty),
          ReadProperty(DescriptionProperty),
          _timestamp,
          ref lastChanged);
        _timestamp = lastChanged.ToArray();
        // update child objects
        FieldManager.UpdateChildren(this);
      }
    }

Of course, both don't apply to the same database/table, so the fields are different, but we can see the different logic being used.

I'd like to understand what happens with both ways, and why one would chose one over the other. Maybe if you have some link on the subject.

My first impression is that in the first case, we assume that we work with a different DataContext, and need to attach the entity before applying the changes. In the PT sample, we seem to assume that we're using the same DataContext. Is this correct ? If so, does it mean that I need to code differently if I'm going to run the DataPortal on a remote machine than when I run it locally on the user's computer ?

I know I'm asking a lot of question. Blame it on my 3274th gray hair

Regards.

raz0rf1sh replied on Monday, January 05, 2009

Hi lukky!

We also ran into some weird issues ... which I talked about in my post http://forums.lhotka.net/forums/thread/29300.aspx.

We had to mark the update check to Never, we also had to allow NULL values, which don't really exist in our database, but we had to so we could reset certain values back to zero. And we had to do a null check when we did our Fetch. Everything works fine ... just seems hackish!

ajj3085 replied on Tuesday, January 06, 2009

Hmm, i don't see anything odd.  Maybe you do just need the update check set to Never.

lukky replied on Tuesday, January 06, 2009

Maybe, maybe not 

I wish I had time to investigate this.

Regards

greengumby replied on Tuesday, January 13, 2009

I had the same issues with LINQ and the update. Found out LINQ only updates fields that have changed.

For example

                     var data = new mm_V2.DAL.LINQ.Person.House() 
                     {
                          IDHouse = ReadProperty<Guid>(IDHouseProperty)
                     };
                     data.rowversion = _rowversion;
                     mgr.DataContext.Houses.Attach(data);

                     if (IsSelfDirty)
                     {
                       data.Description = ReadProperty<string>(DescriptionProperty);
                       data.active = ReadProperty<bool>(ActiveProperty);
       }

So what was happening is the data.active field was initialized to false and then my ActiveProperty
was false also.

When SubmitChanges was called LINQ looked at the field thought no change had happened and did not update this field. So this problem occured for me when setting data back to default values.

Got around it my reloading the original values from db

                var data = mgr.DataContext.Houses.Single
               (p => p.IDHouse == ReadProperty<Guid>(IDHouseProperty));
                data.rowversion = _rowversion;      
  OnMemberReading(data);

Im taking an extra hit to the db but its not a real db intensive section so I am not concerned.

 

lukky replied on Wednesday, January 14, 2009

Yes,

bool fields seem to be problematic when you have to Attach an entity back to the DataContext.

I ended up calling the DataContext.Attach(data, true);  overload. The "true" forces the DataContext to see the entity as dirty, so it includes all the fields in the query.

Regards.