Lazy Loading Data

Summary

In this blog post I’m going to demonstrate how to lazy load your data using Entity Framework and SQL Server.  The purpose is to write an object that returns program settings stored in a table, but read from the table only once during the lifetime of the program.

The Data

In a database named “sampledata” I’m going to create a table that contains true/false settings for any number of settings.  This is a simple example for the purposes of showing how to lazy load.  So I will be “pretending” that there is a purpose for these settings even though the demo program doesn’t use any of it.

You can use the following SQL script to generate a table to run this program: 

CREATE TABLE [dbo].[ProgramSetting](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Setting] [bit] NOT NULL,
 CONSTRAINT [PK_ProgramSetting] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO ProgramSetting (Name,Setting) values ('RequirePassword',1)
INSERT INTO ProgramSetting (Name,Setting) values ('ShowUserName',0)
INSERT INTO ProgramSetting (Name,Setting) values ('ShowUserPicture',0)
INSERT INTO ProgramSetting (Name,Setting) values ('AllowUserToChangePicture',1)

The Class

Now, I’m going to design an object that will be used throughout a program to read settings from the database and return the true/false value.  Here’s the basic class: 

public class ConfigurationSettings
{
    private static List<string> _Data = null;
    private static List<string> Data
    {
        get
        {
            if (_Data == null)
            {
                using (var db = new ConfigDataContext())
                {
                    _Data = (from ps in db.ProgramSettings 
                             where ps.Setting 
                             select ps.Name).ToList();
                }
            }
            return _Data;
        }
    }

    public static bool RequirePassword
    {
        get 
        {
            return Data.Contains("RequirePassword");
        }
    }

    public static bool ShowUserName
    {
        get
        {
            return Data.Contains("ShowUserName");
        }
    }

    public static bool ShowUserPicture
    {
        get
        {
            return Data.Contains("ShowUserPicture");
        }
    }

    public static bool AllowUserToChangePicture
    {
        get
        {
            return Data.Contains("AllowUserToChangePicture");
        }
    }
}

Now, if you look at the code carefully, you’ll see that there is a property that gets the raw data using LINQ and Entity Framework.  You can download the sample code to see the code for the context and the table.  For this blog post all you need to know is that all the data from the ProgramSettings table is read at one time.  It will occur the first time a configuration parameter is read.  Why?  Because the Data.Contains() will call the Data getter which will then check to see if the local variable named _Data is null.  The first time a parameter is read, this will be null and the data will be red into _Data.  The next parameter that is read will cause the _Data to be returned without any read to the database.

The list that is maintained in the variable _Data is just a list of the names of configuration parameters that returned true.  If the parameter is missing or they are false, then we’ll return a false to the calling program.  This can be convenient for new features that can be added to the code first.  They’ll be false until you put parameters in the database and you won’t be stuck with a chicken or the egg problem of when to insert the data into the ProgramSetting table.

Obviously, this works for data that is mostly static.  If you include this code in a stand-alone program and you change a configuration setting in your database, you’ll need to exit from any previously running programs before the changes will take affect.

Another possible problem involves the fact that your configuration parameter names are coded as well as stored in the database.  That means that changing a name in the database, adding a name or deleting a name requires some matching code changes.  This can get tedious if the list of configuration parameters is large.  You could write a program to generate the parameters and just regenerate the parameter list every time you make changes to the database and then update your software.  You can also opt to use some sort of read parameter method that requires the parameter name in a string passed in.  Most likely, you’ll be making changes to your code if you’re adding, deleting or modifying a configuration parameter, so it makes no real difference.

How to Get the Code

You can download the sample code at my GitHub account by clicking here.  Be sure to go to the context source file and change the database connection string to match your own SQL server.

Leave a Reply