Can't set entity field to null for UPDATE

Posts   
 
    
mlc
User
Posts: 2
Joined: 21-Jun-2007
# Posted on: 21-Jun-2007 14:36:04   

Hi, we've recently updated from LLBLGen 1.x to LLBLGen 2.0, and I've found a difference in behavior. In LLBLGen 2.0, when you set a field null on a table-based entity, the field is not set and IsChanged remains false.

I noticed the thread http://llblgen.com/tinyforum/Messages.aspx?ThreadID=10019&StartAtMessage=0, but this appears to be different. I am using a table-based entity, adapter templates, and the SourceColumnIsNullable flag is true. Database is SQL Server 2005.

Here's the test code to reproduce.

        [Test]
        public void SetFieldToNull()
        {
            // set cost method to A
            {
                AccountEntity acct = new AccountEntity(0);
                adapter.FetchEntity(acct);
                acct.CostMethod = "A";
                adapter.SaveEntity(acct);
            }

            // now set to null
            // in LLBLGen 1 this works, but in LLBLGen 2 you need to add either one of the commented-out lines below
            {
                AccountEntity acct = new AccountEntity();
                acct.AccountID = 0;
                acct.IsNew = false;
//              acct.CostMethod = "";
                acct.CostMethod = null;
//              acct.Fields["CostMethod"].IsChanged = true;
                Assert.IsTrue(acct.Fields["CostMethod"].IsChanged); // this assertion will fail
                adapter.SaveEntity(acct);
            }

            // now check - should be null (or empty since that is the Type default value returned by the property)
            {
                AccountEntity acct = new AccountEntity(0);
                adapter.FetchEntity(acct);
                Assert.AreSame(string.Empty, acct.CostMethod); // if we comment out the assertion above, all of these would fail
                Assert.AreSame(null, acct.Fields["CostMethod"].CurrentValue);
                Assert.IsTrue(acct.Fields["CostMethod"].IsNull);
            }
        }

Here is the defintion of Account.CostMethod:

base.AddElementFieldMapping( "AccountEntity", "CostMethod", "CostMethod", [b]true[/b], (int)SqlDbType.Char, 1, 0, 0, false, "", null, typeof(System.String), 46 );

The same behavior exists regardless of whether I set the property to null, or if I use SetNewFieldValue(...,null).

One last note - we are using an add-in that does not expose our properties as nullable types, for backwards compatibility with our code which was based on LLBLGen 1.x. Not sure if this is related.

I noticed in the LLBLGen code (thanks Reflector!) that the logic in FieldUtilities.DetermineIfFieldShouldBeSet() has changed from 1.x to 2.0. Actually 1.x did not have this method, but just had a if clause right inside SetNewFieldValue. My guess is that this change in behavior is what is causing the problem.

Thanks in advance.

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 21-Jun-2007 15:10:45   

// in LLBLGen 1 this works, but in LLBLGen 2 you need to add either one of the commented-out lines below { AccountEntity acct = new AccountEntity(); acct.AccountID = 0; acct.IsNew = false; // acct.CostMethod = ""; acct.CostMethod = null; // acct.Fields["CostMethod"].IsChanged = true;

That's by design, as you are inistantiating a new entity, which has the CostMethod value set to the default value of its type (null if it's a nullable type, and it appears as a String/reference type, which accepts null anyway.)

So manually setting it to null, will produce the effect of unchanged value.

If you want to INSERT an entity into the database with CostMethod = null, don't set it to anything and nothing would be set into the database, leading to a vlaue of NULL being inserted by default, since the database field allows NULL.

mlc
User
Posts: 2
Joined: 21-Jun-2007
# Posted on: 21-Jun-2007 15:22:27   

Thanks for the quick reply. Let me try to explain what I am doing.

I want to update an existing record in the database without fetching the entity first (for performance reasons). To do this I create a new entity, set IsNew to false, set any primary key fields, and then set the fields that I want to change. In LLBLGen 1.x, the generated UPDATE statement included all these fields. In LLBLGen 2.0, any fields I set to null are not included for the reason I mentioned.

In this case, LLBLGen doesn't know whether null is equal to the value in the database or not because I never fetched the entity. Therefore I think the correct behavior is that if the entity was never fetched, always set the field so that it appears in the UPDATE statement.

How am I supposed to do this in LLBLGen 2.0? Is setting IsChanged = true on the field the recommended approach? I guess I do not understand the design.

Thanks again.

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 21-Jun-2007 15:29:04   

Is setting IsChanged = true on the field the recommended approach?

Yes it is, in this situation you should set the IsChanged to true.

jelling
User
Posts: 22
Joined: 26-Oct-2007
# Posted on: 05-Nov-2007 22:57:41   

Walaa wrote:

Is setting IsChanged = true on the field the recommended approach?

Yes it is, in this situation you should set the IsChanged to true.

I had the same issue and setting the IsChanged flag solved it. My question is how can we do this without requiring the developers to know the underlying db field names?

I.E. maybe entity.fields(entityFields.fieldName).IsChanged = True would abstract the issue.

Thanks,

.jelling

Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 06-Nov-2007 09:34:24   

You can try your suggested solution or the following one:

acct.Fields[myEntityFieldsIndex.MyField].IsChanged;