by Andreas

LINQ – how to build queries dynamically

In a previous post I described a way to build dynamic WHERE clauses to achieve the WHERE [value] IN [array] functionality using LINQ. This post will show how you can combine LINQ with lambda to achieve a different type of dynamic / runtime query building. (The example is using Entity Framework 4.2).

I came across a need for this when dealing with Guid columns in SQL Server. In code, Guid.Empty isn’t NULL, it’s “00000000-0000-0000-0000-000000000000”. This doesn’t go too well with the unique index of the table, which of course ignores NULL values but considers “00000000-0000-0000-0000-000000000000” a real value – which it is! So the challenge was to alter the LINQ query depending on one of the input parameters of the method. If the Guid parameter is equal to Guid.Empty, I want to query the database for NULL values in that column. If the Guid has a valid value, query the database using it the way it is.

I made this work by adding a conditional IF, but for readability I have split it up in an old fashion IF / ELSE. Define the LINQ query, and based on the input parameter “parentFolderId” define the WHERE statement using lambda :

var existingFolder = from f in db.Folders
             select f;

if (Guid.Empty.Equals(parentFolderId))
    // parent folder is empty, which is NULL in DB (Guid.Empty = '00000000-0000-0000-0000-000000000000')
    existingFolder = existingFolder.Where(p => p.FolderName == folderName && p.ParentFolderID == null);
    existingFolder = existingFolder.Where(p => p.FolderName == folderName && p.ParentFolderID == parentFolderId);

if (existingFolder.FirstOrDefault() != null)
    return false; // folder already exists

// create new folder

Remember that a LINQ query isn’t executed until the result set is accessed. In this case the database isn’t queried until the line where “existingFolder.FirstOrDefault()” is executed, and the desired result is achieved with minimal code.