Preventing N+1 Query Problem in EF Core

𝐈𝐬 𝐲𝐨𝐮𝐫 𝐀𝐏𝐈 𝐬𝐥𝐨𝐰𝐞𝐫 𝐭𝐡𝐚𝐧 𝐢𝐭 𝐬𝐡𝐨𝐮𝐥𝐝 𝐛𝐞? 𝐓𝐡𝐞 𝐫𝐞𝐚𝐬𝐨𝐧 𝐦𝐢𝐠𝐡𝐭 𝐛𝐞 𝐚 𝐬𝐢𝐧𝐠𝐥𝐞 𝐥𝐢𝐧𝐞 𝐨𝐟 𝐜𝐨𝐝𝐞. In Entity Framework, there is a big difference between building a query and running one. When you use IQueryable, you are just writing instructions. Nothing happens in the database yet. The danger starts when you call .𝑻𝒐𝑳𝒊𝒔𝒕(). This is called "Materialization." It tells your application to stop building the query, go to the database, and pull all that data into your server’s memory. 𝐓𝐡𝐞 𝐰𝐫𝐨𝐧𝐠 𝐰𝐚𝐲: var data = context.Users.ToList().Where(u => u.IsActive); In this case, you fetch every single user from the database first. The filtering happens in your app's RAM. If you have 100,000 users, your app will struggle. 𝐓𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐰𝐚𝐲: var data = context.Users.Where(u => u.IsActive).ToList(); By placing the filter before the list command, EF Core sends a smart SQL query to the database. You only download the users you actually need. 𝐓𝐡𝐞 𝐫𝐮𝐥𝐞 𝐢𝐬 𝐬𝐢𝐦𝐩𝐥𝐞: Use IQueryable to filter, sort, and join. Use .𝑻𝒐𝑳𝒊𝒔𝒕() only when you are ready to use the final results. Build your query fully before you execute it. Your database and your users will thank you. #DotNet #EFCore #EntityFramework #SoftwareEngineering #CleanCode #ProgrammingTips

Your API is fast. Until it calls the database 100 times. --- The N+1 problem is not obvious at first. One request looks simple. But under the hood: → 1 query becomes N queries → Each additional record triggers another call --- It usually comes from: → Lazy loading → Poor query design → ORM misuse --- The impact is real: → Increased latency → Database overload → Poor scalability --- Fixing it is not about scaling your app. It’s about fixing how you access data. --- What actually works: → Eager loading → Optimized queries (joins) → Batching requests --- Performance issues are often not code problems. They are data-access problems. --- #Backend #SystemDesign #Performance #DotNet #SoftwareEngineering #APIDesign

𝗧𝗵𝗲 𝗘𝗮𝘀𝗶𝗲𝘀𝗧 𝗪𝗮𝘆 𝗧𝗼 𝗔𝗱𝗱 𝗗𝗿𝗮𝗴 𝗮𝗻𝗱 𝗗𝗿𝗼𝗽 𝗧𝗼 𝗬𝗼𝘂𝗿 𝗥𝗮𝗶𝗹𝘀 𝗔𝗽𝗽 You need to add drag and drop to your Rails app. You can use a library called SortableJS and Importmaps to make it easy. Here's how you can do it in 5 steps: - Add a position column to your database. - Pin the SortableJS library using Importmaps. - Build the list in your view and attach a Stimulus controller to it. - Generate a Stimulus controller and import SortableJS. - Create a route and controller action to handle the drag and drop. First, add a position column to your database. Run this migration: rails g migration AddPositionToTasks position:integer rails db:migrate You can use the acts_as_list gem to handle the math of shifting positions. Add this to your model: class Task < ApplicationRecord acts_as_list end Pin the SortableJS library using Importmaps: bin/importmap pin sortablejs Build the list in your view: <ul data-controller="sortable"> <% @tasks.order(:position).each do |task| %> <li data-id="<%= task.id %>" class="p-4 bg-white border mb-2 cursor-move"> <%= task.name %> </li> <% end %> </ul> Generate a Stimulus controller and import SortableJS: import { Controller } from "@hotwired/stimulus" import Sortable from "sortablejs" Create a route and controller action to handle the drag and drop: resources :tasks do member do patch :move end end class TasksController < ApplicationController def move @task = Task.find(params[:id]) @task.insert_at(params[:position].to_i) head :ok end end Source: https://lnkd.in/gXXfuN_X

