software solutions / project leadership / agile coaching and training

LINQ to SQL talk stuff

Posted on February 29, 2008

Here is the sample project from my talk at the Columbus .NET Users’ Group last night. If you open the project, you’ll notice a Northwind.sql file. This is my modified version of the Northwind database (I had to add a timestamp column to the Employees table to get it to cooperate with LINQ to SQL).

Like I mentioned yesterday, if you’re new to LINQ to SQL, a great place to start is Scott Guthrie’s series of blog posts on LINQ to SQL. Here they are:

Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
Part 6: Retrieving Data Using Stored Procedures
Part 7: Updating our Database using Stored Procedures
Part 8: Executing Custom SQL Expressions
Part 9: Using a Custom LINQ Expression with the <asp:LinqDatasource> control

LINQ to SQL In Disconnected/N-Tier scenarios: Saving an Object

Posted on February 10, 2008

LINQ to SQL is a great tool, but when you’re using it in an n-tier scenario, there are several problems that you have to solve. A simple example is a web service that allows you to retrieve a record of data and save a record of data when the object that you are loading/saving had child lists of objects. Here are some of the problems that you have to solve:

1) How can you create these web services without having to create a separate set of entity objects (that is, we want to use the entity objects that the LINQ to SQL designer generates for us)?
2) What do we have to do to get LINQ to SQL to work with entities passed in through web services?
3) How do you create these loading/saving web services while keeping the amount of data passed across the wire to a minimum?

I created a sample project using the Northwind sample database. The first thing to do is to create your .dbml file and then drag tables from the Server Explorer onto the design surface. I have something that looks like this:


I’m also going to click on blank space in the design surface, go to the Properties window, and set the SerializationMode to Unidirectional. This will put the [DataContract] and [DataMember] attributes on the designer-generated entity objects so that they can be used in WCF services.


Now I’ll create some web services that look something like this:

public class EmployeeService
	public Employee GetEmployee(int employeeId)
		NorthwindDataContext dc = new NorthwindDataContext();
		Employee employee = dc.Employees.FirstOrDefault(e => e.EmployeeID == employeeId);
		return employee;

	public void SaveEmployee(Employee employee)
		// TODO

I have two web service methods — one that loads an Employee by ID and one that saves an Employee.

Notice that both web service methods are using the entity objects generated by LINQ to SQL. There are some situations when you will not want to use the LINQ to SQL generated entities. For example, if you’re exposing a public web service, you probably don’t want to use the LINQ to SQL entities because that can make it a lot harder for you to refactor your database or your object model without having to change the web service definition, which could break code that calls the web service. In my case, I have a web service where I own both sides of the wire and this service is not exposed publicly, so I don’t have these concerns.

The GetEmployee() method is fairly straight-forward — just load up the object and return it. Let’s look at how we should implement SaveEmployee().

In order for the DataContext to be able to save an object that wasn’t loaded from the same DataContext, you have to let the DataContext know about the object. How you do this depends on whether the object has ever been saved before.

How you make this determination is based on your own convention. Since I’m dealing with integer primary keys with identity insert starting at 1, I can assume that if the primary key value is < 1, this object is new.

Let's create a base class for our entity object called BusinessEntityBase and have that class expose a property called IsNew. This property will return a boolean value based on the primary key value of this object.

namespace Northwind
	public abstract class BusinessEntityBase
		public abstract int Id { get; set; }

		public virtual bool IsNew
			get { return this.Id <= 0; }

Now we have to tell Employee to derive from BusinessEntityBase. We can do this because the entities that LINQ to SQL generates are partial classes that don't derive from any class, so we can define that in our half of the partial class.

namespace Northwind
	public partial class Employee : BusinessEntityBase
		public override int Id
			get { return this.EmployeeID; }
			set { this.EmployeeID = value; }

Now we should be able to tell if an Employee object is new or not. I'm also going to do the same thing with the Order class since the Employee object contains a list of Order objects.

namespace Northwind
	public partial class Order : BusinessEntityBase
		public override int Id
			get { return this.OrderID; }
			set { this.OrderID = value; }

OK, let's start filling out the SaveEmployee() method.

public void SaveEmployee(Employee employee)
	NorthwindDataContext dc = new NorthwindDataContext();
	if (employee.IsNew)

