THE POWER OF POSITIVE THINKING: LINQ IN REST API

Below is a step-by-step guide to creating an ASP.NET Core Web API using the Code-First Approach with Entity Framework Core. The project is named BlogApp, and it includes two models: UserPost and Category.

Step 1: Create the ASP.NET Core Web API Project

Open Visual Studio or the terminal.
Create a new ASP.NET Core Web API project:
```
dotnet new webapi -n BlogApp
cd BlogApp
```

Step 2: Install Required Packages

Install the following NuGet packages:

Entity Framework Core: For database operations.
Entity Framework Core Tools: For migrations.
SQL Server Provider: For SQL Server database (or use another provider like SQLite).

Run these commands:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools

Step 3: Define the Models

Create two models: UserPost and Category.

UserPost.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BlogApp.Models
{
    public class UserPost
    {
        [Key]
        public int PostId { get; set; }

        [Required]
        [StringLength(200)]
        public string Title { get; set; }

        [Required]
        public string Content { get; set; }

        public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

        // Foreign Key for Category
        public int CategoryId { get; set; }

        // Navigation Property
        [ForeignKey("CategoryId")]
        public Category Category { get; set; }
    }
}

Category.cs

using System.Collections.Generic;

namespace BlogApp.Models
{
    public class Category
    {
        public int CategoryId { get; set; }

        [Required]
        [StringLength(100)]
        public string Name { get; set; }

        // Navigation Property
        public ICollection<UserPost> UserPosts { get; set; }
    }
}

Step 4: Create the Database Context

Create a DbContext class to manage the database connection and entity sets.

BlogAppDbContext.cs

using Microsoft.EntityFrameworkCore;

namespace BlogApp.Models
{
    public class BlogAppDbContext : DbContext
    {
        public BlogAppDbContext(DbContextOptions<BlogAppDbContext> options) : base(options)
        {
        }

        // DbSet for each model
        public DbSet<UserPost> UserPosts { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure relationships
            modelBuilder.Entity<UserPost>()
                .HasOne(u => u.Category)
                .WithMany(c => c.UserPosts)
                .HasForeignKey(u => u.CategoryId);

            base.OnModelCreating(modelBuilder);
        }
    }
}

Step 5: Configure the Database Connection

Add the database connection string to appsettings.json and configure the DbContext in Program.cs.

appsettings.json

{
  "ConnectionStrings": {
    "BlogAppConnection": "Server=(localdb)\\mssqllocaldb;Database=BlogAppDb;Trusted_Connection=True;"
  }
}

Program.cs

using BlogApp.Models;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add DbContext with SQL Server
builder.Services.AddDbContext<BlogAppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("BlogAppConnection")));

// Add controllers
builder.Services.AddControllers();

var app = builder.Build();

// Middleware
app.UseRouting();
app.UseAuthorization();
app.MapControllers();

app.Run();

Step 6: Create and Apply Migrations

Create a migration:
```
dotnet ef migrations add InitialCreate
```
Apply the migration to create the database:
```
dotnet ef database update
```

Step 7: Create Controllers

Create API controllers for UserPost and Category.

UserPostsController.cs

using BlogApp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace BlogApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class UserPostsController : ControllerBase
    {
        private readonly BlogAppDbContext _context;

        public UserPostsController(BlogAppDbContext context)
        {
            _context = context;
        }

        // GET: api/UserPosts
        [HttpGet]
        public async Task<ActionResult<IEnumerable<UserPost>>> GetUserPosts()
        {
            return await _context.UserPosts.Include(p => p.Category).ToListAsync();
        }

        // GET: api/UserPosts/5
        [HttpGet("{id}")]
        public async Task<ActionResult<UserPost>> GetUserPost(int id)
        {
            var userPost = await _context.UserPosts.Include(p => p.Category)
                .FirstOrDefaultAsync(p => p.PostId == id);

            if (userPost == null)
            {
                return NotFound();
            }

            return userPost;
        }

        // POST: api/UserPosts
        [HttpPost]
        public async Task<ActionResult<UserPost>> PostUserPost(UserPost userPost)
        {
            _context.UserPosts.Add(userPost);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetUserPost", new { id = userPost.PostId }, userPost);
        }

        // DELETE: api/UserPosts/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteUserPost(int id)
        {
            var userPost = await _context.UserPosts.FindAsync(id);
            if (userPost == null)
            {
                return NotFound();
            }

            _context.UserPosts.Remove(userPost);
            await _context.SaveChangesAsync();

            return NoContent();
        }
    }
}

