Skip to main content

Linq To Sql, Attach, Using a DateTime as Timestamp and large bottle of ibuprofren!!!

Scenario:

You use LINQ to SQL (L2S) as your ORM, you create a business layer that supplies a list of customer entities to the presentation layer. The UI presents the data in a gridview where the user can update the info.

When the user clicks update, it passes a new instance of the entity to your BL method. Now you might be tempted to think that you simply use the Attach method of the Customers table and everythings fine and dandy. Oh that it were true. Be ready for:

"System.InvalidOperationException: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy."

The entity being passed to your BL here is *not* the same one that went out. It is a new one created by the GridView as part of the update. Thus, it has no idea of the original context and your new context has no idea of it's original state.

If you have add a timestamp column that can be used by L2S to determine if the row has changed, you can make things work fine. This has some advantages in that it reduces the amount of T-Sql generated to perform the update.

// Business Layer code to get the list and perform the update.
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)] 
public static List ListCustomers()
{
    using (SandboxDataContext dataContext = new SandboxDataContext())
    {
        var custs = from c in dataContext.Customers orderby c.CustomerName select c ;
        return custs.ToList();
    }
}

[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)]
public static void UpdateCustomer(Customer c)
{
    using (SandboxDataContext dataContext = new SandboxDataContext())
    {
        dataContext.Customers.Attach(c, true);
        dataContext.SubmitChanges();
    }
}

Note: You must use the Attach(entity, true) overload to actually save the changes.
Note: Make sure you configure the DataKeys property of your grid to include the primary key and the timestamp. Otherwise, you get a ChangeConflictException -- Row not found or changed.

-- T-Sql generated by L2S

exec sp_executesql N'UPDATE [dbo].[Customers]
SET [CustomerName] = @p2
WHERE ([CustomerKey] = @p0) AND ([TimeStamp] = @p1)

SELECT [t1].[TimeStamp]
FROM [dbo].[Customers] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerKey] = @p3)',

N'@p0 int,
@p1 timestamp,
@p2 varchar(40),
@p3 int',
@p0=18,
@p1='0x00000000000007D1',
@p2='Acme Widgets, LLC',
@p3=18

Notice how only the CustomerName column is updated instead of every column. This is one of the largest advantages over stored procs. Ordinarily, you'd probably write one stored proc to update the table and it would update every column. You definitely wouldn't want to write an SP for *every* column or permutation of columns.
Instead, what's produced is a nice tight query that only changes those columns that need to be changed. IMHO, this outweighs any perceived performance issues versus stored procs.

Technorati Tags: ,

Comments

Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.UserProfileContains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.webpages_MembershipStores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.webpages_OAuthMembershipStor…

JavaScript function to automatically add slashes to date

In converting an old Windows app to a browser app, the user wanted to be able to enter dates without the slashes. Here's a simple jscript: 1:// Function to convert short date string (MMddyy) 2:// or (MMddyyyy) to a date string (mm/dd/yyyy). 3:// txtBox is the actual textbox control 4:// with the value to be processed. 5:function FixShortDate(txtBox) { 6:if (txtBox == null) { 7:return'' } 8: 9:var re = new RegExp(/(\d{6})(\d{2})?/); 10: 11:if (re.test(txtBox.value)) 12: { 13:if (txtBox.value.length == 8) { 14: txtBox.value = txtBox.value.substring(0, 2) + '/' + txtBox.value.substring(2, 4) + '/' + txtBox.value.substring(4, 8) 15: } 16: 17:if (txtBox.value.length == 6) { 18:if (txtBox.value.substring(4, 6) < 20)

Get Asp.Net Profile properties from Sql

Ever wanted to include the profile information from an Asp.Net profile in a query? It’s not that hard once you understand the structure. I’ve written a little function that does all the work. Note: I’m using Sql Server as my repository.

First we need to understand how the profile data is stored. Looking at the aspnet_Profile table, we can see that it stores the information in two columns: PropertyNames and PropertyValuesString.

Looking at PropertyNames we can see that it has a basic structure of Property Name, Data Type, Starting Position and Length. For example, in the string “FirstName:S:0:4:Phone:S:4:10:LastName:S:14:5:” we can see that FirstName is of type string, starts at position 0 and has a length of 4. Notice the zero base for the starting position, we need to correct for that in our function. This means in the PropertyValuesString “John2175551212Smith”, we would start with the first position and proceed 4 characters to get the name.

You might be thinking …