Web API OData V4 Using Unity IoC, SQLite with EF6 and OData Model Aliasing Part 5

This post is part 5 of the Web API and OData V4 series. This article shows how Web API 2.2 with OData V4 can be used together with Unity as an IoC, Entity Framework with SQLite for persistence and also creates an OData service which can do CRUD operations.

OData Model Aliasing features are used to map the entities in the controller.

Part 1 Getting started with Web API and OData V4 Part 1.
Part 2 Web API and OData V4 Queries, Functions and Attribute Routing Part 2
Part 3 Web API and OData V4 CRUD and Actions Part 3
Part 4 Web API OData V4 Using enum with Functions and Entities Part 4
Part 5 Web API OData V4 Using Unity IoC, SQLite with EF6 and OData Model Aliasing Part 5
Part 6 Web API OData V4 Using Contained Models Part 6
Part 7 Web API OData V4 Using a Singleton Part 7
Part 8 Web API OData V4 Using an OData T4 generated client Part 8
Part 9 Web API OData V4 Caching Part 9
Part 10 Web API OData V4 Batching Part 10
Part 11 Web API OData V4 Keys, Composite Keys and Functions Part 11

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

Setting up SQLite and Entity Framework

Install the SQLite NuGet package:

This package also installs Entity Framework.

Add the animals.sqlite database to the App_Data folder.

If you want to create the database, see the details here: Using SQLite with Entity Framework 6 and the Repository Pattern

Now create the entities which will be used for the OData service:

AnimalType Entity: (The Data attributes are only required for odata model aliasing.)

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Runtime.Serialization;

namespace WebAPIODataV4SQLite.DomainModel
{
    [DataContract(Name = "AnimalType")]
    public class AnimalType
    {
        public AnimalType()
        {
            EventDataValues = new List<EventData>();
        }

        [DataMember(Name = "Key")]
        [Key]
        public long Id { get; set; }

        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public double MeanCost { get; set; }

        [DataMember(Name = "EventData")]
        public virtual ICollection<EventData> EventDataValues { get; set; }
    }
}

EventData Entity:

namespace WebAPIODataV4SQLite.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; }
    }
}

The application now requires an entity context. This context turns off the migrations features.

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

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

        public DbSet<EventData> EventDataEntities { get; set; }
        public DbSet<AnimalType> AnimalTypeEntities { get; set; }

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

    }
}

Entity Framework needs to be reconfigured to use the SQLite providers and also set the connection string in the web.config. Remove/ replace the default configurations with this:

 <connectionStrings>
    <add name="SqliteContext" connectionString="Data Source=|DataDirectory|\animals.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
  <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>
 
  <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>

Now Entity frameowrk is ready to be used with SQLite.

Setting up Unity and OWIN

To use Unity with OData data, download the Unity.AspNet.WebApi package:

Delete the UnityActivator class.

Remove the Web API global register from the Application_Start method in the Global.asax file.

Add a new class WebApiUnityActionFilterProvider. This class is used, if the attributes required DI.

using System.Collections.Generic;
using System.Web.Http;
using System.Web.Http.Controllers;
using System.Web.Http.Filters;
using Microsoft.Practices.Unity;

namespace WebAPIODataV4SQLite.App_Start
{
    public class WebApiUnityActionFilterProvider : ActionDescriptorFilterProvider, IFilterProvider
    {
        private readonly IUnityContainer container;

        public WebApiUnityActionFilterProvider(IUnityContainer container)
        {
            this.container = container;
        }

        public new IEnumerable<FilterInfo> GetFilters(HttpConfiguration configuration, HttpActionDescriptor actionDescriptor)
        {
            var filters = base.GetFilters(configuration, actionDescriptor);
            var filterInfoList = new List<FilterInfo>();

            foreach (var filter in filters)
            {
                container.BuildUp(filter.Instance.GetType(), filter.Instance);
            }

            return filters;
        }
    }
}