CategoriesController.cs

using BlogApp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace BlogApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CategoriesController : ControllerBase
    {
        private readonly BlogAppDbContext _context;

        public CategoriesController(BlogAppDbContext context)
        {
            _context = context;
        }

        // GET: api/Categories
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Category>>> GetCategories()
        {
            return await _context.Categories.ToListAsync();
        }

        // POST: api/Categories
        [HttpPost]
        public async Task<ActionResult<Category>> PostCategory(Category category)
        {
            _context.Categories.Add(category);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetCategory", new { id = category.CategoryId }, category);
        }
    }
}

Step 8: Test the API

Run the application:
```
dotnet run
```
Use tools like Postman or Swagger to test the endpoints:
- GET /api/UserPosts
- POST /api/UserPosts
- GET /api/Categories
- POST /api/Categories

Final Project Structure

BlogApp/
├── Controllers/
│   ├── UserPostsController.cs
│   └── CategoriesController.cs
├── Models/
│   ├── UserPost.cs
│   └── Category.cs
├── Data/
│   └── BlogAppDbContext.cs
├── appsettings.json
├── Program.cs
└── BlogApp.csproj

This step-by-step guide provides a complete implementation of a Code-First Approach in ASP.NET Core Web API using Entity Framework Core.

Create ViewModels for UserPost and Category.
Modify the Controllers to use ViewModels.
Use LINQ to query the database and return data in the desired format.

Step 1: Create ViewModels

ViewModels are used to shape the data returned by the API. They help decouple the database entities from the API response.

UserPostViewModel.cs

namespace BlogApp.ViewModels
{
    public class UserPostViewModel
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public DateTime CreatedAt { get; set; }
        public string CategoryName { get; set; } // Include Category Name
    }
}

CategoryViewModel.cs

namespace BlogApp.ViewModels
{
    public class CategoryViewModel
    {
        public int CategoryId { get; set; }
        public string Name { get; set; }
    }
}

Step 2: Modify Controllers to Use ViewModels

We'll update the UserPostsController and CategoriesController to use ViewModels and LINQ queries.

UserPostsController.cs

using BlogApp.Models;
using BlogApp.ViewModels;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace BlogApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class UserPostsController : ControllerBase
    {
        private readonly BlogAppDbContext _context;

        public UserPostsController(BlogAppDbContext context)
        {
            _context = context;
        }

        // GET: api/UserPosts
        [HttpGet]
        public async Task<ActionResult<IEnumerable<UserPostViewModel>>> GetUserPosts()
        {
            var userPosts = await _context.UserPosts
                .Include(p => p.Category)
                .Select(p => new UserPostViewModel
                {
                    PostId = p.PostId,
                    Title = p.Title,
                    Content = p.Content,
                    CreatedAt = p.CreatedAt,
                    CategoryName = p.Category.Name
                })
                .ToListAsync();

            return userPosts;
        }

        // GET: api/UserPosts/5
        [HttpGet("{id}")]
        public async Task<ActionResult<UserPostViewModel>> GetUserPost(int id)
        {
            var userPost = await _context.UserPosts
                .Include(p => p.Category)
                .Where(p => p.PostId == id)
                .Select(p => new UserPostViewModel
                {
                    PostId = p.PostId,
                    Title = p.Title,
                    Content = p.Content,
                    CreatedAt = p.CreatedAt,
                    CategoryName = p.Category.Name
                })
                .FirstOrDefaultAsync();

            if (userPost == null)
            {
                return NotFound();
            }

            return userPost;
        }

        // POST: api/UserPosts
        [HttpPost]
        public async Task<ActionResult<UserPost>> PostUserPost(UserPost userPost)
        {
            _context.UserPosts.Add(userPost);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetUserPost", new { id = userPost.PostId }, userPost);
        }

        // DELETE: api/UserPosts/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteUserPost(int id)
        {
            var userPost = await _context.UserPosts.FindAsync(id);
            if (userPost == null)
            {
                return NotFound();
            }

            _context.UserPosts.Remove(userPost);
            await _context.SaveChangesAsync();

            return NoContent();
        }
    }
}

