How to make Entity Framework 5.0 use an in-memory persistence DbContext instead of accessing the database.

If you want to switch between in-memory stub data and a database during runtime with Entity Framework 5.0, you have several options, some of them are:

– Using a second level cache mechanism like http://www.codeproject.com/Articles/435142/Entity-Framework-Second-Level-Caching-with-DbConte , filling the cache before use and setting the expiration time to infinite.

– Creating a extension method on the DbSet class that uses only the DbSet (for direct database access) or DbSet.Local for in-memory stub data, based on some parameter.

– Implement a MemoryPersistenceDbContext and MemoryPersistenceDbSet.

 

This post will focus on the last option.

Create a new MVC4 project in Microsoft Visual Studio 2010

File > New > Project

image

 

image

image

 

Add Entity Framework 5.0 NuGet package

Rightclick solution > Manage NuGet Packages for Solution…

image

image

image

image

 

I added 2 tables to a Research database on a LocalDb SQL Server 2012 instance

image

Add *.edmx model

Right click on the models folder:

image

 

image

ModelName = ResearchModel.edmx

image

DbContext name = ResearchUow

(UOW = Unit of work)

image

image

Model namespace = ResearchModel

Check all tables

image

Add code generation item

Open the ResearchUow.edmx > right click > Add Code Generation Item…

image

Code generation item name = ResearchModel.tt

image

Now the project looks like

image

 

Add an IEntity interface

This interface will be used to make the Find function work.

image

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Mvc4Application.Models
{
public interface IEntity
{
int Id { get; }
}
}

Add a MemoryPersistenceDbSet.cs in the Models folder

This will be used to store the data in-memory instead of in the database.

image

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
namespace Mvc4Application.Models
{
public class MemoryPersistenceDbSet<T> : IDbSet<T> where T : class
{
ObservableCollection<T> _data;
IQueryable _query;
public MemoryPersistenceDbSet()
{
_data = new ObservableCollection<T>();
_query = _data.AsQueryable();
}
public virtual T Find(params object[] keyValues)
{
if (!(typeof(T) is IEntity)) { throw new ArgumentException(string.Format("Entity [{0}] does not contain a property [Id], so it could not be converted to the IEntity interface, used in this function.", typeof(T).ToString())); }
return this.SingleOrDefault(e => (e as IEntity).Id == (int)keyValues.Single());
}
public T Add(T item)
{
_data.Add(item);
return item;
}
public T Remove(T item)
{
_data.Remove(item);
return item;
}
public T Attach(T item)
{
_data.Add(item);
return item;
}
public T Detach(T item)
{
_data.Remove(item);
return item;
}
public T Create()
{
return Activator.CreateInstance<T>();
}
public TDerivedEntity Create<TDerivedEntity>() where TDerivedEntity : class, T
{
return Activator.CreateInstance<TDerivedEntity>();
}
public ObservableCollection<T> Local
{
get { return _data; }
}
Type IQueryable.ElementType
{
get { return _query.ElementType; }
}
Expression IQueryable.Expression
{
get { return _query.Expression; }
}
IQueryProvider IQueryable.Provider
{
get { return _query.Provider; }
}
IEnumerator IEnumerable.GetEnumerator()
{
return _data.GetEnumerator();
}
IEnumerator<T> IEnumerable<T>.GetEnumerator()
{
return _data.GetEnumerator();
}
}
}

Change the ResearchModel.tt file, so all generated POCO entities derive from IEntity

Open the ResearchModel.tt files and change the line

<#=codeStringGenerator.EntityClassOpening(entity)#>

to

<#=codeStringGenerator.EntityClassOpening(entity)#> : IEntity

and click save, on save of the ResearchModel.tt file, the POCO entities will be regenerated and will now all implement the IEntity interface:

image

 

Change the ResearchModel.Context.tt file, so the IResearchUow interface, the ResearchUow class and the MemoryPersistenceResearchUow will be created.

 

Open the ResearchModel.Context.tt file and find the code:


<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext

