Introduction

Imagine a web based content management system that allows a user to enter articles. Together with the article, she can upload one more images that are presented with the image. So far so good. However, when the article is deleted from the database, the images should also have been deleted. Unfortunately, quite often a developer forgets to add this functionality to the system.

I have seen it many times before in old web applications that we're maintaining. We have huge folders with images, probably half of which are orphaned. I often joked that it would be nice to do something like this:

DELETE FROM C:\Uploads\Images WHERE File.Name NOT IN (SELECT FileName FROM Attachments) 

where C:\Uploads\Images is the folder containing the uploaded images and Attachments is a database table with a column called FileName that holds the filename of an image, like NewsArticle123.jpg.

Obviously, this doesn't work as the file system doesn't understand database parlance, while at the same time the database doesn't understand what a file is, or how the file system works.

For some time, I was hoping that WinFS would enable this kind of querying, but I guess we'll have to wait a bit more to find out.

But as soon as I saw Linq and some of its database and collection capabilities, I figured it should be possible to get real close to my final syntax. With the help of a single Linq to SQL file, some drag and drop operations and a number of lines of code, I was able to accomplish what I wanted: an easy way to delete orphaned images. Here's what I did:

1. Added a Linq to SQL file to my .NET 3.5 Windows Application.
   
   
2. From the Server Explorer, dragged the Attachments table onto the Object Relational Designer.
   
   
3. Called the following code from a Button's Click event:

public void DeleteOrphans()
{
  string imagePath = @"C:\Uploads\Images";
  FileInfo[] myFiles = new DirectoryInfo(imagePath).GetFiles();

  MyAttachmentsDataContext db = new MyAttachmentsDataContext();

  var orphanedFiles =
      from o in myFiles
      join a in db.Attachments on o.Name equals a.FileName into theFiles
      where !db.Attachments.Any(p => p.FileName == o.Name)
      select o;

  foreach (var orphanedFile in orphanedFiles)
  {
    orphanedFile.Delete();
  }
}

The first two lines of the code get an array of FileInfo objects with all the files in the C:\Uploads\Images folder. Next, these files are joined with the result of the Attachments table with a where clause that filters out all files that have a matching Attachment record in the database. Once the query is fired in the foreach block, the resulting orphanedFiles is a collection of FileInfo instances that don't have an associated record in the Attachments table and can thus be deleted.

Notice how the type inference system did a good job in determining the type of the items in the collection. Although I didn't explicitly determine a type for the orphanedFiles collection, the compiler knows that it will contain instances of FileInfo, so I can call the Delete method without an additional cast.

This is my first stab at solving this problem with Linq, so there may be better or easier ways to do it and get even closer to my initial code example. I also think it won't scale very well to folders with thousands of files. However, it's a nice example and was fun to figure out. It also shows the great potential of the upcoming Linq technologies.