SQL Server – Selecting Top 1 in a Left Join

This scenario doesn’t come up too often, but the other day I had a requirement to do a left join through a one to many relationship but only get 1 result from the right side of the join. This kind of scenario can often be accomplished using sub selects, but in this case I needed access to multiple columns so a sub select would be out of the question.

The solution was to do an OUTER APPLY instead of a LEFT JOIN. An outer apply allows you to write a sub query referencing the ID from the original table just like a sub select. However as this is effectively a kind of join you have access to multiple columns.

Example Scenario

Imagine you have a table of customers and a table of addresses and each customer can have multiple addresses.

Our tables will look like this:

Customer (CustomerID, FirstName, LastName)

Address (AddressID, CustomerID, Line1, Line2, Town, County, Country, DateAdded)

The task is to get a list of customers and their most recent address.

Solution

By using an OUTER APPLY we can join the 2 tables and get the most recent address like this:

SELECT c.*, la.*
FROM Customer c
OUTER APPLY 
    (SELECT TOP 1 *
    FROM Address a
    WHERE a.CustomerID = c.CustomerID
    ORDER BY a.DateAdded DESC
    ) AS la

Advertisements

LINQ to SQL Inserts and Deletes

Inserting and Deleting records in a database using LINQ to SQL is just as easy as selecting information. What’s not so easy is actually finding out how to do it. There are lots of excellent blog posts around such as this one by Scott Guthrie http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx, however most of them we’re all written for the Beta version of LINQ to SQL which let you do a .Add() or .Remove() on your table, which was  changed on the final release. 

So to insert do something like this: 

DataClassesDataContext dataContext = new DataClassesDataContext(); 

//Create my new Movie record
Movie movie = new Movie();
movie.Name = "Tim's movie"; 

//Insert the movie into the data context
dataContext.Movies.InsertOnSubmit(movie); 

//Submit the change to the database
dataContext.SubmitChanges();

And to delete do something like this:

DataClassesDataContext dataContext = new DataClassesDataContext();

var movies = from m in dataContext.Movies
                  where m.Name == "Tim's movie"
                  select m;

dataContext.Movies.DeleteAllOnSubmit(movies);

dataContext.SubmitChanges();

LINQ to SQL Connection Strings

LINQ to SQL is great but like all great things at some point it does something that you don’t expect and gives you a headache. An example of this happened to me this week with the differences between how connection strings are handeled when you LINQ to SQL model is in a class library rather than a Website or Web Application.

What makes this issue particulalry annoying is that it only appears when you try and change the database server that your code is looking at which could end up being when it’s going live or moving to a staging server.

So we all know about connection strings, their quite simple and you just store them in your web.config file, which is how LINQ to SQL works when your using them in a Website. But as soon as you move them to a class library things change. First your connection string name is no longer that simple name you gave it e.g. ConnectionString, now it is prefixed with the namespace which is annoying but not the end of the world. Second discovery though is no matter what you do, it just doesn’t seem to pick up the connection string from the web.config file. Reason being your origional connection string has now compiled itself in the class library’s dll and that is what it is using.

The Solution

Depending when you discovered this the solution is not to bad as you either have a lot of code to change or only a small amount. You can always pass a connection string to the constructor when you are createing an instance of the data context e.g.

DataClasses1DataContext da = new DataClasses1DataContext(connectionstring);

You can also set the connection string on your LINQ to SQL model to be blank, this will remove the default contructor and force you to pass a connection string. This way you web application has the choice of what connection string to use and you can keep re-using your class library in different projects.

How to search inside Stored Procedures?

A common problem faced by many developers when it comes to databases and SQL Server is how to search the text inside a stored procedure.

In many systems particularly older Classic ASP solutions, functional code has been moved from the actual application to stored procedures inside the database. This is usually because it will either run faster here, or because it was just a lot easier to perform the necessary task using TSQL. Following this though comes the problem of how you can search what’s in all those stored procedures, especially when you’re getting into the hundreds of them. Let’s say there was a Users table that contained fields for an address, but that now needs to be moved to a table of its own, you would need to search all the code for things accessing those table columns but SQL Server Management Studio certainly doesn’t provide any search box’s with the power to do this.

Never fear though syscomments is here. Syscomments contains the original text from amongst other things all the Stored Procedures in the DB s all you need to do is search that for what you’re looking for:

Select OBJECT_NAME(id), [text]
From syscomments
Where [text] like ‘%Create%’

The function OBJECT_NAME will also help you by converting the id number in the result set into the actual name of the stored procedure (or view, function etc). If you wanted to limit the result to just stored procedures you can add the following line to the where clause:

AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1