{

    public <#=code.Escape(container)#>()

        : base("name=<#=container.Name#>")

    {

<#

if (!loader.IsLazyLoadingEnabled(container))

{

#>

        this.Configuration.LazyLoadingEnabled = false;

<#

}

#>

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

        throw new UnintentionalCodeFirstException();

    }

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

    <#=codeStringGenerator.DbSet(entitySet)#>

<#

    }

    foreach (var edmFunction in container.FunctionImports)

    {

        WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);

    }

#>

}

<#

if (!String.IsNullOrEmpty(codeNamespace))

{

    PopIndent();

#>

}

<#

}

#>

 

 

Replace by

 


<#=Accessibility.ForType(container)#> partial interface IResearchlUow

{

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

    <#=codeStringGenerator.IDbSet(entitySet)#>

<#

    }

    foreach (var edmFunction in container.FunctionImports)

    {

        WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);

    }

#>

    int SaveChanges();

}

<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext, IResearchUow

{

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

    <#=codeStringGenerator.DbSet(entitySet)#>

<#

    }

    foreach (var edmFunction in container.FunctionImports)

    {

        WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);

    }

#>

    public <#=code.Escape(container)#>() : base("name=<#=container.Name#>")

    {

<#

if (!loader.IsLazyLoadingEnabled(container))

{

#>

        this.Configuration.LazyLoadingEnabled = false;

<#

}

#>

    }

    public <#=code.Escape(container)#>(string connection) : base(connection)

    {

<#

if (!loader.IsLazyLoadingEnabled(container))

{

#>

        this.Configuration.LazyLoadingEnabled = false;

<#

}

#>

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

        throw new UnintentionalCodeFirstException();

    }

}

<#=Accessibility.ForType(container)#> partial class MemoryPersistenceResearchUow : <#=code.Escape(container)#>

{

    public MemoryPersistenceResearchUow()

    {

        Seed();

    }

    public void ClearAll()

    {

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

        <#=codeStringGenerator.DbSetInConstructor(entitySet)#>

<#

    }

#>

    }

    public override int SaveChanges()

    {

        return 0;

    }

}

<#

if (!String.IsNullOrEmpty(codeNamespace))

{

    PopIndent();

#>

}

<#

}

#>

 

and find

public string DbSet(EntitySet entitySet)

{

    return string.Format(

        CultureInfo.InvariantCulture,

        "{0} DbSet<{1}> {2} {{ get; set; }}",

        Accessibility.ForReadOnlyProperty(entitySet),

        _typeMapper.GetTypeName(entitySet.ElementType),

        _code.Escape(entitySet));

}

 

Replace by

    public string IDbSet(EntitySet entitySet)

    {

        return string.Format(

            CultureInfo.InvariantCulture,

            "IDbSet<{0}> {1} {{ get; set; }}",

            _typeMapper.GetTypeName(entitySet.ElementType),

            _code.Escape(entitySet));

    }

    public string DbSet(EntitySet entitySet)

    {

        return string.Format(

            CultureInfo.InvariantCulture,

            "{0} IDbSet<{1}> {2} {{ get; set; }}",

            Accessibility.ForReadOnlyProperty(entitySet),

            _typeMapper.GetTypeName(entitySet.ElementType),

            _code.Escape(entitySet));

    }

    public string DbSetInConstructor(EntitySet entitySet)

    {

        return string.Format(

            CultureInfo.InvariantCulture,

            "this.{0} = new MemoryPersistenceDbSet<{0}>();",

            _typeMapper.GetTypeName(entitySet.ElementType));

    }

 

Now on save of the ResearchModel.Context.tt  T4 template will generate the following code:

namespace Mvc4Application.Models
{
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
public partial interface IResearchUow
{
IDbSet<Car> Car { get; set; }
IDbSet<Person> Person { get; set; }
int SaveChanges();
}
public partial class ResearchUow : DbContext, IResearchUow
{
public IDbSet<Car> Car { get; set; }
public IDbSet<Person> Person { get; set; }
public ResearchUow() : base("name=ResearchUow")
{
}
public ResearchUow(string connection) : base(connection)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
}
public partial class MemoryPersistenceResearchUow : ResearchUow
{
public MemoryPersistenceResearchUow()
{
Seed();
}
public void ClearAll()
{
this.Car = new MemoryPersistenceDbSet<Car>();
this.Person = new MemoryPersistenceDbSet<Person>();
}
public override int SaveChanges()
{
return 0;
}
}
}

 

