I always tell people that EF Core is one of those tools that looks very simple in the beginning. You do a few queries, call ToList, and everything feels fine. Then the business side comes in and suddenly you are dealing with real user tables, multi column sorting, dynamic filters coming from URLs, and lists that contain thousands or even millions of rows.
This is when things get interesting. And to be honest, this is also when I started appreciating EF Core more, especially when I learned how to use ordering, filtering, and paging properly.
In this post, I want to walk you through the lessons I learned from actual projects. Nothing too fancy. Just practical things that helped me build real systems. I will also show small sample snippets so we can discuss how simple examples evolve when business requirements start piling up.
Grab a coffee. This is a long one.
Why these three things matter so much in real projects
When you work on small demos or school projects, you do not really worry about performance or how the front end consumes the data. You just query everything. But in real life, especially in business systems, you cannot do that.
In one of my projects, we had a system that displayed a list of consignment items. The table grew so fast that retrieving the whole list became painful. The front end would freeze and the API would take seconds to respond. The fix was not to optimize SQL directly. The real fix was proper ordering, filtering, and paging.
From that point on, any time I design a list API, these three things become non negotiable for me. They save servers, save users, and save stress.
Getting started with ordering
Ordering sounds simple. Order by a property. Done. But there is more to it when you handle user driven sorting.
Here is a simple version
var products = await _context.Products
.OrderBy(p => p.Name)
.ToListAsync();Nothing surprising. But in real systems, users choose how they want to sort. Maybe by price or by created date. Maybe ascending or descending.
I remember one project where merchants wanted to sort orders by the customer’s last name. In another project, the finance department wanted to sort by transaction amount but required a default fallback when amounts were the same.
This is where dynamic expressions help.
IQueryable<Product> query = _context.Products;
if (sortField == "name")
{
query = sortDirection == "asc"
? query.OrderBy(p => p.Name)
: query.OrderByDescending(p => p.Name);
}
else if (sortField == "price")
{
query = sortDirection == "asc"
? query.OrderBy(p => p.Price)
: query.OrderByDescending(p => p.Price);
}
else
{
query = query.OrderBy(p => p.Id);
}
var products = await query.ToListAsync();In my actual project, this became a helper method because almost every module reused it. Once you experience one big table without sorting, you will never forget to include ordering again.
Filtering, the part that always grows and grows
Filtering starts very innocent. You filter by name. Maybe status. But after a few months, the business will ask for more. Then the front end will ask to support search boxes, date ranges, and advanced filters.
This is one of the first lessons I learned. Always design your filters in a way that allows them to grow.
Here is a simple starting point
var query = _context.Products.AsQueryable();
if (!string.IsNullOrWhiteSpace(search))
{
query = query.Where(p => p.Name.Contains(search));
}
var results = await query.ToListAsync();Pretty basic. But imagine this happens
One day I was working on a merchant onboarding module. The merchant operations team asked me to add filters for approval status. Then a week later, they asked to add a date range for the applied date. Another week later, they wanted to filter by the agent who submitted the merchant.
If you do not prepare the query to be flexible, your code becomes messy.
What helped me a lot was simply building filtering step by step using an IQueryable chain
IQueryable<Merchant> query = _context.Merchants;
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(m => m.Name.Contains(keyword)
|| m.BusinessName.Contains(keyword));
}
if (!string.IsNullOrWhiteSpace(status))
{
query = query.Where(m => m.Status == status);
}
if (startDate != null)
{
query = query.Where(m => m.CreatedAt >= startDate.Value);
}
if (endDate != null)
{
query = query.Where(m => m.CreatedAt <= endDate.Value);
}
if (!string.IsNullOrWhiteSpace(agent))
{
query = query.Where(m => m.Agent == agent);
}
var merchants = await query.ToListAsync();Now imagine this grows again. Eventually, I ended up creating filter objects, then passing them into service methods like
public async Task<List<Merchant>> SearchAsync(MerchantFilter filter)This kept our code clean as requirements evolved.
Paging, the hero that prevents your application from dying
Let me tell you a quick story. In one project, a customer had twenty thousand plus inventory records. They asked us why the screen would not load. When I checked the API logs, I saw that the API returned all twenty thousand rows.
This is when I realized how important paging is.
With EF Core, paging is simply Skip and Take.
var pageNumber = 1;
var pageSize = 20;
var items = await _context.Products
.OrderBy(p => p.Id)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToListAsync();Simple. But this single concept saved us from performance issues many times.
In another project, I made the mistake of not including a default ordering before calling Skip and Take. The result was inconsistent ordering between page loads. Lesson learned. Always include a stable ordering before paging.
Here is the pattern I now use in every API
var query = _context.Products.AsQueryable();
// filtering here
// ordering here
var totalCount = await query.CountAsync();
var items = await query
.Skip((page - 1) * pageSize)
.Take(pageSize)
.ToListAsync();
return new PagedResult<Product>
{
Items = items,
TotalCount = totalCount,
Page = page,
PageSize = pageSize
};Receiving the total count is important because the front end usually wants to show the total number of pages. I learned this the hard way when our front end team kept guessing the pagination UI because they did not know how many pages existed.
Handling dynamic filters without writing giant if else blocks
At some point your filtering logic will get big. My own filtering code reached more than twenty conditions at one point. It felt like reading a grocery list.
This is where I started using expression builders or small reusable extension methods. It does not need to be fancy. Even small things help reduce clutter.
Something like this
public static IQueryable<Product> WhereStatus(
this IQueryable<Product> query,
string status)
{
if (string.IsNullOrWhiteSpace(status))
return query;
return query.Where(p => p.Status == status);
}Then in the service
var query = _context.Products
.WhereStatus(filter.Status)
.WhereCategory(filter.Category)
.WhereNameContains(filter.Keyword);This made the code readable and very easy to extend.
Real project example, consignment inventory system
Let me share a real example from a consignment inventory project I worked on.
We had a module where store managers viewed inventory that was delivered, sold, or returned. They needed to filter by product name, category, date range, store branch, and consignment status. They also needed to sort by product name or quantity.
Our database had more than one hundred thousand rows. Without paging, the module would freeze.
This was the structure we ended up using
IQueryable<Consignment> query = _context.Consignments
.Include(c => c.Product)
.Include(c => c.Store);
if (!string.IsNullOrWhiteSpace(filter.Keyword))
{
query = query.Where(c =>
c.Product.Name.Contains(filter.Keyword));
}
if (!string.IsNullOrWhiteSpace(filter.Store))
{
query = query.Where(c => c.Store.Code == filter.Store);
}
if (filter.StartDate != null)
{
query = query.Where(c => c.Date >= filter.StartDate.Value);
}
if (filter.EndDate != null)
{
query = query.Where(c => c.Date <= filter.EndDate.Value);
}
// ordering
query = filter.SortBy == "name"
? query.OrderBy(c => c.Product.Name)
: query.OrderByDescending(c => c.Date);
// paging
var total = await query.CountAsync();
var items = await query
.Skip((filter.Page - 1) * filter.PageSize)
.Take(filter.PageSize)
.ToListAsync();
return new PagedResult<Consignment>
{
Items = items,
TotalCount = total
};This solved the performance issue immediately. Store managers were happy because lists loaded in less than a second even with large data.
Selecting only what you need, or how projection saved our API
Another lesson I wish I learned earlier is projection. Sometimes you do not need the whole entity. You only need a few fields.
In one real case, we had a list of transactions. Each transaction had navigation properties that opened even more sub relations. Loading all of them caused slow responses.
This is where Select becomes very powerful.
var transactions = await _context.Transactions
.Where(t => t.Status == "Approved")
.Select(t => new TransactionListItem
{
Id = t.Id,
Amount = t.Amount,
Customer = t.CustomerName,
CreatedAt = t.CreatedAt
})
.ToListAsync();This small change reduced query time from almost two seconds to less than two hundred milliseconds in one of our APIs. Projection plus ordering plus filtering plus paging basically became my default pattern.
Joining other tables with filtering and ordering
In real life, you will not always query from a single table. You will join tables or include related data.
One project had a reporting module that required joining customers, orders, and payments. The filtering logic needed to consider customer name and payment status. The sorting needed to consider order amount.
With EF Core, a join usually looks like this
var report = await _context.Orders
.Join(_context.Customers,
order => order.CustomerId,
customer => customer.Id,
(order, customer) => new { order, customer })
.Where(x => x.customer.Name.Contains(keyword))
.OrderBy(x => x.order.Amount)
.Select(x => new OrderReportItem
{
OrderId = x.order.Id,
Customer = x.customer.Name,
Amount = x.order.Amount,
Date = x.order.CreatedAt
})
.ToListAsync();I used this pattern a lot in reporting modules where Include was not enough or when projection required merged fields.
When expression trees start making sense
There was a time when I saw dynamic LINQ or expression trees and ignored them. They looked too complicated. But in one project, the customer needed a very flexible filtering system that allowed them to combine conditions dynamically.
This is when I realized expression trees allow you to build dynamic where clauses. Even if you do not need them today, eventually a requirement will push you toward them.
Here is a very simplified example
var parameter = Expression.Parameter(typeof(Product));
var left = Expression.Property(parameter, "Name");
var right = Expression.Constant(keyword);
var contains = Expression.Call(
left,
"Contains",
null,
right
);
var lambda = Expression.Lambda<Func<Product, bool>>(contains, parameter);
var query = _context.Products.Where(lambda);In real projects, this gave us the flexibility to generate queries based on user selected conditions. I would not say use this every day, but it is a good tool to keep in mind.
Putting everything together into a reusable pattern
After a few years of building projects, I noticed that ninety percent of list APIs follow the same pattern.
- Filtering
- Ordering
- Paging
- Projection
So I eventually created a reusable pattern. Something like this
public async Task<PagedResult<T>> ApplyAsync<T>(
IQueryable<T> query,
int page,
int size)
{
var total = await query.CountAsync();
var items = await query
.Skip((page - 1) * size)
.Take(size)
.ToListAsync();
return new PagedResult<T>
{
Items = items,
TotalCount = total,
Page = page,
PageSize = size
};
}This allowed me to keep controllers clean. In your project, you can expand this to support dynamic sorting rules or mapping layers.
Final thoughts
If there is one thing I learned from all these experiences, it is this Take ordering, filtering, and paging seriously. They look basic at first but they become essential once your data grows and your requirements mature.
These three things helped me build APIs that are stable, predictable, and fast. They also reduced stress for the front end team because they received consistent data. And from a performance perspective, these patterns helped us avoid unnecessary database load.
If you are new to EF Core or still building your first real world project, I hope this post gives you a more practical view of how these features are used in the wild. And if you are already experienced, maybe some of these stories made you remember situations you also faced.
Thanks for reading. Hope you learned something from my personal journey.
