Asif Bhat, Ph.D.’s Post

#PartOne EFCore Snippets Micro ORM vs Full ORM When building data access in .NET applications, a performance key difference in your application is choosing the right (Object Relational Mapping) for your application. Full ORM (Entity Framework Core) : EF Core handles the entire data pipeline for you — model mapping, change tracking, migrations, and SQL generation. You work purely with C# objects. Ex: var user = await context.Users.FindAsync(1); user.Name = "Updated"; await context.SaveChangesAsync(); Micro ORM (Dapper) : Dapper is lightweight — it maps query results to objects but nothing more. You write the SQL by yourself. Ex: var user = await connection.QueryFirstAsync ( "SELECT * FROM Users WHERE Id = @id", new { id = 1 } ); await connection.ExecuteAsync( "UPDATE Users SET Name = @name WHERE Id = @id", new { id = 1, name = "Updated" } ); Key To Choose : Start with EF Core for productivity. Use Dapper when profiling reveals bottlenecks or when you need complex queries that EF Core cannot optimize effectively. #dotnet #csharp #efcore #dapper #orm #softwareengineering #sqlserver #backenddevelopment #dotnetdeveloper #database

🚀 EF Core vs ADO.NET vs Dapper vs Raw SQL (What should you actually use in real projects?) Most developers ask: 👉 “Which one is best?” Real answer: 👉 It depends on performance, control, and development speed Let’s break it down clearly 👇 🔹 1. EF Core (ORM – High Productivity) ✅ Best for: Rapid development CRUD operations Clean architecture projects 💡 Pros: Less code (LINQ-based) Automatic mapping Change tracking Easy maintenance ⚠️ Cons: Slower than Dapper/SQL Less control over queries Can generate inefficient SQL 👉 Use when: ✔ Speed of development > Performance ✔ Large team / scalable codebase 🔹 2. Dapper (Micro ORM – Balanced) ✅ Best for: Performance + simplicity Optimized queries 💡 Pros: Very fast (near raw SQL) Minimal overhead Full SQL control Easy mapping ⚠️ Cons: No change tracking More manual work than EF 👉 Use when: ✔ Performance matters ✔ You still want clean mapping 🔹 3. ADO.NET (Low-Level – Full Control) ✅ Best for: Legacy systems Fine-grained DB control 💡 Pros: Maximum control No abstraction overhead Stable & reliable ⚠️ Cons: Verbose code Hard to maintain Manual mapping required 👉 Use when: ✔ Working with old systems ✔ Need low-level DB operations 🔹 4. Raw SQL (Direct Power) ✅ Best for: Complex queries Reports / analytics Bulk operations 💡 Pros: Fastest execution Full SQL optimization Best for heavy joins ⚠️ Cons: Risk of SQL injection (if not handled) Harder to maintain No abstraction 👉 Use when: ✔ Query is complex ✔ Performance is critical 🔥 Real-World Strategy (What Pros Do) 👉 No one uses just one ✔ EF Core → CRUD, Admin panels ✔ Dapper → High-performance APIs ✔ Raw SQL → Reports, heavy queries ✔ ADO.NET → Legacy / special cases 💡 Final Rule 👉 “Don’t choose tools based on trend… choose based on problem.” ⚡ Pro Tip If your API is slow: ❌ Don’t blame the server first 👉 Check your ORM queries #dotnet #backend #efcore #dapper #sql #softwareengineering #performance #developers

Your ORM is lying to you about performance. Every time Sequelize or TypeORM "helps" you query a database, it's generating bloated SQL, running unnecessary JOINs, and adding overhead you never asked for. Handwriting SQL in Node.js isn't nostalgia - it's a measurable latency win. Here's a real example. Instead of this: const users = await User.findAll({ where: { active: true }, include: [Profile] }); Write this: const { rows } = await pool.query('SELECT u.id, u.name, p.bio FROM users u JOIN profiles p ON p.user_id = u.id WHERE u.active = true'); The second version runs one predictable query. No magic. No hidden SELECT N+1 traps. No model hydration cost. At scale, these differences compound. Teams switching from ORM-heavy codebases to raw pg or better-sqlite3 queries regularly report 20-40% reductions in average query response time. Practical takeaway - profile your slowest endpoints first, extract the ORM query, rewrite it in raw SQL, and benchmark before and after. The data will make the decision for you. Have you benchmarked ORM-generated queries against handwritten SQL in your current project? #Nodejs #WebDevelopment #BackendDevelopment #SQL #PerformanceOptimization #SoftwareEngineering

Calling .ToList() feels so natural at the end of a LINQ query. But placing it in the wrong spot can silently kill your API's performance. 📉 Following up on my last post about IEnumerable vs IQueryable, the real danger often hides in exactly when the query gets executed. When chaining LINQ methods, execution order is everything: ❌ .ToList().Where(...) ✅ .Where(...).ToList() Here is why placement matters: .ToList() triggers Materialization. It tells Entity Framework: "I am done building the query. Execute it immediately and bring the data into memory." If you call it before your .Where() or .Select() clauses, EF may fetch more data than needed from the database, and the rest of your filtering happens in your server's RAM. ⚠️ If you defer .ToList() to the very end, EF can combine all your conditions into a more efficient SQL query. ⚡ Lower database load. Faster response times. 💡 Lesson learned: Defer execution as long as possible. Build the query first. Execute it last. I’m continuing my series on small .NET changes that actually impact performance. ⚡ Next: AsNoTracking() — when and why to use it 👀 What is your strategy for catching inefficient LINQ queries before they hit production? 👇 #csharp #dotnetcore #softwarearchitecture #efcore #backend

