software solutions / project leadership / agile coaching and training

LINQ to SQL: a three-month checkpoint

Posted on January 19, 2008

We are now about 3 months into our project using LINQ to SQL. Our project is a Winforms app using SQL Server 2005 (LINQ to SQL only works with SQL Server). We are planning on moving to an n-tier system with a WCF service layer, but for now our application talks directly to the database. Even though we don’t have the service layer in there yet, we’re architecting the system as if we had the service layer in there so we’re having many of the same issues that we will have when actually have the service layer.

Microsoft hasn’t always been known for stellar 1.0 releases (e.g. Vista, the Zune, etc.). When it comes to something that’s in the .NET Framework, I had a little more faith because it’s a little harder for them to go back and fix something if they screw it up. I figured that because of that, they’ll make sure that they get it right.

LINQ to SQL is not complete. There are some issues that Microsoft knows about that LINQ to SQL doesn’t currently handle. None of these issues are show-stoppers. While we’ve had to jump through some hoops to get around these issues, but we’ve been able to do everything that we’ve needed to do. I’ll get into more detail on the hoop-jumping in later posts.

Even with all of these issues, I give LINQ to SQL a rousing endorsement. I’ve always been an ORM fan, and I’ve used Nhibernate on several projects.

Getting Started

When we started our project, we inherited a legacy database that has been developed over the last 10 years. There are some interesting things in the database, such as numerics being used as primary keys, tables that aren’t normalized, and spotty referential integrity.

For the first week, three of us dragged all of the tables onto the LINQ to SQL designer and renamed all the properties to more friendly names. This was a fairly painless process. Now we had all of our entity objects created and ready to go.

Well, almost. We created a BusinessEntityBase class and all of the entity objects derive from this class. We do this by creating partial classes that match up with the classes generated by LINQ to SQL (all of the classes generated by LINQ to SQL are partial classes) and specifying that those classes derive from BusinessEntityBase. We don’t have much in the BusinessEntityBase class — the main thing in there is an abstract Id property that each entity must override to specify the value of the primary key. We use this to keep track of whether an entity object is unsaved or not.

At this point, we were ready to start working! All of our entity objects were generated for us. Contrast this with Nhibernate, where we had to write (or generate) all of our entity objects and the Nhibernate mapping files. It takes most people a long time to figure out how to write those Nhibernate mapping files!

Working with LINQ

“LINQ” is the general term for the syntax that we now use to write queries. These queries can be executed against a database (LINQ to SQL), a collection (LINQ to Objects), and various other things (LINQ to Amazon).

The LINQ syntax and particularly lambda expressions were very foreign concepts at first. You’re just not used to using those types of things in C# code. Then one day is just clicks, and you start discovering all kinds of new ways to use LINQ queries and lambda expressions.

Personally, I think lambda expressions are more revolutionary than the LINQ syntax. They don’t provide you with anything that you couldn’t do in .NET 2.0 with anonymous delegates, but now the syntax is much more concise. You can do what you want to do in fewer lines of code, which also makes for more readable code. Here’s an example of why I like lambda expressions.

Let’s say that I’m working with everyone’s favorite sample database (Northwind) and I want to find a Employee by first name, last name, or both. In the past, you probably wrote a stored procedure that looked like this:

create procedure EmployeeSearch
@FirstName varchar(20),
@LastName varchar(20)
as
select EmployeeID, FirstName, LastName, Title, TitleOfCourtesy,
BirthDate, HireDate, Address, City, Region, PostalCode, Country,
HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath
from Employees
where (@FirstName is null or FirstName = @FirstName)
and (@LastName is null or LastName = @LastName)

That worked fine, but having to check if the parameters are null is a performance hit in the stored procedure, and someone had to write the stored procedure in the first place.

With lambda expressions and LINQ to SQL, you can now do something like this and build your query incrementally:

