Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

Version Fields and LINQ: Careful of underscores in Field Name


:P
On this page:

This probably falls into the bonehead category, but this might bite somebody else so I'm writing it up: LINQ to SQL works best when your database tables have version fields that can be used to see change state. So recluctantly I resigned myself to give in to this and decided I'd use a consistent fieldname that wouldn't interfere with normal naming.

Version and TimeStamp may be very clear names, but they are also likely fieldnames that can be chosen for real field data in a table. So I decided to use:

_version

Nice and simple and clear and not likely for field name interference.

But LINQ to SQL gets confused in some situations by fields with a leading underscore. The code generated looks Ok from what I can see:

[Column(Name="[_version]", Storage="__version", AutoSync=AutoSync.Always, DbType="rowversion", IsDbGenerated=true, IsVersion=true, UpdateCheck=UpdateCheck.Never)]
    [DataMember(Order=28)]
    public byte[] _version
    {
        get
        {
            return this.@__version;
        }
        set
        {
            if ((this.@__version != value))
            {
                this.On_versionChanging(value);
                this.SendPropertyChanging();
                this.@__version = value;
                this.SendPropertyChanged("_version");
                this.On_versionChanged();
            }
        }
    }

but @__version/_version returns null in dynamic query scenarios. For example in the following code the second query fails to load the timestamp field: 

// *** this works
TimeTrakkerContext ctx = new TimeTrakkerContext();
tt_customer cust33 = ctx.tt_customers.Single(c => c.Pk == 1);
Response.Write( cust33._version);
 
// *** this doesn't
TimeTrakkerContext ctx2 = new TimeTrakkerContext();
tt_customer cust32 = ctx2.ExecuteQuery<tt_customer>("select * from tt_customers where pk = 1").Single();
Response.Write( cust32._version);

If I go in and change the _version field to tversion in the database and adjust the field names accordingly in the code:

// *** this works
TimeTrakkerContext ctx = new TimeTrakkerContext();
tt_customer cust33 = ctx.tt_customers.Single(c => c.Pk == 1);
Response.Write( cust33.tversion);
 
// *** now it works
TimeTrakkerContext ctx2 = new TimeTrakkerContext();
tt_customer cust32 = ctx2.ExecuteQuery<tt_customer>("select * from tt_customers where pk = 1").Single();
Response.Write( cust32.tversion);

then it all works as expected.

For now better safe than sorry - I'm not using any leading underscores in fields and especially not timestamp fields. <g>

Posted in LINQ  Visual Studio  

The Voices of Reason


 

Daniel
September 23, 2008

# re: Version Fields and LINQ: Careful of underscores in Field Name

Actually, any underscore in a column name might have issues. We just ran into this today. We had a column name: State_Province and we were not getting any data back from it with that mapping. Once we changed the database and the LINQ mapping to StateProvince things were just fine.

Daniel

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024