In this article, we will learn how to implement paging, sorting, and filtering in a .NET API using Entity Framework Core and how to consume it in a Vue.js 3 UI.
---
title: Entity Framework Core Paging, Sorting, and Filtering in .NET API for Vue.js 3 UI
subtitle: In this article, we will learn how to implement paging, sorting, and filtering in a .NET API using Entity Framework Core and how to consume it in a Vue.js 3 UI.
author: Jon LaBelle
date: October 15, 2024
source: https://jonlabelle.com/snippets/view/markdown/entity-framework-core-paging-sorting-and-filtering-in-net-api-for-vuejs-3-ui
notoc: false
---
## Introduction
Paging, sorting, and filtering are common requirements in web applications. When dealing with large datasets, it is essential to provide users with the ability to navigate through the data efficiently. In this tutorial, we will create a .NET API that uses Entity Framework Core to implement paging, sorting, and filtering functionality. We will then consume this API in a Vue.js 3 UI to display the data.
## Prerequisites
To follow this tutorial, you should have the following prerequisites:
- Basic knowledge of C# and .NET
- Node.js and npm installed on your machine
- Vue.js 3 installed globally on your machine
- Visual Studio or Visual Studio Code for .NET development
## Step 1: Create a .NET API with Entity Framework Core
First, let's create a .NET API project with Entity Framework Core to implement paging, sorting, and filtering functionality.
Open Visual Studio and create a new ASP.NET Core Web API project.
Next, install the required NuGet packages for Entity Framework Core and SQL Server:
```bash
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
```
Create a model class for the data you want to display. For example, let's create a `Product` and `Category` classes:
```csharp
// Category.cs
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Product> Products { get; set; }
}
// Product.cs
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public int CategoryId { get; set; }
public Category Category { get; set; }
}
```
Next, create a `DbContext` class that inherits from `DbContext` and includes a `DbSet` property for the `Product` class:
```csharp
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
{ }
public DbSet<Product> Products { get; set; }
public DbSet<Category> Categories { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.HasOne(p => p.Category)
.WithMany(c => c.Products)
.HasForeignKey(p => p.CategoryId);
}
}
```
Add a connection string to your `appsettings.json` file:
```json
{
"ConnectionStrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=ProductsDb;Trusted_Connection=True;"
}
}
```
Register the `DbContext` in the `Startup.cs` file:
```csharp
services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
```
Create sorting, filtering, and paging services:
```csharp
// PagedResult.cs
public class PagedResult<T>
{
public IEnumerable<T> Items { get; set; }
public int TotalCount { get; set; }
}
// PaginationSortingFilteringService.cs
public class PaginationSortingFilteringService
{
public async Task<PaginationResult<T>> GetPagedResultAsync<T>(
IQueryable<T> query,
int page,
int pageSize,
string[] sortBy,
string[] sortOrder,
string filter = "",
string filterBy = "Name")
{
if (!string.IsNullOrEmpty(filter))
{
// Assuming T has a Name property for filtering. Adjust as needed.
query = query.Where(p => EF.Property<string>(p, filterBy).Contains(filter));
}
for (int i = 0; i < sortBy.Length; i++)
{
var sortProperty = sortBy[i];
var sortDirection = sortOrder.Length > i ? sortOrder[i] : "asc";
query = sortDirection == "asc" ? query.OrderByProperty(sortProperty) : query.OrderByPropertyDescending(sortProperty);
}
var totalCount = await query.CountAsync();
var items = await query.Skip((page - 1) * pageSize).Take(pageSize)
.ToListAsync();
return new PaginationResult<T>
{
Items = items,
TotalCount = totalCount
};
}
}
// ProductsController.cs
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly AppDbContext _context;
private readonly PaginationSortingFilteringService _paginationService;
public ProductsController(AppDbContext context, PaginationSortingFilteringService paginationService)
{
_context = context;
_paginationService = paginationService;
}
[HttpGet]
public async Task<ActionResult<PaginationResult<Product>>> GetProducts(
int page = 1,
int pageSize = 10,
string[] sortBy = null,
string[] sortOrder = null,
string filter = "",
string filterBy = "Name")
{
sortBy ??= new[] { "Id" };
sortOrder ??= new[] { "asc" };
var query = _context.Products.Include(p => p.Category).AsQueryable();
var result = await _paginationService.GetPagedResultAsync(query, page, pageSize, sortBy, sortOrder, filter, filterBy);
return Ok(result);
}
}
```
## Step 2: Consume the API in a Vue.js 3 UI
In this step, we will create a Vue.js 3 UI to consume the updated API with pagination, sorting, and filtering capabilities.
### 1. Set up the Vue.js project
First, create a new Vue.js 3 project using Vue CLI:
```sh
vue create my-project
cd my-project
```
### 2. Install Axios
We will use Axios to make HTTP requests to the API:
```sh
npm install axios
```
### 3. Create a ProductService
Create a `ProductService.js` file in the `src` directory to handle API requests:
```javascript
import axios from 'axios';
const API_URL = 'http://localhost:5000/api/products';
export default {
getProducts(
page = 1,
pageSize = 10,
sortBy = ['Id'],
sortOrder = ['asc'],
filter = '',
filterBy = 'Name'
) {
return axios.get(API_URL, {
params: {
page,
pageSize,
sortBy,
sortOrder,
filter,
filterBy
}
});
}
};
```
### 4. Create a ProductList component
Create a `ProductList.vue` component in the `src/components` directory to display the products:
```vue
<template>
<div>
<h1>Product List</h1>
<div>
<label for="filter">Filter by Name:</label>
<input v-model="filter" @input="fetchProducts" id="filter" />
</div>
<table>
<thead>
<tr>
<th @click="changeSort('Id')">ID</th>
<th @click="changeSort('Name')">Name</th>
<th @click="changeSort('Price')">Price</th>
<th>Category</th>
</tr>
</thead>
<tbody>
<tr v-for="product in products" :key="product.id">
<td>{{ product.id }}</td>
<td>{{ product.name }}</td>
<td>{{ product.price }}</td>
<td>{{ product.category.name }}</td>
</tr>
</tbody>
</table>
<div>
<button @click="prevPage" :disabled="page === 1">Previous</button>
<button @click="nextPage">Next</button>
</div>
</div>
</template>
<script>
import ProductService from '../ProductService';
export default {
data() {
return {
products: [],
page: 1,
pageSize: 10,
sortBy: ['Id'],
sortOrder: ['asc'],
filter: '',
filterBy: 'Name'
};
},
methods: {
fetchProducts() {
ProductService.getProducts(
this.page,
this.pageSize,
this.sortBy,
this.sortOrder,
this.filter,
this.filterBy
)
.then((response) => {
this.products = response.data.items;
})
.catch((error) => {
console.error(error);
});
},
changeSort(property) {
const index = this.sortBy.indexOf(property);
if (index !== -1) {
this.sortOrder[index] =
this.sortOrder[index] === 'asc' ? 'desc' : 'asc';
} else {
this.sortBy = [property];
this.sortOrder = ['asc'];
}
this.fetchProducts();
},
prevPage() {
if (this.page > 1) {
this.page--;
this.fetchProducts();
}
},
nextPage() {
this.page++;
this.fetchProducts();
}
},
mounted() {
this.fetchProducts();
}
};
</script>
<style scoped>
table {
width: 100%;
border-collapse: collapse;
}
th,
td {
border: 1px solid #ddd;
padding: 8px;
}
th {
cursor: pointer;
}
</style>
```
### 5. Use the ProductList component
Update the `App.vue` file to use the `ProductList` component:
```vue
<template>
<div id="app">
<ProductList />
</div>
</template>
<script>
import ProductList from './components/ProductList.vue';
export default {
name: 'App',
components: {
ProductList
}
};
</script>
<style>
#app {
font-family: Avenir, Helvetica, Arial, sans-serif;
-webkit-font-smoothing: antialiased;
-moz-osx-font-smoothing: grayscale;
text-align: center;
color: #2c3e50;
margin-top: 60px;
}
</style>
```
## Conclusion
In this tutorial, we learned how to implement paging, sorting, and filtering in a .NET API using Entity Framework Core and how to consume it in a Vue.js 3 UI. By following these steps, you can create a web application that efficiently handles large datasets and provides users with a seamless experience.