Great. So now I can call the GetEmployee() web method to get an employee, change something on the Employee object, and call the SaveEmployee() web method to save it. But when I do it, nothing happens.

The problem is with this line:

public void SaveEmployee(Employee employee)
	NorthwindDataContext dc = new NorthwindDataContext();
	if (employee.IsNew)
		dc.Employees.Attach(employee); // <-- PROBLEM HERE

The Attach() method attaches the entity object to the DataContext so that the DataContext can save it. But the overload that I called just attached the entity to the DataContext and didn't check to see that anything on the object had been changed. That doesn't do us a whole lot of good. Let's try this overload:

public void SaveEmployee(Employee employee)
	NorthwindDataContext dc = new NorthwindDataContext();
	if (employee.IsNew)
		dc.Employees.Attach(employee, true); // <-- UPDATE

This second parameter is going to tell LINQ to SQL that it should treat this entity as modified so that it needs to be saved to the database. Now when I call SaveEmployee(), I get an exception when I call Attach() that says:

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.

What this means is that my database table does not have a timestamp column on it. Without a timestamp, LINQ to SQL can't do it's optimistic concurrency checking. No big deal, I'll go add timestamp columns to the Employees and Orders tables in the database. I'll also have to go into my DBML file and add the column to the table in there. You can either add a new property to the object by right-clicking on the object in the designer and selecting Add / Property, or you can just delete the object from the designer and then dragging it back on from the Server Explorer.

Now the DBML looks like this:

updated dbml

Now let's try calling SaveEmployee() again. This time it works. Here is the SQL that LINQ to SQL ran:

UPDATE [dbo].[Employees]
SET [LastName] = @p2, [FirstName] = @p3, [Title] = @p4, [TitleOfCourtesy] = @p5, [BirthDate] = @p6, [HireDate] = @p7, [Address] = @p8, [City] = @p9, [Region] = @p10, [PostalCode] = @p11, [Country] = @p12, [HomePhone] = @p13, [Extension] = @p14, [Photo] = @p15, [Notes] = @p16, [ReportsTo] = @p17, [PhotoPath] = @p18
WHERE ([EmployeeID] = @p0) AND ([Timestamp] = @p1)