Change the WebApiConfig class. Replace it with the code underneath. Now Unity is used for the Web API service. The Register method needs to be added to OWIN.

using System.Linq;
using System.Web.Http;
using System.Web.Http.Filters;
using System.Web.OData.Builder;
using System.Web.OData.Extensions;
using Microsoft.OData.Edm;
using Microsoft.Practices.Unity.WebApi;
using WebAPIODataV4SQLite.App_Start;
using WebAPIODataV4SQLite.DomainModel;

namespace WebAPIODataV4SQLite
{
    public static class WebApiConfig
    {
        public static HttpConfiguration Register()
        {
            var config = new HttpConfiguration();
            config.MapHttpAttributeRoutes();
            config.DependencyResolver = new UnityDependencyResolver(UnityConfig.GetConfiguredContainer());
            RegisterFilterProviders(config);

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );

            config.MapODataServiceRoute("odata", "odata", model: GetModel());
            return config;
        }

        public static IEdmModel GetModel()
        {
            ODataModelBuilder builder = new ODataConventionModelBuilder();

            builder.EntitySet<AnimalType>("AnimalType");
            builder.EntitySet<EventData>("EventData");

            return builder.GetEdmModel();
        }

        private static void RegisterFilterProviders(HttpConfiguration config)
        {
            var providers = config.Services.GetFilterProviders().ToList();
            config.Services.Add(typeof(System.Web.Http.Filters.IFilterProvider), new WebApiUnityActionFilterProvider(UnityConfig.GetConfiguredContainer()));
            var defaultprovider = providers.First(p => p is ActionDescriptorFilterProvider);
            config.Services.Remove(typeof(System.Web.Http.Filters.IFilterProvider), defaultprovider);
        }
    }
}

Create a new OWIN startup class. This class starts the Web API OWIN middleware.

using Microsoft.Owin;
using Owin;

[assembly: OwinStartup(typeof(WebAPIODataV4SQLite.Startup))]

namespace WebAPIODataV4SQLite
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            app.UseWebApi(WebApiConfig.Register());
        }
    }
}

Now the required classes can be registered with Unity.

using System;
using Microsoft.Practices.Unity;
using Microsoft.Practices.Unity.Configuration;
using WebAPIODataV4SQLite.DomainModel;

namespace WebAPIODataV4SQLite.App_Start
{
    public class UnityConfig
    {
        #region Unity Container
        private static Lazy<IUnityContainer> container = new Lazy<IUnityContainer>(() =>
        {
            var container = new UnityContainer();
            RegisterTypes(container);
            return container;
        });

        public static IUnityContainer GetConfiguredContainer()
        {
            return container.Value;
        }
        #endregion

        public static void RegisterTypes(IUnityContainer container)
        {
            container.RegisterType<SqliteContext, SqliteContext>();
        }
    }
}

Add the OData model and Controllers

The OData model has already been defined and created in the WebApiConfig.Register method. The GetModel is where the model is created for OData.

public static IEdmModel GetModel()
{
 ODataModelBuilder builder = new ODataConventionModelBuilder();

 builder.EntitySet<AnimalType>("AnimalType");
 builder.EntitySet<EventData>("EventData");

 return builder.GetEdmModel();
}

Now a simple controller can be created which implements CRUD for OData. The Entity context is added in the constructor using Unity.

using System.Data.Entity.Migrations;
using System.Linq;
using System.Net;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.OData;
using System.Web.OData.Query;
using System.Web.OData.Routing;
using WebAPIODataV4SQLite.DomainModel;

namespace WebAPIODataV4SQLite.Controllers
{
    public class EventDataController : ODataController
    {
        readonly SqliteContext _sqliteContext;

        public EventDataController(SqliteContext sqliteContext)
        {
            _sqliteContext = sqliteContext;
        }

        [EnableQuery(PageSize = 20)]
        public IHttpActionResult Get()
        {
            return Ok(_sqliteContext.EventDataEntities.AsQueryable());
        }