𝗧𝗵𝗲 𝗘𝗮𝘀𝗶𝗲𝘀𝗧 𝗪𝗮𝘆 𝗧𝗼 𝗔𝗱𝗱 𝗗𝗿𝗮𝗴 𝗮𝗻𝗱 𝗗𝗿𝗼𝗽 𝗧𝗼 𝗬𝗼𝘂𝗿 𝗥𝗮𝗶𝗹𝘀 𝗔𝗽𝗽 You need to add drag and drop to your Rails app. You can use a library called SortableJS and Importmaps to make it easy. Here's how you can do it in 5 steps: - Add a position column to your database. - Pin the SortableJS library using Importmaps. - Build the list in your view and attach a Stimulus controller to it. - Generate a Stimulus controller and import SortableJS. - Create a route and controller action to handle the drag and drop. First, add a position column to your database. Run this migration: rails g migration AddPositionToTasks position:integer rails db:migrate You can use the acts_as_list gem to handle the math of shifting positions. Add this to your model: class Task < ApplicationRecord acts_as_list end Pin the SortableJS library using Importmaps: bin/importmap pin sortablejs Build the list in your view and attach a Stimulus controller to it: <ul data-controller="sortable"> <% @tasks.order(:position).each do |task| %> <li data-id="<%= task.id %>" class="p-4 bg-white border mb-2 cursor-move"> <%= task.name %> </li> <% end %> </ul> Generate a Stimulus controller and import SortableJS: import { Controller } from "@hotwired/stimulus" import Sortable from "sortablejs" Create a route and controller action to handle the drag and drop: resources :tasks do member do patch :move end end class TasksController < ApplicationController def move @task = Task.find(params[:id]) @task.insert_at(params[:position].to_i) head :ok end end Source: https://lnkd.in/gXXfuN_X

Our API was slow. Not broken. Just... slow. Users weren't complaining yet. But the numbers didn't lie. I dug in - and what I found wasn't bad code. It was missing indexes, unoptimized queries, and EF Core doing way more work than it needed to. Three things that moved the needle: -> Identified N+1 query patterns and fixed them -> Added targeted indexes on high-traffic columns -> Rewrote a few critical EF Core queries to raw SQL where it mattered Result? 35% reduction in average API response time. The lesson I keep coming back to: Performance problems rarely live where you think they do. Always profile first. Optimize second. #BackendDevelopment #DotNet #SQLServer #EntityFramework #SoftwareEngineering

𝐈𝐟 𝐲𝐨𝐮'𝐫𝐞 𝐜𝐚𝐥𝐥𝐢𝐧𝐠 𝐓𝐨𝐋𝐢𝐬𝐭() 𝐭𝐨𝐨 𝐞𝐚𝐫𝐥𝐲 𝐢𝐧 .𝐍𝐄𝐓… 𝐲𝐨𝐮’𝐫𝐞 𝐩𝐫𝐨𝐛𝐚𝐛𝐥𝐲 𝐤𝐢𝐥𝐥𝐢𝐧𝐠 𝐲𝐨𝐮𝐫 𝐚𝐩𝐩’𝐬 𝐩𝐞𝐫𝐟𝐨𝐫𝐦𝐚𝐧𝐜𝐞. 💥 𝐓𝐡𝐞 𝐏𝐫𝐨𝐛𝐥𝐞𝐦 I still see this in production code: 𝘷𝘢𝘳 𝘶𝘴𝘦𝘳𝘴 = 𝘤𝘰𝘯𝘵𝘦𝘹𝘵.𝘜𝘴𝘦𝘳𝘴.𝘛𝘰𝘓𝘪𝘴𝘵()              .𝘞𝘩𝘦𝘳𝘦(𝘹 => 𝘹.𝘐𝘴𝘈𝘤𝘵𝘪𝘷𝘦)              .𝘛𝘰𝘓𝘪𝘴𝘵(); Looks harmless. It’s not. 🧠 𝐖𝐡𝐚𝐭’𝐬 𝐀𝐜𝐭𝐮𝐚𝐥𝐥𝐲 𝐇𝐚𝐩𝐩𝐞𝐧𝐢𝐧𝐠 That first 𝐓𝐨𝐋𝐢𝐬𝐭(): • Pulls entire table into memory • Executes query immediately • Moves filtering from DB → app So instead of SQL doing the work… your server does it. ✅ 𝐓𝐡𝐞 𝐅𝐢𝐱 𝘷𝘢𝘳 𝘶𝘴𝘦𝘳𝘴 = 𝘤𝘰𝘯𝘵𝘦𝘹𝘵.𝘜𝘴𝘦𝘳𝘴           .𝘞𝘩𝘦𝘳𝘦(𝘹 => 𝘹.𝘐𝘴𝘈𝘤𝘵𝘪𝘷𝘦)           .𝘛𝘰𝘓𝘪𝘴𝘵(); Now: • Filtering happens in SQL • Only needed data is fetched • Way better performance ⚡ 𝐒𝐞𝐧𝐢𝐨𝐫 𝐈𝐧𝐬𝐢𝐠𝐡𝐭 𝐈𝐐𝐮𝐞𝐫𝐲𝐚𝐛𝐥𝐞 = runs in database 𝐈𝐄𝐧𝐮𝐦𝐞𝐫𝐚𝐛𝐥𝐞 = runs in memory The moment you call 𝐓𝐨𝐋𝐢𝐬𝐭()… you switch execution context. 🎯 𝐓𝐚𝐤𝐞𝐚𝐰𝐚𝐲 𝐓𝐨𝐋𝐢𝐬𝐭() isn’t just a method. It’s a trigger. 𝐔𝐬𝐞 𝐢𝐭 𝐭𝐨𝐨 𝐞𝐚𝐫𝐥𝐲 — 𝐲𝐨𝐮 𝐩𝐚𝐲 𝐟𝐨𝐫 𝐢𝐭 𝐥𝐚𝐭𝐞𝐫. Be honest . . . how often have you seen this in real code? Attached diagram for better understanding. #DotNet #APIDesign #Microservices #BackendDevelopment #SoftwareArchitecture #SystemDesign #Developers #CSharp #DistributedSystems #Performance