public IQueryable SearchEmployees(string firstName, string lastName)
{
NorthwindDataContext dc = new NorthwindDataContext();

// We'll start with the entire list of employees.
IQueryable employees = dc.Employees;

if (!string.IsNullOrEmpty(firstName))
{
// Filter the employees by first name
employees = employees.Where(e => e.FirstName == firstName);
}

if (!string.IsNullOrEmpty(lastName))
{
// Filter the employees by last name
employees = employees.Where(e => e.LastName == lastName);
}

return employees;
}

Why is this better?

  • We didn’t have to write a separate stored procedure.
  • The generated SQL code won’t have to check for NULL parameters passed into a stored procedure.
  • This code is much more testable and easier to read than a stored procedure (IMO).
  • This is compiled, type safe code!

Here is the SQL code that LINQ to SQL runs as a result of this method:

SELECT [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[ReportsTo], [t0].[PhotoPath]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[LastName] = @p0
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Kruger]

I’m not saying that stored procedures are obsolete. There will still be cases where you have a query that is so complex that it’s easier to do it in a stored procedure, or it may not be possible to do it in LINQ at all. But LINQ to SQL is allowing me to scrap many of the stored procedures that I used in the past.

More to come…

Over the next few weeks, I’ll post in more detail about how we are using LINQ to SQL and some of the things we’ve had to do to make it work.





Why I should’ve gone to CodeMash last year

Posted on January 13, 2008

I went to CodeMash this year. Last year I did not. Sure, I knew that there would be a lot of good talks, but can’t I earn the same information by reading books and blogs, listening to podcasts, etc.?

Now I see why I was wrong. People I work with talk about the value of being involved in the .NET community, and now I see why they are right.

Sure, the talks were great. But by far the best part is being able to sit down with people who know way more than me and ask them about problems that I’m having right now on my current project. That kind of free advice is invaluable.

In the technology world, there is always tons of new stuff out there, and there’s no way that I can keep up with it all (especially with a wife and a kid on the way). If I want to be someone who can make good architectural decisions, how can I do that without having knowledge of what’s out there? Since I can’t keep up with it all, I could use some other people that can help out.

So I plan on trying to be more involved in the local .NET community (user groups, blogging, etc.), and I’m really excited about it. Hopefully I can make some worthwhile contributions of my own while I’m at it.





Minor batch file tricks

Posted on January 7, 2008

Just so I don’t forget how to do these things…

Remove quotes:
SET Line=”C:\Program Files\”
SET Line=%Line:”=%
echo %Line% — will output: C:\Program Files\

Remove a trailing backslash:
SET Line=C:\Windows\
IF “%Line:~-1%”==”\” SET Line=%Line:~0,-1%
echo %Line% — will output: C:\Windows





SERVICES
SOFTWARE SOLUTIONS
I have over 10 years of software development experience on several different platforms (mostly Ruby and .NET). 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.
PROJECT LEADERSHIP
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.
AGILE COACHING
I believe that Agile processes and tools should be applied with common sense. I've spent the last 6 years working on Agile projects as a consultant in many different environments, both in leadership roles and as a practitioner doing the work. I can help you find out how Agile can work best in your organization, not just apply a prescriptive process.
TEST DRIVEN DEVELOPMENT TRAINING
TDD Boot Camp is a hands-on, three day, comprehensive training course that will teach you all of the skills, tools, frameworks that you will need to use test-driven development to develop real world .NET applications. If you're not looking for something that intensive, check out the the half-day version.
Have any questions? Contact me for more information.
PRESENTATIONS
The Business of You: 10 Steps For Running Your Career Like a Business
From CONDG 2012, Stir Trek 2014
From Stir Trek 2013, DogFoodCon 2013
From Stir Trek 2012, QA or the Highway 2014
(presented with Brandon Childers, Chris Hoover, Laurel Odronic, and Lan Bloch from IGS Energy) from Path to Agility 2012
(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