Creating Web API Using Code-First Approach In Entity Framework

In this article, we will create a database with a foreign key constraint (one to many relationship), using Code First technique in an Entity Framework, seed some sample data and return the data, using the Web API.

Before starting, I assume that you have some knowledge of an Entity Framework code first technique, if not you can learn it here.

Entity Framework

Entity Framework is a relational mapper framework. It is an enhancement of ADO.NET, which gives developers an automated mechanism to access and store the data in the database.

Web API

ASP.NET Web API is a framework to build HTTP Service, which reaches a broad range of clients including Browsers and mobile devices.

It is an ideal platform to build RESTtful Services.

Let's start.

Take an empty Web API project and name it.

Click OK and select the empty template with Web API.

This will create an empty project with Web API.

Now, start Package Manager Console.

In Package Manger Console, type Install-Package EntityFramework.

Once an Entity Framework is installed, type Install-Package Newtonsoft.Json and install it as well.

Note

Newtonsoft.JSON is a framework, which is used to serialize or deserialize the objects in JSON format. For more information, click here.

Now, add a connection string in web.config file, as shown below. 

<connectionStrings>
    <add connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDatabase;Integrated Security=True" name="DefaultConnection" providerName="System.Data.SqlClient" />
  </connectionStrings>

Note

When an Application will run for first time, you can see the database in SQL Server Object Explorer under MSSQLLocalDB>Database>TestDatabase.

Now, add a class file in Models folder.

Name it as entites.cs.

Now, replace the code of Entites.cs class with the code given below. 

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

namespace Test.Models
{
    public class Country
    {
        [Key]
        public int CountryId { get; set; }
        public string Name { get; set; }
        public List<State> State { get; set; }
    }

    public class State
    {
        [Key]
        public int StateId { get; set; }
        public string Name { get; set; }
        public List<City> City { get; set; }
        //Adding Foreign Key constraints for country
        public int CountryId { get; set; }
        public Country Country { get; set; }
    }

    public class City
    {
        [Key]
        public int CityId { get; set; }
        public string Name { get; set; }
        //Adding Foreign Key Constraints for State
        public int StateId { get; set; }
        public State State { get; set; }
    }
}

Note

[Key] defines the Primary key and Table

public int StateId { get; set; } Defines the Foreign Key

public State State { get; set; } Show the relation.

Now, add a folder in root directory and name it as Context.

Now, add DatabaseContext.cs class. In context folder, replace the code with the code given below. 

using System.Data.Entity;
using Test.Models;

namespace Test.Context
{
    public class DatabaseContext : DbContext
    {
        public DatabaseContext() : base("DefaultConnection") { }

        public DbSet<Country> Countries { get; set; }
        public DbSet<State> States { get; set; }
        public DbSet<City> Cities { get; set; }
    }
}

DatabaseContext extends DbContext class. This is the main class, which implements all Entity Framework functionality. For more information about DbContext, read this.

Each DbSet<T> represents a table in the database.

Now, add a class in Context folder and name it as DatabaseInitializer.cs.

Now, replace the code of DatabaseInitializer.cs clsass with the code given below. 

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using Test.Models;

namespace Test.Context
{
    public class DatabaseInitializer : DropCreateDatabaseIfModelChanges<DatabaseContext>
    {
        protected override void Seed(DatabaseContext context)
        {
            base.Seed(context);

            var cityInMaharashtra = new List<City> {
                new City {Name="Mumbai" },
                                new City {Name= "Pune" }
            };
            var cityInUttarPradesh = new List<City> {
                new City {Name="Lucknow" },
                                new City {Name="Banaras" }
            };
            var cityInTamilnadu = new List<City> {
                new City {Name="Bangaluru" },
                                new City {Name="Chennai" }
            };
            var cityInUttaranchal = new List<City> {
                new City {Name="Dehradun" },
                                new City {Name="Rishikesh" }
            };
            var cityInPanjab = new List<City> {
                new City {Name="Chandigarh" },
                                new City {Name="Ludhiana" }
            };
            var stateInIndia = new List<State> {
                new State {
                    Name="Maharashtra",City=cityInMaharashtra
                },
                new State {
                    Name="Uttar Pradesh",City=cityInUttarPradesh
                },
                new  State {
                    Name="Tamil nadu",City=cityInTamilnadu
                },
                new  State {
                    Name="Uttaranchal",City=cityInUttaranchal
                },
                new  State {
                    Name="Panjab",City=cityInPanjab
                }
            };
            Country country = new Country
            {
                Name = "India",
                State = stateInIndia
            };
            context.Countries.Add(country);
            context.SaveChanges();
        }
    }
}

This file has some sample data inside the seed Method. When the application runs for the first time, Code First Migration will create tables specified in DatabaseContext class and the data from seed method will be inserted.

DatabaseInitializer.cs will be called in Application_Start() function in global.asax file, as shown below. 

using System.Web.Http;
using Test.Context;

namespace Test
{
    public class WebApiApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            GlobalConfiguration.Configure(WebApiConfig.Register);
            System.Data.Entity.Database.SetInitializer(new DatabaseInitializer());
        }
    }
}

Now, our database design has been done.

Let' create our API to return data in JSON format.

Add a Web API Controller inside controller folder.

Name it as DefaultController.

Now, replace the DefaultController.cs class code with the code given below. 

using System;
using System.Linq;
using System.Web.Http;
using Test.Context;

namespace Test.Controllers
{
    public class DefaultController : ApiController
    {
        //Creating Instance of DatabaseContext class
        private DatabaseContext db = new DatabaseContext();

        //Creating a method to return Json data
        [HttpGet]
        public IHttpActionResult Get()
        {
            try
            {
                //Prepare data to be returned using Linq as follows
                var result = from country in db.Countries
                             select new {
                                 country.CountryId,
                                 country.Name,
                                 State = from state in db.States
                                         where state.CountryId==country.CountryId
                                         select new {
                                             state.StateId,
                                             state.Name,
                                             City=from city in db.Cities
                                                  where city.StateId==state.StateId
                                                  select new {
                                                      city.CityId,
                                                      city.Name
                                                  }
                                         }
                             };
                return Ok(result);
            }
            catch (Exception)
            {
                //If any exception occurs Internal Server Error i.e. Status Code 500 will be returned
                return InternalServerError();
            }
        }
    }
}

The code given above has a Get method, which will be invoked when the user will request the URL given below.

Localhost:3000/api/Default

Note:

• Port number 3000 is randomly assigned by Visual Studio, so it will be different on your machine.
• It uses LINQ to an Entity to select the data.

Now, our API is ready but before running, let's change the default behavior of our Application for returning XML data to JSON data in WebApiConfig.cs located under App_Start folder 

using Newtonsoft.Json.Serialization;
using System.Web.Http;

namespace Test
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services

            // Web API routes
            config.MapHttpAttributeRoutes();

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

            //By default Web API return XML data
            //We can remove this by clearing the SupportedMediaTypes option as follows
            config.Formatters.XmlFormatter.SupportedMediaTypes.Clear();

            //Now set the serializer setting for JsonFormatter to Indented to get Json Formatted data
            config.Formatters.JsonFormatter.SerializerSettings.Formatting =
                Newtonsoft.Json.Formatting.Indented;

            //For converting data in Camel Case
            config.Formatters.JsonFormatter.SerializerSettings.ContractResolver =
                new CamelCasePropertyNamesContractResolver();
        }
    }
}

Now, our API is ready. Let's run.

The above error happens because we haven't specified the URL, so add the /api/Default in your browser URL as highlighted

Now, our API will return the JSON formatted data with one to many relation.

Every country has some sample states, which have their respective cities.