LibraryAPI

A Library Management REST API built using ASP.NET Core Web API, Entity Framework Core, and SQL Server.

The API manages books, members, borrowing and returning of books, and provides reports such as the top 5 most borrowed books and overdue books.

Technologies Used

• ASP.NET Core Web API
• .NET 8
• Entity Framework Core 8
• SQL Server
• LINQ
• Swagger / OpenAPI
• REST API
• Code First approach

Features

• Add new books
• Get all books with availability status
• Add library members
• Borrow books
• Return books
• Check book availability before borrowing
• Get top 5 most borrowed books
• Get overdue books with member details
• ISBN uniqueness validation
• Email validation
• Available copies validation
• Proper HTTP status codes
• LINQ queries for data retrieval

Database Design

The application uses three main tables:

Books

Column	Description
BookId	Primary key
Title	Book title
Author	Book author
ISBN	Unique ISBN number
PublishedYear	Year the book was published
AvailableCopies	Number of currently available copies

Members

Column	Description
MemberId	Primary key
Name	Member name
Email	Member email
Phone	Member phone number
JoinDate	Membership registration date

BorrowRecords

Column	Description
BorrowId	Primary key
MemberId	Foreign key to Members
BookId	Foreign key to Books
BorrowDate	Date when book was borrowed
ReturnDate	Date when book was returned
IsReturned	Indicates whether the book has been returned

API Endpoints

Books

Add a Book

POST /api/books

Adds a new book to the library.

Validation includes:

• Title is required
• Author is required
• ISBN is required and unique
• AvailableCopies cannot be negative

Get All Books

GET /api/books

Returns all books along with their availability status.

Example availability:

Available
Not Available

Members

Add a Member

POST /api/members

Adds a new library member.

Validation includes:

• Name is required
• Valid email format
• Email must be unique

Borrowing

Borrow a Book

POST /api/borrow

Before borrowing, the API:

1. Checks whether the member exists.
2. Checks whether the book exists.
3. Checks whether copies are available.
4. Creates a BorrowRecord.
5. Decreases AvailableCopies by 1.

If no copies are available, the API returns a 400 Bad Request.

Returning

Return a Book

POST /api/return

When a book is returned, the API:

1. Finds the borrow record.
2. Checks whether it has already been returned.
3. Sets IsReturned = true.
4. Sets ReturnDate.
5. Increases AvailableCopies by 1.

Reports

Top 5 Most Borrowed Books

GET /api/reports/top-borrowed

Returns the five books with the highest number of borrowing transactions.

LINQ operations used:

GroupBy()
Count()
OrderByDescending()
Take(5)

Overdue Books

GET /api/reports/overdue

Returns books that:

• Have not been returned.
• Have exceeded the 14-day borrowing period.

The due date is calculated as:

BorrowDate + 14 days

The response includes book and member information.

SQL Queries

The project also includes SQL queries for:

• Top 5 most borrowed books
• Members who borrowed more than 3 books in the last month
• Overdue books
• Books currently borrowed by a specific member
• Stored procedure for overdue books

Entity Relationships

Members
   |
   | 1
   |
   | Many
BorrowRecords
   |
   | Many
   |
   | 1
Books

A member can have multiple borrowing records, and a book can appear in multiple borrowing records.

Validation and Error Handling

The API uses appropriate HTTP status codes.

Status Code	Usage
200 OK	Successful GET/operation
201 Created	Resource successfully created
400 Bad Request	Invalid input or unavailable book
404 Not Found	Member, book, or borrow record not found
409 Conflict	Duplicate ISBN or email
500 Internal Server Error	Unexpected server error

Entity Framework Core

The project uses the Code First approach.

Install the required EF Core packages:

Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 8.0.11
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 8.0.11
Install-Package Microsoft.EntityFrameworkCore.Design -Version 8.0.11

Create the initial migration:

Add-Migration InitialCreate

Update the database:

Update-Database

Running the Project

1. Clone the repository.
2. Open the solution in Visual Studio.
3. Configure the SQL Server connection string in appsettings.json.
4. Restore NuGet packages.
5. Run the Entity Framework migration.
6. Start the application.
7. Open Swagger to test the API endpoints.

Swagger

Swagger/OpenAPI can be used to test all API endpoints directly from the browser.

Typical Swagger URL:

https://localhost:<port>/swagger

Project Structure

LibraryAPI
│
├── Controllers
│   ├── BooksController.cs
│   ├── MembersController.cs
│   ├── BorrowController.cs
│   └── ReportsController.cs
│
├── Models
│   ├── Book.cs
│   ├── Member.cs
│   └── BorrowRecord.cs
│
├── Data
│   └── LibraryDbContext.cs
│
├── DTOs
│   ├── CreateBookDto.cs
│   ├── CreateMemberDto.cs
│   └── BorrowBookDto.cs
│
├── Migrations
│
├── appsettings.json
└── Program.cs

Key Business Rules

• ISBN must be unique.
• Member email must be unique.
• AvailableCopies cannot be negative.
• A member must exist before borrowing a book.
• A book must exist before borrowing.
• A book cannot be borrowed when AvailableCopies is 0.
• Returning an already returned book is not allowed.
• A book is overdue after 14 days if it has not been returned.

Author

Sumedha Tongle

GitHub Repository: LibraryAPI