Linq to SQL Anti-Patterns–Dealing with nullable types

Hi Guys,

This blog will demonstrate some bad habits in LinqToSql and how to deal with nullable types that provide clean code without null checks all over and improved performance on the projections.

In one of my posts I recommended and demonstrated value of using NHProf, well the same goes for Linq to SQL. You can download a trial here:

http://l2sprof.com/

It is really easy to use, the the case of ASP.NET, just add a global.asax or edit an existing one’s code behind like so:

public class Global : System.Web.HttpApplication
    {

        protected void Application_Start(object sender, EventArgs e)
        {
            try
            {

                var profileOn = false;
                bool.TryParse(ConfigurationManager.AppSettings["EnableLinqToSQLProfile"], out profileOn);
                if (profileOn)
                    HibernatingRhinos.Profiler.Appender.LinqToSql.LinqToSqlProfiler.Initialize();
            }
            catch (Exception)
            {
               Debug.WriteLine("Could not initialize LinqToSql Profiling");
            }
        }

Excellent, then add a reference to the HibernatingRhinos.Profiler.Appender.dll which I guess can be in your lib folder.

Right, now lets have a look at some customer code that is causing 2 hits to the database, which we can reduce it to one hit.

Old Code causing 2 hits:

 public int GetCaseStudyIDByPageID(int pageID)
        {
            var result = _db.CaseStudies.Where(i => i.PageID == pageID);

            if (result.Any())
            {
                return result.First().CaseStudyId;
            }

            return -1;
        }

So from above, if we attach the L2SQL profiler, we will see 2 exact same queries going to the DB, one for the .Any() and then the other for the c.First.

Useful Query: (Note the extra columns we do not need, we come back to this, as I do not like the projection here, too many columns, I just need the PageID!!)

SELECT TOP ( 1 ) [t0].[CaseStudyId],
                 [t0].[PageID],
                 [t0].[Title],
                 [t0].[ShortDescription],
                 [t0].[LongDescription],
                 [t0].[Challenge],
                 [t0].[Solution],
                 [t0].[Results],
                 [t0].[ImageURL],
                 [t0].[Rank],
                 [t0].[Visible],
                 [t0].[ModifiedById],
                 [t0].[DateCreated],
                 [t0].[DateModified]
FROM   [dbo].[CaseStudy] AS [t0]
WHERE  [t0].[PageID] = 128 /* @p0 */

So the extra query is:

SELECT (CASE 
          WHEN EXISTS (SELECT NULL AS [EMPTY]
                       FROM   [dbo].[CaseStudy] AS [t0]
                       WHERE  [t0].[PageID] = 128 /* @p0 */) THEN 1
          ELSE 0
        END) AS [value]

Lets Improve it:

        public int GetCaseStudyIDByPageID(int pageID)
        {
            var result = _db.CaseStudies.FirstOrDefault(i => i.PageID == pageID);
            return result == null ? -1 : result.CaseStudyId;
        }

Now in the profiler, we will see only 1 statement being executed, as we removed the Any() extension method.

The same goes for counts

Old code – 2 queries to the DB:

public int GetCaseStudyIDByPageID(int pageID)
        {
            var r = from I in DB.CaseStudies
                    where I.PageID == pageID
                    select I.CaseStudyId;

            return r.Count() == 0 ? -1 : r.First();
        }

New optimised code:

      public int GetCaseStudyIDByPageID(int pageID)
        {
            var result = _db.CaseStudies.FirstOrDefault(i => i.PageID == pageID);
            return result == null ? -1 : result.CaseStudyId;
        }

Let’s improve it further by using projections to reduce the number of columns coming back, if you look at the result, we get all the columns back, which is extra data over the wire:

        public int GetCaseStudyIDByPageID(int pageID)
        {
            var result = _db.CaseStudies.Where(z => z.PageID == pageID).Select(z => (int?)z.CaseStudyId).FirstOrDefault();
            return result ?? -1;

        }

SELECT TOP ( 1 ) [t1].[value]
FROM   (SELECT [t0].[CaseStudyId] AS [value],
               [t0].[PageID]
        FROM   [dbo].[CaseStudy] AS [t0]) AS [t1]
WHERE  [t1].[PageID] = 128 /* @p0 */

This is much less columns being returned.

However, we can improve this further by creating a method to handle this for us, how about something along the lines of NullableFirstOrDefault….

        public int GetCaseStudyIDByPageID(int pageID)
        {
            var result = NullableFirstOrDefault(_db.CaseStudies.Where(z => z.PageID == pageID).Select(z => z.CaseStudyId));
            return result ?? -1;

        }


        public Nullable<T> NullableFirstOrDefault<T>(IQueryable<T> input) where T : struct
        {
            return input.Select(z => (Nullable<T>)z).FirstOrDefault();
        }

Now, we get the same result, with limited projection over the wire, but we can then use this as an extension method to optimise all nullable first or defaults.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ROMIKO.NET.LINQ.Extensions
{
    public static class LinqToSqlExtension
    {


        public static Nullable<T> NullableFirstOrDefault<T>(this IQueryable<T> input) where T : struct
        {
            return input.Select(z => (Nullable<T>)z).FirstOrDefault();
        }

        public static IQueryable<Nullable<T>> SelectNullable<T>(this IQueryable<T> input) where T : struct
        {
            return input.Select(z => (Nullable<T>)z);
        }
    }
}

and now, we can use it like this, without any null checks

        public int GetCaseStudyIDByPageID(int pageID)
        {
            var result = _db.CaseStudies.Where(z => z.PageID == pageID).Select(z => z.CaseStudyId).NullableFirstOrDefault();
            return result ?? -1;

        }

The result is the same, but easier to read code and faster queries Smile

Lets take it a step further and make it even easier to read the code by introducing extension methods that allows you to provide default values for nullable types (Matthew, you a geek!)

        public static T FirstOrDefault<T>(this IQueryable<T> input, T defaultValue) where T : struct
        {
            return input.Select(z => (Nullable<T>)z).FirstOrDefault() ?? defaultValue;
        }

So our LINQ extension class looks like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MATTHEWWILLS.NET.LINQ.Extensions
{
    public static class LinqToSqlExtension
    {


        public static Nullable<T> NullableFirstOrDefault<T>(this IQueryable<T> input) where T : struct
        {
            return input.Select(z => (Nullable<T>)z).FirstOrDefault();
        }

        public static T FirstOrDefault<T>(this IQueryable<T> input, T defaultValue) where T : struct
        {
            return input.Select(z => (Nullable<T>)z).FirstOrDefault() ?? defaultValue;
        }


        public static IQueryable<Nullable<T>> SelectNullable<T>(this IQueryable<T> input) where T : struct
        {
            return input.Select(z => (Nullable<T>)z);
        }
    }
}

Nice, now look how easy the code is to read and it is optimised on projects.

      public int GetCaseStudyIDByPageID(int pageID)
        {
            return _db.CaseStudies.Where(z => z.PageID == pageID).Select(z => z.CaseStudyId).FirstOrDefault(-1);
        }

The above produces the same result in the profiler:

SELECT TOP ( 1 ) [t1].[value]
FROM   (SELECT [t0].[CaseStudyId] AS [value],
               [t0].[PageID]
        FROM   [dbo].[CaseStudy] AS [t0]) AS [t1]
WHERE  [t1].[PageID] = 128 /* @p0 */

This anti pattern was used allot throughout their code, and caused double/triple calls to the DB for every page load. Therefore, using a profiling tool like NHProf, L2SProf or EHProf will save you and your customer/employee allot of money in the long term and perhaps save developers from picking up bad habits where IQuerable is being abused and treated like lists when in fact they execute on the backend.

We have solved this, and also provided a neat way of dealing with nullable types with clean extension methods.

So we have solved scalar issues with FirstOrDefault and the code below which is easy to write to the untrained eye will not need to be thought of when invalid checks occur on nullable types by using the custom extension methods provided above.

var result = _db.CaseStudies.FirstOrDefault(i => i.PageID == pageID).Select(x=>x.PageId);

return result ?? -1; //Invalid Check

Also, see the repository pattern being used and people ended up with code with these stats:

Above, we have +- 700 SQL Statements being called, and each on average uses a data context. Try to have a minimum amount of data contexts. One Data Context can server all sorts of requests, so when choosing a repository pattern or strategy profile the number of data contexts created and try reduce them.