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.
![WEB API]()
Take an empty Web API project and name it.
![WEB API]()
Click OK and select the empty template with Web API.
![WEB API]()
This will create an empty project with Web API.
Now, start Package Manager Console.
![WEB API]()
In Package Manger Console, type Install-Package EntityFramework.
![WEB API]()
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.
![WEB API]()
Name it as entites.cs.
![WEB API]()
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.
![WEB API]()
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.
![WEB API]()
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.
![WEB API]()
Name it as DefaultController.
![WEB API]()
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.
![WEB API]()
The above error happens because we haven't specified the URL, so add the /api/Default in your browser URL as highlighted
![WEB API]()
Now, our API will return the JSON formatted data with one to many relation.
![WEB API]()
Every country has some sample states, which have their respective cities.