Read Data with Conditions from Database in Entity Framework and ASP.NET Core Web API

Create Database

Create a database named LearnASPNETCoreWebAPIWithRealApps. This database have a table: Product table as below:

USE LearnASPNETMVCWithRealApps

/* Table structure for table `product` */

GO
CREATE TABLE Product (
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(250) NULL,
	Price money NULL,
	Quantity int NULL,
	Status bit NOT NULL
)

/* Dumping data for table `product` */
GO
INSERT Product(Name, Price, Quantity, Status) VALUES('Name 1', 20.0000, 4, 1)
GO
INSERT Product(Name, Price, Quantity, Status) VALUES('Name 2', 12.0000, 8, 0)
GO
INSERT Product(Name, Price, Quantity, Status) VALUES('Name 3', 4.0000, 3, 1)
GO
INSERT Product(Name, Price, Quantity, Status) VALUES('Name 4', 17.0000, 8, 1)

Structure of Product Table

Data of Product Table

Create ASP.NET Core Web API Project

On the Visual Studio, create new ASP.NET Core Web API Application project

Select Empty Template

Click Ok button to Finish

Add Configurations

Open Startup.cs file and add new configurations as below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;

namespace LearnASPNETCoreWebAPIWithRealApps
{
    public class Startup
    {

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
        }

        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc();
        }
    }
}

Create AppSettings File

Select Project and right click to select Add\New Item Menu

Select Web\ASP.NET in left side. Select App Settings File item and click Add button to Finish

In appsettings.json file and new configurations as below:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=LearnASPNETCoreWebAPIWithRealApps;user id=sa;password=123456"
  }
}

Entity Class

Create new folder named Models. In Models folder, create new entity class as below:

Product Entity

Create new class named Product.cs as below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace LearnASPNETCoreWebAPIWithRealApps.Models
{
    public class Product
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public decimal Price { get; set; }

        public int Quantity { get; set; }

        public bool Status { get; set; }
    }
}

Create Data Context

In Models folder, create new class named DataContext.cs, this file use Entity Framework to interact with the database as below:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace LearnASPNETCoreWebAPIWithRealApps.Models
{
    public class DataContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var builder = new ConfigurationBuilder()
                                    .SetBasePath(Directory.GetCurrentDirectory())
                                    .AddJsonFile("appsettings.json");
            var configuration = builder.Build();
            optionsBuilder.UseSqlServer(configuration["ConnectionStrings:DefaultConnection"]);
        }

        public DbSet<Product> Product { get; set; }

    }
}

Create Controller

Create new folder named Controllers. In this folder, create new controller named ProductController.cs, this file contains Web API use Http Methods as below:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Threading.Tasks;
using LearnASPNETCoreWebAPIWithRealApps.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace LearnASPNETCoreWebAPIWithRealApps.Controllers
{
    [Route("api/product")]
    public class ProductController : Controller
    {
        private DataContext db = new DataContext();

        [Produces("application/json")]
        [HttpGet("find/{id}")]
        public async Task<IActionResult> find(int id)
        {
            try
            {
                var product = db.Product.Find(id);
                return Ok(product);
            }
            catch
            {
                return BadRequest();
            }
        }

        [Produces("application/json")]
        [HttpGet("search/{keyword}")]
        public async Task<IActionResult> search(string keyword)
        {
            try
            {
                var products = db.Product.Where(p => p.Name.Contains(keyword)).ToList();
                return Ok(products);
            }
            catch
            {
                return BadRequest();
            }
        }

        [Produces("application/json")]
        [HttpGet("between/{min}/{max}")]
        public async Task<IActionResult> between(decimal min, decimal max)
        {
            try
            {
                var products = db.Product.Where(p => p.Price >= min && p.Price <= max).ToList();
                return Ok(products);
            }
            catch
            {
                return BadRequest();
            }
        }

    }
}

Structure of ASP.NET Core Web API Project

Testing Web API

Access Web API use the following url: http://localhost:18942/api/product/find/4

Output

{"id":4,"name":"Name 3","price":4.0000,"quantity":3,"status":true}

Access Web API use the following url: http://localhost:18942/api/product/search/name

Output

[{"id":1,"name":"Name 1","price":20.0000,"quantity":4,"status":true},{"id":2,"name":"Name 2","price":12.0000,"quantity":8,"status":false},{"id":4,"name":"Name 3","price":4.0000,"quantity":3,"status":true},{"id":2014,"name":"Name 4","price":17.0000,"quantity":8,"status":true}]

Access Web API use the following url: http://localhost:18942/api/product/between/2/15

Output

[{"id":2,"name":"Name 2","price":12.0000,"quantity":8,"status":false},{"id":4,"name":"Name 3","price":4.0000,"quantity":3,"status":true}]

Consume Web API from Console Application

Create Console App (.NET Framework) Project in Visual Studio.

Entity Class

Create Models folder in Console Application. In this folder, create new class named Product.cs as below:

Product Entity

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearnASPNETCoreWebAPIWithRealApps_Client.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int Quantity { get; set; }
        public bool Status { get; set; }
    }
}

Create ProductRestClientModel

ProductRestClientModel class contain methods call Web API. Add reference to System.Net.Http.Formatting library from Nuget Packages

using LearnASPNETCoreWebAPIWithRealApps_Client.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;

namespace LearnASPNETCoreWebAPIWithRealApps_Client
{
    public class ProductRestClientModel
    {
        private string BASE_URL = "http://localhost:18942/api/product/";