I just shipped my first open source .NET library to NuGet. It's called ReportGen — a report generation library for .NET 8. The idea was simple: most teams write the same "export to CSV/Excel" code over and over. I wanted a clean, strongly-typed library that handles it properly. What I built in the past few weeks: → Fluent builder API (Report.Create().From().AddColumn().ToCsv()) → CSV + Excel exporters (file path or stream — works directly with ASP.NET Response.Body) → Reusable report templates → Attribute-based column discovery The hardest part wasn't the code — it was the architecture decisions. When should something be a contract vs an implementation? What belongs in Core vs Exporters? These are the questions I never had to answer at work because someone else already solved them. Building from scratch forced me to think at a different level. This is v0.1.0 — just the foundation. Already planned for upcoming versions: → 🔍 Dynamic column selection (frontend-driven, whitelist-based) → 🌍 CultureInfo support for locale-aware formatting → 📊 Multi-sheet workbook support → 📬 Delivery abstraction (email, S3, Azure Blob) The packages are live: dotnet add package ReportGen.Core dotnet add package ReportGen.Exporters 🔗 GitHub: https://lnkd.in/gxU6vgzH 📦 NuGet: NuGet: https://lnkd.in/gvPYqxyZ

Most developers focus on writing better code. But the real performance killer? A poorly optimized database. 👇 I see this mistake constantly in production systems — developers optimize their code but completely ignore the database layer. Here are the 4 Database Optimization techniques that separate average developers from great ones: 1️⃣ Indexing Without proper indexes, your DB scans every single row on every query. Result: Speed up data retrieval by up to 100x. 2️⃣ Normalization Storing duplicate data seems fine at first — until it causes bugs, inconsistencies, and bloated storage. Result: Reduce data redundancy, keep your DB clean. 3️⃣ Query Optimization A single poorly written SQL query can bring a production server to its knees. Result: Write efficient SQL queries, save server resources. 4️⃣ Partitioning When your tables hit millions of rows, performance degrades fast. Result: Split large tables for better performance and scalability. Master these 4 — and you'll build faster apps, ace backend interviews, and write production-grade code. Sharing this quick animated breakdown for the dev community 🎥 🌐 Visit our website: www.developersstreet.com 📞 +91 9412892908 #BackendDevelopment #DatabaseOptimization #SQL #SoftwareEngineering #WebDevelopment #Programming #DevelopersStreet #TechTips #CareerGrowth #IndianDeveloper

Behind every fast dashboard or report, there is an optimized database. From indexing to query optimization, small improvements in SQL design can lead to significant performance gains. As a Data Analyst, focusing on efficient data processing is key to delivering reliable insights📊 #SQL #DataAnalyst #Database #Optimization

Why Every Backend Developer Should Use 'EXPLAIN ANALYZE' Recently, I was stuck debugging an issue where a table in our app was taking forever to load. The query itself looked perfectly fine. No syntax issues. No obvious bugs. But the response was still slow in production. That’s when I used EXPLAIN ANALYZE. Sometimes a SQL query works perfecty, but still becomes slow in production. That’s because writing a query is one thing — knowing how the database runs it is another. EXPLAIN ANALYZE helps You See That It shows: Whether your query is using an index. If the database is scanning the whole table. Which part of the query is slowest. Total execution time. EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; Think of it like an X-ray for your SQL query. Instead of guessing why a query is slow, you can see exactly what the database is doing. A lot of backend performance issues are just queries we never inspected. #SQL #BackendDevelopment #PostgreSQL #DatabaseOptimization #SoftwareEngineering