Matthew Pavey | Faith, Family, Code

LINQ, the .NET Language Integrated Query project, was an initiative to standardize data access across data sources.

I recently read an article by Arthur Fuller arguing that he was not convinced LINQ would revolutionize database application development. I cannot say I completely agree or disagree with everything in it, but it was an interesting read, and the comments underneath were as interesting as the article, with people arguing hard on both sides.

Like Fuller, I have always been a strong proponent of whatever the back end can do, the back end should do. That has to be judged project by project, but the projects I architect have typically benefited from a separate data layer with stored procedures handling data access. So I came to LINQ with some skepticism.

That said, I do not think LINQ requires moving back end code to the front end. That comes down to how the solution is architected and how the developer chooses to use it. The problem is that with LINQ still so new, almost every code snippet, document, and article uses examples that make it look like all the data access code has to live in the front end. I fell into it myself at first: I dropped a DBML file into the web project and wrote some quick code to test it. Once I saw it worked, I immediately created a Data layer, moved the DBML file there, and carried on. That way the data access layer does what it says, and the front end uses the data rather than fetching it.

One comment on the article summed up what I had been thinking:

LINQ is a tier neutral technology. On the front end one can use LINQ to query returned datasets, XML files etc and on the back end to query a database. IMHO the back end (data access tier) is the only tier that is allowed to access data storage like SQL Server. The front end can manipulate returned data from the back end but not retrieve or update it without using the DAC. LINQ is merely a uniform way of accessing different data sources. New functionality like LINQ does not force bad coding style, that is left up to the creativity of the developer.

So no conclusive agreement has been reached about where LINQ ends up, but I am optimistic and looking forward to learning more about it over the coming months.

Looking back from the present day: LINQ itself stuck around and became a normal part of writing C#. LINQ to SQL, the specific technology I was testing here, was largely superseded by Entity Framework within a few years. The conviction underneath the post did not change, though. I still think the back end should do what the back end can do, which is more or less the argument I was still making in 2026.

This one is one of the basics that all SQL programmers learn at some point, sometimes the hard way.

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the row you just added to a particular table. Say you have a trigger that inserts a record into a Logs table whenever your stored procedure or INSERT statement adds a record to the Orders table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the row added to the Logs table instead, which makes for a nasty bug in your data access layer.

To avoid that, and to protect yourself against someone adding a trigger later, use SCOPE_IDENTITY(). It returns the identity of the most recently added row within the current scope, which is the one you actually inserted.

This came up during QA testing, and it was the first thing I thought of. Sure enough, the original developer had used @@IDENTITY to get the newly inserted identity value, which was perfectly correct at the time he wrote it. We had since added trigger functionality, and that is what caused the wrong results. Switching to SCOPE_IDENTITY() fixed it.

If you use triggers anywhere in your database, this is one to remember.

I do not have to do this often, but it comes in handy in certain situations.

Setting a variable from dynamic SQL:

DECLARE @MyValue INT

EXEC sp_executesql N'SELECT @MyValue = 999',
                   N'@MyValue INT OUTPUT',
                   @MyValue OUTPUT

SELECT @MyValue

Setting an output parameter from a dynamic stored procedure call:

DECLARE @OutputParameter VARCHAR(100)
DECLARE @Error           INT
DECLARE @SPName          VARCHAR(128)
DECLARE @SPCall          NVARCHAR(128)
DECLARE @RC              INT

SELECT @SPCall = 'EXEC ' + @SPName + ' @OutputParameter OUTPUT'

EXEC @RC = sp_executesql @SPCall,
                         N'@OutputParameter VARCHAR(100) OUTPUT',
                         @OutputParameter OUTPUT

SELECT @Error = @@ERROR

One place this was useful for me was converting a denormalized set of horizontal data into a normalized vertical set.

The denormalized data had a series of column names like "200701", "200702", "200703", one for each month of the year. The file changed month to month, and to avoid rewriting code every time a new one arrived, I could import the data generically, work out which columns were in the file, and pull each value by setting a variable with dynamic SQL.

DECLARE @Total        FLOAT
DECLARE @SqlStatement NVARCHAR(1000)

SET @Total = 0

SET @SqlStatement = 'SELECT @Total = [' + @ColumnName + '] ' +
                    'FROM RawData WHERE RecordID = ' + CONVERT(VARCHAR, @RecordID)

-- get the specified column value for the current record
EXEC sp_executesql @SqlStatement, N'@Total FLOAT OUTPUT', @Total OUTPUT

A simple function to calculate age from a date of birth. It handles the two cases that trip people up: a birthday later in the current year, and a birthday later in the current month.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetAge] (@DOB DATETIME, @Today DATETIME)
RETURNS INT
AS
BEGIN

    DECLARE @Age INT

    SET @Age = YEAR(@Today) - YEAR(@DOB)

    -- if the birthday month has not arrived yet, subtract one
    IF MONTH(@Today) < MONTH(@DOB)
    BEGIN
        SET @Age = @Age - 1
    END

    -- if it is the birthday month but the day has not arrived, subtract one
    IF MONTH(@Today) = MONTH(@DOB) AND DAY(@Today) < DAY(@DOB)
    BEGIN
        SET @Age = @Age - 1
    END

    RETURN @Age

END

Usage

DECLARE @Today AS DATETIME

SET @Today = GETDATE()

SELECT ID,
       DOB,
       'Age' = dbo.GetAge(DOB, @Today)
FROM   MyTable