Singleton with Entity Framework : Will Queries run multiple times?

Question

With a model such as ...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.ComponentModel.DataAnnotations;

namespace Singleton
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = new ModelBuilder();

            builder.Configurations.Add(new TemplateConfiguration());
            builder.Configurations.Add(new UserConfiguration());
            builder.Configurations.Add(new UnitConfiguration());
            builder.Configurations.Add(new AttributeConfiguration());

            var model = builder.CreateModel();

            using (var context = new SampleDataContext(model))
            {
                bool updating = true;
                if (updating)
                {
                    var units = new List<Unit>
                {
                    new Unit{ Name = "Unit1" },
                    new Unit{ Name = "Unit2" }
                };

                    units.ForEach(x => { context.Units.Add(x); });
                    context.SaveChanges();

                    var templates = new List<Template>
                {
                    new Template{
                        Name = "Default",
                        Attributes = new List<Attribute>
                        {
                            new Attribute
                            {
                                Unit = context.Units.Single( i => i.Name == "Unit1" )
                            }
                        }
                    }
                };

                    templates.ForEach(x =>
                    {
                        context.Templates.Add(x);
                    });
                    context.SaveChanges();
                    var users = new List<User>
                {
                    new User
                    {
                        Name = "Stacey"
                    },
                    new User
                    {
                        Name = "Daniel"
                    },
                    new User
                    {
                        Name = "Derek"
                    }
                };

                    users.ForEach(x => { context.Users.Add(x); });
                    context.SaveChanges();

                    updating = !updating; // stop updating
                }

                if (!updating)
                {
                    Single.Instance = context.Templates.Single(i => i.Name == "Default");
                }

                foreach (User user in context.Users)
                {
                    Console.WriteLine(user.Template.Name); // does template requery?
                }
            }
        }
    }
    public class Template
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual ICollection<Attribute> Attributes { get; set; }
    }

    public class TemplateConfiguration : EntityConfiguration<Template>
    {
        public TemplateConfiguration()
        {
            HasKey(k => k.Id);
            Property(k => k.Id).IsIdentity();
            Property(k => k.Name);

            //// map the collection entity
            HasMany(k => k.Attributes).WithRequired()
                .Map("template.attributes",
                    (template, attribute) => new
                    {
                        Template = template.Id,
                        Attribute = attribute.Id
                    });

            MapSingleType(c => new
            {
                c.Id,
                c.Name
            }).ToTable("templates");
        }
    }

    public class User
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }

        [StoreGenerated(StoreGeneratedPattern.None)]
        public Template Template { get { return Single.Instance; } }
    }

    public class UserConfiguration : EntityConfiguration<User>
    {
        public UserConfiguration()
        {
            HasKey(k => k.Id);
            Property(k => k.Id).IsIdentity();
            Property(k => k.Name);

            MapSingleType(c => new
            {
                c.Id,
                c.Name
            }).ToTable("users");
        }
    }

    public class Unit
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

    public class UnitConfiguration : EntityConfiguration<Unit>
    {
        public UnitConfiguration()
        {
            HasKey(k => k.Id);
            Property(k => k.Id).IsIdentity();
            Property(k => k.Name);

            MapSingleType(c => new
            {
                c.Id,
                c.Name
            }).ToTable("units");
        }
    }

    public class Attribute
    {
        public virtual int Id { get; set; }
        public Unit Unit { get; set; }
    }

    public class AttributeConfiguration : EntityConfiguration<Attribute>
    {
        public AttributeConfiguration()
        {
            HasKey(k => k.Id);
            Property(k => k.Id).IsIdentity();
            // Initialize the Statistic
            HasRequired(k => k.Unit);

            // map the data type to the context so that it can be queried.
            MapHierarchy(c => new
            {
                c.Id,
                Unit = c.Unit.Id
            }).ToTable("attributes");
        }
    }
    public class Single
    {
        public static Template Instance;
    }

    public class SampleDataContext : DbContext
    {
        public SampleDataContext(DbModel model)
            : base(model)
        {
            this.ObjectContext.ContextOptions.LazyLoadingEnabled = true;
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<Template> Templates { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<Unit> Units { get; set; }
        public DbSet<Attribute> Attributes { get; set; }
    }
}

if I assign Singleton.Instance to a query from the DataContext, and then assign the Singleton.Instance to other objects in my code, will the SQL be run once, or each time it is accessed? Can anyone help me here to see if this pattern is going to save some SQL?

Answer 1

The SQL will run each time you access it. What you'r looking for is some kind of caching strategy.

Have a look at the following thread. I think it will help you: http://stackoverflow.com/questions/1147681/how-to-make-entity-framework-cache-some-objects

Answer 2

From context.SomeQuery, you're almost certainly just returning some kind of queryable object or other iterable (I'm not sure your example reflects how your EF solution is actually architected, I think some elements were lost for the sake of brevity). So, every time you access this singleton, you're just going to iterate over it (run the query) again.

I recommend you use a simple memoization revolving around 4.0 caching, try this.