Add a partial class file for the MemoryPersistenceResearchUow

Every time you update the ReserachModel.edmx from the database or save the T4 templates ResearchModel.tt and ResearchModel.Context.tt, the T4 templates will execute and regenerate all POCO entities and the IResearchUow interface, ResearchUow class and the MemoryPersistenceResearchUow.  To prevent the code that seeds the in-memory UOW to be overwritten a partial class MemoryPersistenceResearchUow is created.

image

public partial class MemoryPersistenceResearchUow
{
public void Seed()
{
ClearAll();
// TODO Add seed logic here, like.....
this.Person.Add(new Person { Id = 1, Name = "Roel van Lisdonk" });
this.Car.Add(new Car { Id = 1, NumberPlate = "8-KJA-00", PersonId = 1 });
}
}

Add a IResearchUowFactory and ResearchUowFactory that will contain the logic to create a ResearchUow or an MemoryPersistenceResearchUow.

namespace Mvc4Application.Models
{
public interface IResearchUowFactory
{
IResearchUow GetResearchUow();
}
}
using System;
using System.Web.Configuration;
namespace Mvc4Application.Models
{
public class ResearchUowFactory : IResearchUowFactory
{
public IResearchUow GetResearchUow()
{
string key = "UseStubs";
string result = WebConfigurationManager.AppSettings[key];
if (result == null) { throw new ApplicationException(string.Format("Could not find AppSetting[{0}].", key)); }
bool useStubs = bool.Parse(result);
return useStubs ? new MemoryPersistenceResearchUow() : new ResearchUow();
}
}
}

 

The project will no look like:

image

 

Add appSetting "UseStubs" to the Web.config

  <appSettings>
<add key="UseStubs" value="true" />

Install an IoC container by using NuGet, in this case I will use ninject:

Install Ninject and Ninject.MVC3 (no MVC4 available yet, but works just fine) this will also install Ninject.Web.Common.

image

In the App_Start folder change the NinjectWebCommon.cs

Fill the RegisterService function:

/// <summary>
/// Load your modules or register your services here!
/// </summary>
/// <param name="kernel">The kernel.</param>
private static void RegisterServices(IKernel kernel)
{
kernel.Bind<IResearchUowFactory>().To<ResearchUowFactory>();
} 

In the Controllers folder change the HomeControler, add:

private readonly IResearchUowFactory _researchUowFactory;
private readonly IResearchUow _researchUow;
public HomeController(IResearchUowFactory researchUowFactory)
{
_researchUowFactory = researchUowFactory;
_researchUow = _researchUowFactory.GetResearchUow();
}
public ActionResult Index()
{
Person firstPerson = _researchUow.Person.First();
ViewBag.Message = string.Format("First person name [{0}].", firstPerson.Name);
return View();
}

Result

This results in:

image

 

The text "First person name [Roel van Lisdonk]" is shown. This was the data from the seed method:

this.Person.Add(new Person { Id = 1, Name = "Roel van Lisdonk" });

and not from the real database, because the database at this point is empty.

This proves we can switch using in-memory stub data or the real database by changing a appSetting in the web.config.

 

 

 

Don’t forget to fix the unit tests.

In the unit tests for the HomeController, change the lines:

HomeController controller = new HomeController();

to

HomeController controller = new HomeController(new ResearchUowFactory());

 

 

Now you are able to use the ResearchUow within your HomeController and switch between the MemoryPersistenceResearchUow and the ResearchUow by changing the appSetting UseStubs.

How to convert / cast the current server date and time to a varchar in the format YYYY-MM-DDThh:mm:ss[+|-]hh:mm in T-SQL

 

To convert the current machine date and time to a varchar in the format YYYY-MM-DDThh:mm:ss[+|-]hh:mm in T-SQL, you can use the following code:

 

select convert(varchar(100), cast(sysdatetimeoffset() as datetimeoffset(0)), 126)

 

Result (in Amsterdam in Summer time)

2012-08-13T15:23:09+02:00