OData without Entity Framework in ASP.NET 6.0 and performing CRUD operations
Tags: odata, dotnet, entityframework, webapi
Published: 2022-03-19 10:57:49

In previous tutorial [Supercharging ASP.NET 6.0 with ODATA | CRUD | Batching | Pagination](https://dev.to/renukapatil/supercharging-aspnet-60-with-odata-crud-batching-pagination-12np) 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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ystaut6wtz3qdbl53uu0.PNG)
You can see previous tutorial [Supercharging ASP.NET 6.0 with ODATA | CRUD | Batching | Pagination](https://dev.to/renukapatil/supercharging-aspnet-60-with-odata-crud-batching-pagination-12np) 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
`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t4aduxjo5xeplz3cvf8d.png)

## Repository
You can check for the source code here.
[OData-without-Entity-framework](https://github.com/renukapatil-21/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!_