Class 35 : #chaicode web dev cohort Lots of things learned in SQL class. Here are my learnings: - SQL vs NoSQL db - what is ORM? - creating tables - datatypes - constraints - Aggregate functions - group by clause - pattern matching - logical operators - filtering rows - sorting - pagination using limit and offset - Execution order of the query Hitesh Choudhary Piyush Garg Akash Kadlag Jay Kadlag Suraj Kumar Jha Chai Code

When “Correct” Queries Don’t Return Correct Results. Recently, I ran into an interesting backend issue where a jpa query was logically correct, the database data was correct, yet some expected results were missing. At first, everything looked fine: The filters were valid The relationships made sense The data existed in the database But the output didn’t match expectations. What was really happening: The issue came from how the ORM translates relationships between entities into SQL. In some cases: A simple condition on a related entity can silently change the type of SQL join being generated Instead of keeping all records and checking conditions safely, the query can become restrictive This can unintentionally exclude valid rows before filtering even happens So even though the logic was correct, the execution plan was not what I expected. The fix: The solution was to make the relationship handling explicit instead of relying on implicit behavior. By explicitly controlling how related data is joined (for example using a LEFT JOIN instead of relying on automatic joins), the query became predictable again and all valid results were correctly included. Key lesson: In ORM-based systems (like JPA/Hibernate): The way you write a condition is not always how it executes Relationships can change query behavior silently Small assumptions can lead to missing or incomplete data Takeaway: Always validate not only what your query says, but how your ORM translates it into SQL. Because sometimes, the issue is not the data or logic… but the hidden behavior behind the abstraction. #SoftwareEngineering #BackendDevelopment #ORM #Hibernate #SpringBoot #SystemDesign #Debugging #Programming #LessonsLearned

How to Delete and Update Millions of Rows in EF Core Without Loading a Single Entity | by Chris Woodruff https://lnkd.in/eJq2Rg9m #dotnet #entityframework #efcore #csharp #data #database #orm

Lately while working with Entity Framework, I noticed something I used to ignore: how data is loaded. At first, I didn’t care much… as long as the data was coming, everything looked fine. But after facing some slow endpoints, I realized the problem wasn’t in the logic, it was in how many queries were being sent to the database. That’s when I started paying attention to loading. Eager Loading When I know I’ll need related data, I just load everything from the beginning. This helped me reduce extra queries a lot. But sometimes I load data that I don’t really use. Explicit Loading Here I choose when to load the related data. I don’t load it from the start, but when I need it, I call it manually. It gives me more control, but needs a bit more code. Lazy Loading This one loads data automatically when I access it. At first it feels easy, but it can cause many hidden queries without noticing ( N+1 problem ). I faced this before and it affected performance. What I learned: I started thinking more about how data is loaded, not just getting it. If I know what I need → Eager If I want control → Explicit If I use Lazy → I’m careful with it #dotnet #backend #efcore #entityframeworkcore #performance #csharp #database #systemdesign

✅ .NET Core Tips #10 — ORM (What it really is & why it matters) Most developers start by writing raw SQL. Then they discover ORM — and everything changes. 🔍 What is ORM? ORM (Object-Relational Mapper) lets you interact with your database using C# objects instead of raw SQL queries. You stop thinking in tables and start thinking in models. 🔧 Without ORM You write queries manually, handle parameters, execute commands, and map results yourself. It gives control — but leads to verbose code, more bugs, and harder maintenance. ✅ With ORM (EF Core) var student = await _context.Students   .FirstOrDefaultAsync(s => s.Id == studentId); Cleaner, readable, and type-safe. Less boilerplate, fewer mistakes. Same result — better developer experience. 🚀 Why ORM is powerful: • No scattered SQL strings in your codebase • Strongly-typed queries with compile-time safety • Automatic relationship handling • Code-first migrations — schema lives in your code • Easier database switching (SQL Server → PostgreSQL) • No need to rewrite your full data access layer when changing DB 💡 Real-world tip ORM works best for everyday CRUD operations. For complex or performance-critical queries — use Stored Procedures alongside it. EF Core + Stored Procedures is a practical balance in production systems. ⚡ Performance tip — Use Projection Even with ORM, avoid over-fetching data. Use Select() to fetch only what you need: var students = _context.Students   .Select(s => new { s.FullName, s.Email })   .ToList(); This reduces SQL payload, improves performance, and keeps queries efficient. 🧰 Popular .NET ORMs: • EF Core — standard choice for most applications • Dapper — lightweight and fast, stays close to SQL • NHibernate — mature and enterprise-focused 📌 Final thought ORM doesn't replace SQL. It builds on top of it. Combine ORM + SQL knowledge + Projection techniques — and you'll write scalable, maintainable, high-performance applications. #dotnet #csharp #efcore #orm #aspnetcore #backend #softwaredevelopment #performance

Stop writing bad database code right now. Entity Framework will save you. I wasted 3 years writing messy SQL queries by hand. Then I found Entity Framework for .NET. It changed everything for me. Here's how Entity Framework makes your life easier: No more SQL injection attacks Your data stays safe automatically Write less code EF does the hard work for you Fix bugs faster Changes happen in one place only Switch databases easily Move from SQL Server to PostgreSQL fast Track changes automatically EF knows what data changed Work faster Stop writing the same code over and over Your team will thank you. Your boss will notice the speed. Your users will get features faster. Entity Framework is not just a tool. It's your secret weapon. Start using it today. Stop wasting time on basic database tasks. What's holding you back from trying Entity Framework? Drop a comment below.