CategoriesController.cs

using BlogApp.Models;
using BlogApp.ViewModels;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace BlogApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CategoriesController : ControllerBase
    {
        private readonly BlogAppDbContext _context;

        public CategoriesController(BlogAppDbContext context)
        {
            _context = context;
        }

        // GET: api/Categories
        [HttpGet]
        public async Task<ActionResult<IEnumerable<CategoryViewModel>>> GetCategories()
        {
            var categories = await _context.Categories
                .Select(c => new CategoryViewModel
                {
                    CategoryId = c.CategoryId,
                    Name = c.Name
                })
                .ToListAsync();

            return categories;
        }

        // POST: api/Categories
        [HttpPost]
        public async Task<ActionResult<Category>> PostCategory(Category category)
        {
            _context.Categories.Add(category);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetCategory", new { id = category.CategoryId }, category);
        }
    }
}

Step 3: Test the API

Run the application:
```
dotnet run
```
Use tools like Postman or Swagger to test the endpoints:
- GET /api/UserPosts
- GET /api/UserPosts/1
- POST /api/UserPosts
- GET /api/Categories
- POST /api/Categories

Example API Responses

GET /api/UserPosts

[
    {
        "postId": 1,
        "title": "First Post",
        "content": "This is the content of the first post.",
        "createdAt": "2023-10-01T12:34:56Z",
        "categoryName": "Technology"
    },
    {
        "postId": 2,
        "title": "Second Post",
        "content": "This is the content of the second post.",
        "createdAt": "2023-10-02T14:20:10Z",
        "categoryName": "Lifestyle"
    }
]

GET /api/Categories

[
    {
        "categoryId": 1,
        "name": "Technology"
    },
    {
        "categoryId": 2,
        "name": "Lifestyle"
    }
]

Step 4: Add Advanced LINQ Queries (Optional)

You can add more advanced LINQ queries to filter, sort, or paginate the data.

Example: Filter Posts by Category

[HttpGet("category/{categoryId}")]
public async Task<ActionResult<IEnumerable<UserPostViewModel>>> GetUserPostsByCategory(int categoryId)
{
    var userPosts = await _context.UserPosts
        .Include(p => p.Category)
        .Where(p => p.CategoryId == categoryId)
        .Select(p => new UserPostViewModel
        {
            PostId = p.PostId,
            Title = p.Title,
            Content = p.Content,
            CreatedAt = p.CreatedAt,
            CategoryName = p.Category.Name
        })
        .ToListAsync();

    return userPosts;
}

Example: Paginate Posts

[HttpGet("paged")]
public async Task<ActionResult<IEnumerable<UserPostViewModel>>> GetUserPostsPaged(int page = 1, int pageSize = 10)
{
    var userPosts = await _context.UserPosts
        .Include(p => p.Category)
        .OrderBy(p => p.CreatedAt)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(p => new UserPostViewModel
        {
            PostId = p.PostId,
            Title = p.Title,
            Content = p.Content,
            CreatedAt = p.CreatedAt,
            CategoryName = p.Category.Name
        })
        .ToListAsync();

    return userPosts;
}

Final Notes

ViewModels help decouple the API response from the database schema.
LINQ provides a powerful way to query and shape data.
Use Swagger or Postman to test your API endpoints.

THE POWER OF POSITIVE THINKING

Pages

Saturday, March 22, 2025

LINQ IN REST API

Step 1: Create the ASP.NET Core Web API Project

Step 2: Install Required Packages

Step 3: Define the Models

UserPost.cs

Category.cs

Step 4: Create the Database Context

BlogAppDbContext.cs

Step 5: Configure the Database Connection

appsettings.json

Program.cs

Step 6: Create and Apply Migrations

Step 7: Create Controllers

UserPostsController.cs

CategoriesController.cs

Step 8: Test the API

Final Project Structure

Step 1: Create ViewModels

UserPostViewModel.cs

CategoryViewModel.cs

Step 2: Modify Controllers to Use ViewModels

UserPostsController.cs

CategoriesController.cs

Step 3: Test the API

Example API Responses

GET /api/UserPosts

GET /api/Categories

Step 4: Add Advanced LINQ Queries (Optional)

Example: Filter Posts by Category

Example: Paginate Posts

Final Notes

No comments:

Post a Comment

About Me

Blog Archive