Share via

A Little Linq to Sql

  • Article

Building a little application for capturing real time data, I decided to play around with LINQ a bit. I found myself in the situation where I had a new object (say a data point) that, based on my database schema, referenced other objects that may or may not already exist in my database. So I want to do an "InsertIfDoesn'tExist(Item.Category)". I've translated this into the domain of my property web site sample to simplify things. The schema looks a like this (taken from my Linq to Sql classes diagram in VS 2008).

Picture1

Initially I started off with

 if ((from t in dc.Towns where t.Name == NewProperty.Town.Name select t).Count() == 0)
{
    Town NewTown = new Town() { Name = NewProperty.Town.Name };
    dc.Towns.InsertOnSubmit(NewTown);
    dc.SubmitChanges();
}

Which I really wasn't very happy with. By using lambda expression syntax I could drop the select:

 if (dc.Towns.Where(t => t.Name == NewProperty.Town.Name).Count() == 0)
{
    Town NewTown = new Town() { Name = NewProperty.Town.Name };
    dc.Towns.InsertOnSubmit(NewTown);
}

And then I realised that Count() supports an optional predicate so I could drop the Where() too.

 if (dc.Towns.Count(t => t.Name == NewProperty.Town.Name) == 0)
{
    Town NewTown = new Town() { Name = NewProperty.Town.Name };
    dc.Towns.InsertOnSubmit(NewTown);
}

Not surprisingly, irrespective of which of the above I use, Linq to Sql optimises them into the following T-SQL:

 exec sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[Towns] AS [t0]
WHERE [t0].[Name] = @p0',N'@p0 nvarchar(6)',@p0=N'NewTownName'

Now if you actually want to get hold of the Town if it's already in the DB (and you usually do) then the above doesn't help too much, you're better off using SingleOrDefault() to get someting like:

 Town NewTown = (from t in dc.Towns
                 where t.Name == NewProperty.Town.Name
                 select t).SingleOrDefault();
if (NewTown == null)
{
    NewTown = new Town() { Name = NewProperty.Town.Name };
    dc.Towns.InsertOnSubmit(NewTown);
}

Which again can be shortened to (thanks MikeT for pointing this out):

 Town NewTown = dc.Towns.SingleOrDefault(t => t.Name == NewProperty.Town.Name);
if (NewTown == null)
{
    NewTown = new Town() { Name = NewProperty.Town.Name };
    dc.Towns.InsertOnSubmit(NewTown);
}

And again, both produce identical T-SQL

 exec sp_executesql N'SELECT [t0].[TownID], [t0].[Name]
FROM [dbo].[Towns] AS [t0]
WHERE [t0].[Name] = @p0',N'@p0 nvarchar(6)',@p0=N'NewTownName'

It'll take me a while to get used to but I like this LINQ stuff....

Technorati Tags: linq,sql server

Comments