When I first started with Entity Framework Core, I thought LINQ was just a convenient way to avoid writing SQL manually. And honestly, that’s how most of us begin, just trying to fetch a few records without touching raw SQL. But as my projects grew, I realized LINQ is more than just “SELECT * FROM Table”. It can become a powerful tool to express real business logic directly in code, without losing readability.
In this post, I want to walk you through my journey with LINQ in EF Core from simple queries, to filtering and joins, and finally to situations where I had to implement actual business logic. I’ll share examples from real projects, little mistakes I made along the way, and how I eventually got comfortable expressing complex requirements using LINQ.
I’m targeting .NET 9 here, but honestly, most of these concepts apply if you’re on older versions of EF Core too.
Starting Simple: Fetching Data
Like many developers, my first EF Core projects started with something like this:
var customers = await _dbContext.Customers.ToListAsync();
foreach (var customer in customers)
{
Console.WriteLine($"{customer.Id} - {customer.Name}");
}This is probably the “Hello World” of EF Core queries. Nothing fancy, just fetch all customers. At the time, I was happy that I didn’t need to write SELCT * FROM Customers.
But of course, reality hit quickly. The business didn’t want all customers. They wanted only “active” customers, or customers with unpaid balances, or customers created in the last 30 days. That’s when filtering came in.
Filtering with Where
I remember one project where the finance team kept asking for reports of overdue invoices. My first version was ugly, I fetched all invoices and filtered them in memory. Big mistake. Performance dropped quickly once the dataset grew.
That’s when I really learned the power of “Where”.
var overdueInvoices = await _dbContext.Invoices
.Where(i => i.DueDate < DateTime.UtcNow && !i.IsPaid)
.ToListAsync();This felt so much cleaner. Instead of fetching everything, I pushed the filtering down to the database. The generated SQL was efficient, and the finance team stopped complaining about slow reports.
Lesson learned: always let the database do the heavy lifting when you can.
Projection with Select
Another small but important moment for me was when I realized: you don’t always need full entities.
For example, in one project, I was sending data to an API. The API only needed customer names and email addresses, but I was loading the entire Customer entity (which included navigation properties, addresses, orders, etc.).
var customerDtos = await _dbContext.Customers
.Select(c => new CustomerDto
{
Name = c.Name,
Email = c.Email
})
.ToListAsync();This made the query lighter and reduced serialization overhead. Honestly, I felt a little stupid for not realizing this earlier. But this was one of those real-world lessons: just because you can load an entire entity doesn’t mean you should.
Joins: Bringing Related Data Together
At some point, every project runs into “we need to see orders with customer details”.
In EF Core, you often don’t need explicit joins because of navigation properties. That’s nice. For example:
var orders = await _dbContext.Orders
.Include(o => o.Customer)
.ToListAsync();This was a game-changer for me. It felt magical, like I didn’t need to worry about SQL JOIN syntax anymore.
But sometimes, navigation properties aren’t enough. I had a case where I needed to join invoices to payments, but the relationships weren’t directly mapped in the entity model. That’s when I had to fall back to Join.
var invoicePayments = await _dbContext.Invoices
.Join(_dbContext.Payments,
invoice => invoice.Id,
payment => payment.InvoiceId,
(invoice, payment) => new
{
InvoiceNumber = invoice.Number,
PaymentAmount = payment.Amount,
PaymentDate = payment.Date
})
.ToListAsync();When I first wrote this, it looked a bit intimidating. The Join syntax in LINQ isn’t something we use every day because EF Core’s navigation properties usually handle it for us. But sometimes the database design or business requirements don’t give you clean entity mappings. In one project, for example, the payments table came from an external service that synced into our database separately, so there was no direct navigation between invoices and payments.
Breaking it down:
- _dbContext.Invoices is our starting point.
- .Join(_dbContext.Payments, …) says we want to combine invoices with payments.
- invoice => invoice.Id is the key from the invoice side.
- payment => payment.InvoiceId is the key from the payment side.
- (invoice, payment) => new { … } defines the shape of the result, in this case, invoice number, payment amount, and payment date.
If you think in SQL, this is basically:
SELECT i.Number, p.Amount, p.Date
FROM Invoices i
INNER JOIN Payments p ON i.Id = p.InvoiceId;Once I understood the pattern – table1.Join(table2, key1, key2, result) – it clicked. It’s a bit more verbose than SQL, but it gives you full control when navigation properties aren’t available or don’t make sense for the query.
Grouping and Aggregates
One project stands out in my memory: a retail application where management kept asking, “What are the top 5 products sold this month”?
Instead of writing a custom SQL query, I did it with LINQ:
var topProducts = await _dbContext.OrderLines
.Where(ol => ol.Order.OrderDate.Month == DateTime.UtcNow.Month)
.GroupBy(ol => ol.ProductId)
.Select(g => new
{
ProductId = g.Key,
TotalQuantity = g.Sum(ol => ol.Quantity)
})
.OrderByDescending(x => x.TotalQuantity)
.Take(5)
.ToListAsync();When I first got this working, I remember feeling proud like I had “leveled up” as a developer. I wasn’t just fetching rows anymore; I was actually solving a business question directly with LINQ.
Real Business Logic in LINQ
As projects grew, the queries got messier. I’ll share one real case:
A client wanted to show “preferred customers.” The rules sounded simple at first:
- Customers with more than 5 orders in the last 6 months
- And a total purchase amount above 1,000
- And they must not have any unpaid invoices
Here’s how I ended up writing it:
var preferredCustomers = await _dbContext.Customers
.Where(c => c.Orders
.Count(o => o.OrderDate >= DateTime.UtcNow.AddMonths(-6)) > 5)
.Where(c => c.Orders
.Sum(o => o.TotalAmount) > 1000)
.Where(c => !c.Invoices.Any(i => !i.IsPaid))
.ToListAsync();When I first saw it, I thought, “Wow, this is complicated”. But then I realized, it’s actually readable. Anyone familiar with C# can follow the logic.
This was the moment where LINQ felt more like a language for business logic than just a query tool.
Common Mistakes I Made (and Fixed)
Looking back, I made plenty of mistakes with LINQ and EF Core. Here are some that might save you headaches:
- Fetching too much data. I used to call .ToList() too early, then filter in memory. Big performance hit. Solution: keep the query as an IQueryable until the last moment.
- Overusing Include. I thought I needed to load everything with Include. But that led to heavy queries. Projection (Select) is usually better if you don’t need the full object graph.
- Forgetting indexes. Just because LINQ generates SQL doesn’t mean the database will be fast. I had to work with DBAs to add indexes when certain queries became slow.
- Trying to do everything in LINQ. Sometimes, the business logic was just too complex to squeeze into a query. I learned it’s okay to break it up: fetch some data with LINQ, then apply logic in C#.
When to Switch Back to Raw SQL
I’ll be honest, LINQ isn’t always the answer. In one project, we had a report query that involved multiple unions, window functions, and complex aggregations. I tried LINQ for days, but it just became unreadable.
In the end, I wrote a raw SQL query and mapped it to a DTO:
var reportData = await _dbContext.ReportResults
.FromSqlRaw(@"
SELECT ProductId, SUM(Quantity) as TotalQuantity
FROM OrderLines
GROUP BY ProductId
HAVING SUM(Quantity) > 100
")
.ToListAsync();And you know what? That was fine. LINQ is great, but sometimes SQL is still the best tool.
Final Thoughts
LINQ with EF Core is one of those tools that grows with you. At first, it’s just a nicer way to query a table. But as you use it in real projects, you start to see it as a way to express business rules, filter and shape data, and keep your code readable.
If I could give one piece of advice: don’t be afraid of LINQ. Start simple, grow step by step, and soon you’ll be writing queries that directly capture your business logic. And when it doesn’t fit, it’s okay to drop back to SQL.
