using SQLite with Entity Framework 6 and the Repository Pattern

In this post, a Data access layer is implemented using the repository pattern together with entity Framework 6 and SQLite as a database. The post continues on from Getting started, using SQLite with .NET

UPDATE 02.03.2014: Using the new SQLite version 1.0.91.3 now with the SQLite provider. The xml app configuration needs to be changed due to this. Updated all other NuGet packages as well.

UPDATE 02.07.2014: Using the new SQLite version 1.0.93.0 now with the SQLite provider. The xml app configuration needs to be changed due to this. Updated all other NuGet packages as well.

Code: https://github.com/damienbod/AnimalMarketEnginsSqliteDb

A SQLite database can be created using the Firefox addon.

CREATE TABLE "AnimalType" 
(
 "Id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , 
 "Name" TEXT NOT NULL  DEFAULT Unknown, 
 "MeanCost" DOUBLE NOT NULL  DEFAULT 100.0
)

CREATE TABLE "EventData" 
(
 "EventDataId" INTEGER PRIMARY KEY  NOT NULL ,
 "Factor" int NOT NULL ,
 "StringTestId" NVARCHAR(160) NOT NULL ,
 "FixChange" DOUBLE NOT NULL  DEFAULT (0.0) ,
 "AnimalTypeId" INTEGER NOT NULL ,
 FOREIGN KEY ([AnimalTypeId]) REFERENCES [AnimalType] ([Id]) 
   ON DELETE NO ACTION ON UPDATE NO ACTION
)

Application data can then be added directly using this tool:

The .sqlite file is added to the project and ‘copied if newer’ when built.

OLD no longer required, but still works
The required NuGet packages need to be added. (At present the Sysem.Data.SQLite.Linq can only be got from https://www.myget.org/F/bricelam/ or you have to build the src itself.)
See http://brice-lambson.blogspot.ch/2013/06/systemdatasqlite-on-entity-framework-6.html

UPDATE: A new version of SQLite 1.0.91.3 has been released (available in NuGet, Version now works with the latest version of Entity Framework 6.0.2).

And you also require Entity Framework version 6.0.2.

A domain model can then be created. In this example it is a very simple 1:n model with just 2 tables.

using System.Collections.Generic;

namespace AnimalMarketDal.DomainModel
{
    public class AnimalType
    {
        public AnimalType()
        {
            EventDataValues = new List<EventData>();
        }
 
        public long Id { get; set; }
        public string Name { get; set; }
        public double MeanCost { get; set; }
        
        public virtual ICollection<EventData> EventDataValues { get; set; }
    }
}

namespace AnimalMarketDal.DomainModel
{
    public class EventData
    {
        public long EventDataId { get; set; }
        public int Factor { get; set; }
        public string StringTestId { get; set; }
        public double FixChange { get; set; }

        public long AnimalTypeId { get; set; }
        public virtual AnimalType AnimalType { get; set; }
    }
}

Now create a DbContext. It is important to turn off the migrations as this is not code first.

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using AnimalMarketDal.DomainModel;

namespace AnimalMarketDal.Dal
{
    public class AnimalContext  : DbContext
    {
        public AnimalContext()
        {
            // Turn off the Migrations, (NOT a code first Db)
            Database.SetInitializer<AnimalContext>(null);
        }

        public DbSet<AnimalType> AnimalTypes { get; set; }
        public DbSet<EventData> EventDataValues { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Database does not pluralize table names
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }
    
    } 
}

The repository layer is then created. The context is added using Unity and construction injection with a transient lifecycle. This needs to be disposed, so the IDisposable is also implemented.

using System;
using System.Collections.Generic;
using AnimalMarketDal.DomainModel;

namespace AnimalMarketDal.Repo
{
    public interface IEventDataRepository : IDisposable
    {
        AnimalType GetCowType();

        AnimalType GetPigType();

        AnimalType GetLambType();

        IEnumerable<EventData> GetPigData();

        IEnumerable<EventData> GetLambData();

        IEnumerable<EventData> GetCalfData();
    }
}

And the implementation:

using System;
using System.Collections.Generic;
using System.Linq;
using AnimalMarketCommon.Attributes;
using AnimalMarketDal.Dal;
using AnimalMarketDal.DomainModel;

namespace AnimalMarketDal.Repo
{
    [LifecycleTransient]
    public class EventDataRepository : IEventDataRepository
    {
        private const int PigId = 1;
        private const int CalfId = 2;
        private const int LambId = 0;

        private readonly AnimalContext _context;

        public EventDataRepository(AnimalContext context)
        {
            _context = context;
        }

        private IEnumerable<AnimalType> GetAnimalTypes()
        {
            return from a in _context.AnimalTypes select a;
        }

        public AnimalType GetCowType()
        {
            return GetAnimalTypes().FirstOrDefault(a => a.Id.Equals(CalfId));
        }

        public AnimalType GetPigType()
        {
            return GetAnimalTypes().FirstOrDefault(a => a.Id.Equals(PigId));
        }

        public AnimalType GetLambType()
        {
            return GetAnimalTypes().FirstOrDefault(a => a.Id.Equals(LambId));
        }

        public IEnumerable<EventData> GetPigData()
        {
            return from a in _context.EventDataValues
                   where a.AnimalTypeId.Equals(PigId)
                          orderby a.StringTestId
                          select a;
        }

        public IEnumerable<EventData> GetLambData()
        {
            return from a in _context.EventDataValues
                   where a.AnimalTypeId.Equals(LambId)
                   orderby a.StringTestId
                   select a;
        }

        public IEnumerable<EventData> GetCalfData()
        {
            return from a in _context.EventDataValues
                   where a.AnimalTypeId.Equals(CalfId)
                   orderby a.StringTestId
                   select a;
        }

        public void SaveChanges()
        {
            _context.SaveChanges();
        }

        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    _context.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}

In this example attributes are used to define the different classes which are to be created and its defined lifecycle; singleton = LifecycleSingletonAttribute and transient = LifecycleTransientAttribute. The AnimalContext is registered as a transient type.

public static void RegisterTypes(IUnityContainer container)
        {
		    // Add your register logic here...
            var myAssemblies = AppDomain.CurrentDomain.GetAssemblies().Where(a => a.FullName.StartsWith("AnimalMarket")).ToArray();

            container.RegisterType(typeof(Startup));

            container.RegisterTypes(
                 UnityHelpers.GetTypesWithCustomAttribute<LifecycleSingletonAttribute>(myAssemblies),
                 WithMappings.FromMatchingInterface,
                 WithName.Default,
                 WithLifetime.ContainerControlled,
                 null
                ).RegisterTypes(
                        UnityHelpers.GetTypesWithCustomAttribute<LifecycleTransientAttribute>(myAssemblies),
                        WithMappings.FromMatchingInterface,
                        WithName.Default,
                        WithLifetime.Transient);

            container.RegisterType(typeof(AnimalContext));
        }

Reflection is used to search for the attributes.


        public static IEnumerable<Type> GetTypesWithCustomAttribute<T>( Assembly[] assemblies)
        {
            foreach (var assembly in assemblies)
            {
                foreach (Type type in assembly.GetTypes())
                {
                    if (type.GetCustomAttributes(typeof(T), true).Length > 0)
                    {
                        yield return type;
                    }
                }
            }
        }

The Entity SQLite DbProviderFactories is defined in the application config file.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  
 <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.93.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <connectionStrings>
    <add name="AnimalContext" connectionString="Data Source=.\animals.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".Net Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    </DbProviderFactories>
  </system.data>

  
</configuration>

The Web Api can then be tested using Fiddler. Here’s an example of the results: