OData without Entity Framework in ASP.NET 6.0 and performing CRUD operations

In previous tutorial Supercharging ASP.NET 6.0 with ODATA | CRUD | Batching | Pagination we have seen OData with Entity Framework.
In this tutorial explains how we can expose odata endpoints without Entity Framework and performing CRUD operations.

OData is an data source or endpoint that allows a set of data access mechanism to get data from it where the underlying concept to bring data upto endpoint is totally abstract to the end user.
There is not a strong relationship with the Entity Framework and Odata.

Preparation

1. Install .NET 6 SDK: (https://dotnet.microsoft.com/en-us/download/dotnet/6.0)
2. Install SQL and Setup:(https://www.microsoft.com/en-in/sql-server/sql-server-downloads)
3. Install Postman for testing the API:(https://www.postman.com/downloads/)

Create Project

1. Use this command: dotnet new webapi -o ODataWithoutEf
2. Create Solution: dotnet new sln
3. Connect solution with project: dotnet sln add ODataStudent

Add Dependencies

1. Install OData: Install-Package Microsoft.AspNetCore.OData -Version 8.0.4

Connect Project to Database

Create a database with following schema:
Database name: ODataStudent
Table name: Student
column names:

You can see previous tutorial Supercharging ASP.NET 6.0 with ODATA | CRUD | Batching | Pagination here we created database, same database is used in this tutorial.

Create Models folder. In the same folder create Student.cs file.

namespace ODataWithoutEF.Models
{
    public class Student
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
}

In Models folder create ContextDb.cs file. This will work as a repository and it supply the data through controller.

using System.Data;
using System.Data.SqlClient;

namespace ODataWithoutEF.Models
{
    public class ContextDb
    {  
        string conn = "Data Source=(localdb)\\mssqllocaldb; Database=ODataStudent;Trusted_Connection=True;MultipleActiveResultSets=True";
        public List<Student> GetStudent()
        {
            List<Student> list = new List<Student>();
            string query = "Select * from Students";
            using (SqlConnection con = new SqlConnection(conn))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    foreach (DataRow dr in dt.Rows)
                    {
                        list.Add(new Student { Id=Convert.ToInt32(dr[0]), FirstName= Convert.ToString(dr[1]), LastName = Convert.ToString(dr[2]) });
                    }
                }
            }
            return list;
        }

        public bool Add(Student obj)
        {
            string query = "insert into Students values('" + obj.FirstName + "','" + obj.LastName + "')";
            using (SqlConnection con = new SqlConnection(conn))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    int i = cmd.ExecuteNonQuery();
                    if (i >= 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
        }

        public bool Edit(int id, Student obj)
        {
            string query = "update Students set FirstName= '" + obj.FirstName + "', LastName='" + obj.LastName + "' where Id='" + id + "' ";
            using (SqlConnection con = new SqlConnection(conn))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    int i = cmd.ExecuteNonQuery();
                    if (i >= 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
        }

        public bool DeleteStudent(int id)
        {
            string query = "delete Students where Id='" + id + "'";
            using (SqlConnection con = new SqlConnection(conn))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    int i = cmd.ExecuteNonQuery();
                    if (i >= 1)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                }
            }
        }
    }
}

Create API Controller

Create StudentsController.cs file in Controllers folder.

using Microsoft.AspNet.OData.Routing;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Routing.Controllers;
using ODataWithoutEF.Models;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace ODataWithoutEF.Controllers
{ 
    public class StudentsController : ODataController
    {
        ContextDb db = new ContextDb();

        [EnableQuery]

        public IEnumerable<Student> Get()
        {
            return db.GetStudent().ToList();
        }

        [HttpGet(nameof(GetById))]
        public IEnumerable<Student> GetById(int Id)
        {
            var result = db.GetStudent().Where(model => model.Id == Id);

            return result;
        }

        public void Post([FromBody] Student obj)
        {
            if (ModelState.IsValid == true)
            {
                db.Add(obj);
            }
            else
            {

            }
        }


        [HttpPut("{id}")]
        public void Put(int id, [FromBody] Student obj)
        {
            if (ModelState.IsValid == true)
            {
                db.Edit(id, obj);
            }
        }


        [HttpDelete("{id}")]
        public void Delete(int id)
        {
            if (ModelState.IsValid == true)
            {
                db.DeleteStudent(id);
            }
        }

    }
}

• Program.cs file: In GetEdmModel() function we are registering the model. Just create one object ODataConventionModelBuilder class and register it.

using Microsoft.AspNetCore.OData;
using Microsoft.OData.Edm;
using Microsoft.OData.ModelBuilder;
using ODataWithoutEF.Models;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
static IEdmModel GetEdmModel()
{
    ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
    builder.EntitySet<Student>("Students");
    return builder.GetEdmModel();
}

builder.Services.AddControllers().AddOData(opt => opt.AddRouteComponents("odata",GetEdmModel()).Select().Filter().Count().SetMaxTop(25));

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}
app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Testing API on Postman

Get student by id and firstname: https://localhost:7275/odata/Emps?$Select=Id,FirstName


Repository

You can check for the source code here.
OData-without-Entity-framework

Keep Learning!
Thank You
Hope this helps, feel free to share your ideas or comment on this article and let me know your thoughts or if you have any questions!

Comments

[Rockgecko]

What is db.GetEmp().ToList();? And wouldn't calling ToList() enumerate every record in the db?

[Renuka Patil]

Hey @Rockgecko thanks for reading the blog, I hope this is helping you.
Coming to the first question instead of (db.GetEmp().ToList();) it would be (db.GetStudent().ToList();) that was by mistaken, you can check runnable code on github. And using the ToList() method return the list instance with appropriate elements, yes ToList() will enumerate every record in database as in Get action we retrieve all the data in list.