SELECT [t1].[Timestamp]
FROM [dbo].[Employees] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[EmployeeID] = @p19)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [5]
-- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
-- @p2: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Buchanan]
-- @p3: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Steven]
-- @p4: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [Sales Manager]
-- @p5: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Mr.]
-- @p6: Input DateTime (Size = 0; Prec = 0; Scale = 0) [3/4/1955 12:00:00 AM]
-- @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/17/1993 12:00:00 AM]
-- @p8: Input NVarChar (Size = 15; Prec = 0; Scale = 0) [14 Garrett Hill]
-- @p9: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]
-- @p10: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p11: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [SW2 8JR]
-- @p12: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [UK]
-- @p13: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [(71) 555-4848]
-- @p14: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [3453]
-- @p15: Input Image (Size = 21626; Prec = 0; Scale = 0) [SqlBinary(21626)]
-- @p16: Input NText (Size = 448; Prec = 0; Scale = 0) [Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976.  Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London.  He was promoted to sales manager in March 1993.  Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management."  He is fluent in French.]
-- @p17: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p18: Input NVarChar (Size = 37; Prec = 0; Scale = 0) [http://accweb/emmployees/buchanan.bmp]
-- @p19: Input Int (Size = 0; Prec = 0; Scale = 0) [5]

Notice that it passed back all of the properties in the SQL statement -- not just the one that I changed (I only changed one property when I made this call). But isn't it horribly ineffecient to save every property when only one property changed?

Well, you don't have much choice here. Now there is another overload of Attach() that takes in the original version of the object instead of the boolean parameter. In other words, it is saying that it will compare your object with the original version of the object and see if any properties are different, and then only update those properties in the SQL statement.

Unfortunately, there's no good way to use this overload in this case, nor do I think you would want to. I suppose you could load up the existing version of the entity from the database and then pass that into Attach() as the "original", but now we're doing even more work -- we're doing a SELECT that selects the entire row, and then we're doing an UPDATE that only updates the changed properties. I would rather stick with the one UPDATE that updates everything.

Filtering Intellisense lists in the WF RuleSetDialog

Posted on February 3, 2008

Recently on our project we’ve been diving into Windows Workflow Foundation, particularly the rules engine. This process is relatively painless since Microsoft was kind enough to expose the RuleSetDialog class so that you can use the WF Rule Set editor in your application. This code is as easy as doing something like this:

// Create a RuleSet that works with Orders (just another .net Object)
RuleSetDialog ruleSetDialog = new RuleSetDialog(typeof(Order), null, null);

// Show the RuleSet Editor

// Get the RuleSet after editing
RuleSet ruleSet = ruleSetDialog.RuleSet;

That’s how simple it is to include the RuleSetDialog in your application. The problem is that the Intellisense dropdowns in the RuleSetDialog expose private and protected members of your class, and Microsoft doesn’t give you any way to filter the Intellisense list. So you end up with stuff like this:

Intellisense with private and protected members

Microsoft is aware of this issue, and they haven’t said anything definite about doing anything about this problem.

When you’re writing a commercial application or something that non-developers are going to use, you don’t want this kind of cryptic stuff in the list. I don’t want to expose all of the private members of my classes to the user, just like how you don’t expose private members of a class in a public API.

One way to filter the list is to create an interface and pass the interface type in as the first parameter in the RuleSetDialog constructor. This way you won’t have all of the private and protected members of the class in the Intellisense because an interface only exposes public methods. So now you’re constructor looks like this:

// Create a RuleSet that works with Orders (just another .net Object)
RuleSetDialog ruleSetDialog = new RuleSetDialog(typeof(IOrder), null, null);

This is a decent solution, but it still has problems:

  • You have to create the interface.
  • System.Object members like Finalize(), GetHashCode(), and Equals() are still exposed.

Like I said before, in my commercial application, I don’t want users to have to see all of this extra stuff. I only want to show them the things that I want to show them.

Well, thanks to Reflector, I was able to come up with a way to let you filter the list. In my example, I can filter out all of the protected and private members, filter out static types, only display members decorated with an attribute, or completely override the list to only display strings that I’ve added. So now you can easily get something that looks more like this:

Filtered Intellisense

Much better!

Now I must warn you. This solution is making extensive use of reflection to get at private and internal methods and events that Microsoft didn’t feel like exposing to us. So I felt a little dirty while I was writing it, but it gets the job done!

Here is the code. Please leave a comment if you find anything wrong with it.

Here are some other good posts about the WF Rules Engine:

Execute Windows Workflow Rules without Workflow
Introduction to the Windows Workflow Foundation Rules Engine
External Ruleset Demo


I have over 15 years of software development experience on several different platforms (.NET, Ruby, JavaScript, SQL Server, and more). I recognize that software is expensive, so I'm always trying to find ways to speed up the software development process, but at the same time remembering that high quality is essential to building software that stands the test of time.
I have experience leading and architecting large Agile software projects and coordinating all aspects of a project's lifecycle. Whether you're looking for technical expertise or someone to lead all aspects of an Agile project, I have proven experience from multiple projects in different environments that can help make your project a success.
Every team and every situation is different, and I believe that processes and tools should be applied with common sense. I've spent the last 10+ years working on projects using Agile and Lean concepts in many different environments, both in leadership roles and as a practitioner doing the work. I can help you develop a process that works best in your organization, not just apply a prescriptive process.
Have any questions? Contact me for more information.
From Stir Trek 2017
Iteration Management - Your Key to Predictable Delivery
From Stir Trek 2016 and QA or the Highway 2015
From CodeMash 2016, QA or the Highway 2014, Stir Trek 2012
The Business of You: 10 Steps For Running Your Career Like a Business
From CodeMash 2015, Stir Trek 2014, CONDG 2012
From Stir Trek 2013, DogFoodCon 2013
(presented with Brandon Childers, Chris Hoover, Laurel Odronic, and Lan Bloch from IGS Energy) from Path to Agility 2012
From CodeMash 2012 and 2013
(presented with Paul Bahler and Kevin Chivington from IGS Energy)
From CodeMash 2011
An idea of how to make JavaScript testable, presented at Stir Trek 2011. The world of JavaScript frameworks has changed greatly since then, but I still agree with the concepts.
A description of how test-driven development works along with some hands-on examples.
From CodeMash 2010
From CodeMash 2010