Rhymed Code

Table Persons

If you come from the world of always-use-stored-procedures or at least you always write your own SQL query, you’d be curious how LINQ to SQL translates your C# code to SQL query. Say you have table Persons with fields PersonId, FirstName, LastName, and Age. PersonId is the primary key of the table Persons.

In this example, we want to update a particular row (PersonId = 1) with a new Age value. So, we write the following block of code in our application.

Entities.DataContext context = new Entities.DataContext();

int personId = 1;
Entities.Person person =
    context.Persons.SingleOrDefault(p => p.PersonId == personId);
person.Age = 24;
context.SubmitChanges();

To see the generated SQL query, we want to redirect the log of DataContext object to console.

Entities.DataContext context = new Entities.DataContext();
context.Log = Console.Out;

int personId = 1;
Entities.Person person =
    context.Persons.SingleOrDefault(p => p.PersonId == personId);
person.Age = 24;
context.SubmitChanges();

Now, when we run the application, you will see the SQL queries generated by your code in console window. The SQL query will look something similar to the following.

UPDATE [dbo].[Persons]
SET [Age] = @p4
WHERE ([PersonId] = @p0) AND ([FirstName] = @p1) AND ([LastName] = @p2) AND ([Age] = @p3)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [John]
-- @p2: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Smith]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [23]
-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [24]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

If you’re used to writing your own query, you probably wonder why the WHERE clause is verbose (or wordy, an English major will say). You see the query tries to find the row to update by matching every single field in the table with their old values. “Well, that’s dumb, ain’t it?” you say. “We can find the row to update by matching only the value of the primary key, PersonId.” Here’s how.

Properties Window

1. Go to your Solution Explorer in Visual Studio and double click the .dbml file.
2. Click the property PersonId on the class diagram.
3. Go to Properties window (it’s on bottom right on default window setup) and find the property Update Check. The current value should be Always.
4. Change it to Never.

Repeat step 2-4 for all properties (FirstName, LastName, and Age).

Update the application to update the Age to 25 (previously 24). Rebuild and re-run the application. You will see the update statement that you probably always write.

UPDATE [dbo].[Persons]
SET [Age] = @p1
WHERE [PersonId] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [25]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

So, what’s the purpose of the property Update Check? The property description says it all. It’s to control the frequency of optimistic concurrency checking.