        public Task<HttpResponseMessage> Find(int id)
        {
            try
            {
                var client = new HttpClient();
                client.BaseAddress = new Uri(BASE_URL);
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                return client.GetAsync("find/" + id);
            }
            catch
            {
                return null;
            }
        }

        public Task<HttpResponseMessage> Search(string keyword)
        {
            try
            {
                var client = new HttpClient();
                client.BaseAddress = new Uri(BASE_URL);
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                return client.GetAsync("search/" + keyword);
            }
            catch
            {
                return null;
            }
        }

        public Task<HttpResponseMessage> Between(decimal min, decimal max)
        {
            try
            {
                var client = new HttpClient();
                client.BaseAddress = new Uri(BASE_URL);
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                return client.GetAsync("between/" + min + "/" + max);
            }
            catch
            {
                return null;
            }
        }


    }
}

Run Console Application

using LearnASPNETCoreWebAPIWithRealApps_Client.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

namespace LearnASPNETCoreWebAPIWithRealApps_Client
{
    class Program
    {
        static void Main(string[] args)
        {
            var productRestClientModel = new ProductRestClientModel();

            Console.WriteLine("Test with Find Method");
            var httpResponseMessage1 = productRestClientModel.Find(4).Result;
            HttpStatusCode httpStatusCode1 = httpResponseMessage1.StatusCode;
            Console.WriteLine("Status Code: " + httpStatusCode1);
            var isSuccessStatusCode1 = httpResponseMessage1.IsSuccessStatusCode;
            Console.WriteLine("IsSuccessStatusCode: " + isSuccessStatusCode1);
            Product product = httpResponseMessage1.Content.ReadAsAsync<Product>().Result;
            Console.WriteLine("Product Info");
            Console.WriteLine("Id: " + product.Id);
            Console.WriteLine("Name: " + product.Name);
            Console.WriteLine("Price: " + product.Price);
            Console.WriteLine("Quantity: " + product.Quantity);
            Console.WriteLine("Status: " + product.Status);

            Console.WriteLine("\nTest with Search Method");
            var httpResponseMessage2 = productRestClientModel.Search("name").Result;
            var httpStatusCode2 = httpResponseMessage2.StatusCode;
            Console.WriteLine("Status Code: " + httpStatusCode2);
            var isSuccessStatusCode2 = httpResponseMessage2.IsSuccessStatusCode;
            Console.WriteLine("IsSuccessStatusCode: " + isSuccessStatusCode2);
            List<Product> searchResult = httpResponseMessage2.Content.ReadAsAsync<List<Product>>().Result;
            Console.WriteLine("Product List");
            foreach (var p in searchResult)
            {
                Console.WriteLine("Id: " + p.Id);
                Console.WriteLine("Name: " + p.Name);
                Console.WriteLine("Price: " + p.Price);
                Console.WriteLine("Quantity: " + p.Quantity);
                Console.WriteLine("Status: " + p.Status);
                Console.WriteLine("=====================");
            }

            Console.WriteLine("\nTest with Between Method");
            var httpResponseMessage3 = productRestClientModel.Between(2, 15).Result;
            var httpStatusCode3 = httpResponseMessage3.StatusCode;
            Console.WriteLine("Status Code: " + httpStatusCode2);
            var isSuccessStatusCode3 = httpResponseMessage3.IsSuccessStatusCode;
            Console.WriteLine("IsSuccessStatusCode: " + isSuccessStatusCode3);
            List<Product> betweenResult = httpResponseMessage3.Content.ReadAsAsync<List<Product>>().Result;
            Console.WriteLine("Product List");
            foreach (var p in betweenResult)
            {
                Console.WriteLine("Id: " + p.Id);
                Console.WriteLine("Name: " + p.Name);
                Console.WriteLine("Price: " + p.Price);
                Console.WriteLine("Quantity: " + p.Quantity);
                Console.WriteLine("Status: " + p.Status);
                Console.WriteLine("=====================");
            }

            Console.ReadLine();
        }
    }
}

Output

Test with Find Method
Status Code: OK
IsSuccessStatusCode: True
Product Info
Id: 4
Name: Name 3
Price: 4.0000
Quantity: 3
Status: True

Test with Search Method
Status Code: OK
IsSuccessStatusCode: True
Product List
Id: 1
Name: Name 1
Price: 20.0000
Quantity: 4
Status: True
=====================
Id: 2
Name: Name 2
Price: 12.0000
Quantity: 8
Status: False
=====================
Id: 4
Name: Name 3
Price: 4.0000
Quantity: 3
Status: True
=====================
Id: 2014
Name: Name 4
Price: 17.0000
Quantity: 8
Status: True
=====================

Test with Between Method
Status Code: OK
IsSuccessStatusCode: True
Product List
Id: 2
Name: Name 2
Price: 12.0000
Quantity: 8
Status: False
=====================
Id: 4
Name: Name 3
Price: 4.0000
Quantity: 3
Status: True
=====================

References

I recommend you refer to the books below to learn more about the knowledge in this article:

• Modern API Design with ASP.NET Core 2: Building Cross-Platform Back-End Systems
• Mastering ASP.NET Web API: Build powerful HTTP services and make the most of the ASP.NET Core Web API platform
• Entity Framework Core in Action
• Pro Entity Framework Core 2 for ASP.NET Core MVC
• .NET Core in Action