        [HttpPost]
        [ODataRoute("EventData")]
        public async Task<IHttpActionResult> CreateEventData(EventData eventData)
        {
            if (eventData !=null &amp;&amp; !ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            _sqliteContext.EventDataEntities.Add(eventData);
            await _sqliteContext.SaveChangesAsync();

            return Created(eventData);
        }

        [EnableQuery(PageSize = 20)]
        public IHttpActionResult Get([FromODataUri] int key)
        {
            return Ok(_sqliteContext.EventDataEntities.Find(key));
        }

        [HttpPut]
        [ODataRoute("EventData")]
        public async Task<IHttpActionResult> Put([FromODataUri] int key, EventData eventData)
        {
            if (eventData != null &amp;&amp; !ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            if (!_sqliteContext.EventDataEntities.Any(t => t.EventDataId == eventData.EventDataId &amp;&amp; t.EventDataId == key))
            {
                return Content(HttpStatusCode.NotFound, "NotFound");
            }

            _sqliteContext.EventDataEntities.AddOrUpdate(eventData);
            await _sqliteContext.SaveChangesAsync();

            return Updated(eventData);
        }

        [HttpPut]
        [ODataRoute("EventData")]
        public async Task<IHttpActionResult> Patch([FromODataUri] int key, Delta<EventData> delta)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            if (!_sqliteContext.EventDataEntities.Any(t =>  t.EventDataId == key))
            {
                return Content(HttpStatusCode.NotFound, "NotFound");
            }

            var eventData = _sqliteContext.EventDataEntities.Single(t => t.EventDataId == key);
            delta.Patch(eventData);
            await _sqliteContext.SaveChangesAsync();

            return Updated(eventData);
        }

        [HttpDelete]
        [ODataRoute("EventData")]
        public async Task<IHttpActionResult> Delete([FromODataUri] int key)
        {
            var entity = _sqliteContext.EventDataEntities.FirstOrDefault(t => t.EventDataId == key);
            if (entity == null)
            {
                return Content(HttpStatusCode.NotFound, "NotFound");
            }

            _sqliteContext.EventDataEntities.Remove(entity);
            await _sqliteContext.SaveChangesAsync();

            return Content(HttpStatusCode.NoContent, "Deleted");
        }

        protected override void Dispose(bool disposing)
        {
            _sqliteContext.Dispose();
            base.Dispose(disposing);
        }
    }
}

The Controller can then be tested using Fiddler or your browser for Http GET.

It is important to use Prefer: return-content in the header, if you want to recieve responses as you expect, otherwise no-contents will be returned.

OData Model Aliasing
We want to be able to select an EventData entity from AnimalType(3). The following URL will be used:

http://localhost:59145/odata/AnimalType(2)/EventData

To do this, a new method is created in the AnimalTypeController:

[HttpGet]
[ODataRoute("AnimalType({key})/EventData")]
[EnableQuery(PageSize = 20)]      
public IHttpActionResult GetEventData([FromODataUri] int key)
{
 return Ok(_sqliteContext.AnimalTypeEntities.Find(key).EventDataValues);
}

This method looks in the AnimalType entity for the collection with the property called EventData. This is only found because model aliasing is used. The AnimalType entity is defined with DataContract and DataMember attributes:

[DataContract(Name = "AnimalType")]
public class AnimalType
{
        public AnimalType()
        {
            EventDataValues = new List<EventData>();
        }

        [DataMember(Name = "Key")]
        [Key]
        public long Id { get; set; }

        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public double MeanCost { get; set; }

        [DataMember(Name = "EventData")]
        public virtual ICollection<EventData> EventDataValues { get; set; }
}

You can see that the EventDataValues collection is mapped to EventData. Without this attribute, the get in AnimalType controller would be mapped to EventDataValues and also the route. By using OData Model Aliasing, the entites $ref and the routes can be controlled/defined or changed.

Here’s how it looks: