.NETData accessReported by candidates

EF Core, Dapper or ADO.NET: what are the trade-offs, and when do you use each?

Asked at Infosys, Kellton, Fingent, MSC Technology, IDBI Intech and Sage IT, often as its ADO.NET parts (DataReader vs DataSet, ExecuteNonQuery); a 2026 IBM report too.

Say this first · about 30 seconds

ADO.NET is the base layer: I open the connection, write the SQL and read every column myself. Full control, but a lot of code.
Dapper sits on top of it: I still write the SQL, and Dapper maps the rows to objects. I use it for heavy read queries and reports.
EF Core is a full ORM: I write LINQ, it writes the SQL, tracks changes and runs migrations. I use it for normal create, read, update and delete.
In our API we used EF Core for writes and Dapper for the slow reports, on the same connection.

Make it yours · Navistar month-end dealer report (invented example)

Situation: at Navistar Financial the month-end dealer report joined contracts, payments and dealers with window functions the had tuned. Written in EF Core LINQ, it produced different, slower SQL and took 6 minutes.
What I did: I kept EF Core for the API's writes and moved only that report to Dapper, running the 's SQL as is, with parameters, on EF Core's own connection.
Result: the report ran in under a minute, the SQL was reviewable by the , and nothing else in the app changed.
What I'd say: I pick per query, not per project: EF Core by default, Dapper where I need exact SQL.

The code, simplified

Invented to show the shape, in C#. Tap Next to walk the October report.

Navistar.sln · where these 2 files sit

Reports/

Diagram · step 1The job asks for October's dealer report.

Month-end job

DealerQueriesDapper

Azure SQL

DealerMonthAsync(Oct)

DealerReportJob.cs · line 1 · 1 of 2The month-end job runs this on 1 November for October.

Fix later in your repo The SQL lives in a C# constant; it could move to a versioned .sql file next to the migrations.

Say it out loud, then check

Show the 30-second answer

ADO.NET is the base layer: I open the connection, write the SQL and read every column myself. Full control, but a lot of code.
Dapper sits on top of it: I still write the SQL, and Dapper maps the rows to objects. I use it for heavy read queries and reports.
EF Core is a full ORM: I write LINQ, it writes the SQL, tracks changes and runs migrations. I use it for normal create, read, update and delete.
In our API we used EF Core for writes and Dapper for the slow reports, on the same connection.

Check your answer: tick what you said

All three run on ADO.NET: a connection, a command with parameters, a data reader. EF Core and Dapper write that code for you.
ADO.NET: you write SQL and map each column by hand. Most control, most code, mapping errors show up only at run time.
Dapper: a micro-ORM. You write the SQL; it maps rows to objects. Fast and simple; no change tracking, no migrations, no LINQ.
EF Core: a full ORM. LINQ translated to SQL, change tracking, in one transaction, migrations. Most productive for CRUD.
EF Core's costs: tracking memory on big reads (use and only the columns you need), and LINQ that turns into bad SQL or N+1 queries.
They mix: EF Core for writes, Dapper for reports, on the same connection and transaction.
Always parameters, never string-built SQL, in all three: with concatenation is as injectable as raw ADO.NET.

You said 0 of 7

Then add your example: Navistar month-end dealer report (invented example)

Situation: at Navistar Financial the month-end dealer report joined contracts, payments and dealers with window functions the had tuned. Written in EF Core LINQ, it produced different, slower SQL and took 6 minutes.
What I did: I kept EF Core for the API's writes and moved only that report to Dapper, running the 's SQL as is, with parameters, on EF Core's own connection.
Result: the report ran in under a minute, the SQL was reviewable by the , and nothing else in the app changed.
What I'd say: I pick per query, not per project: EF Core by default, Dapper where I need exact SQL.

See it

ADO.NETSQL + mapping

ADO.NET underneathevery library ends here

Connection

CommandSQL + params

DataReaderrows

Azure SQLBMW FS contracts

1/6Before: the BMW FS statement API used plain ADO.NET. Every query: open, command, read each column.

In plain words

Words first. SQL is the language the database understands (SELECT ... WHERE ...). A query is one request to the database. A connection is the open line from your app to the database; opening one is slow, so .NET keeps a pool of open ones and lends them out. A transaction groups several changes so they all save or none do. An ORM (object-relational mapper) turns database rows into C# objects and back. LINQ is C#'s way of writing queries as code (, ). Change tracking means EF Core keeps a copy of every object it loaded, so it can see later what you changed. N+1 means one query for a list, then one more query for each item in it.
Think of getting a parcel from a warehouse. ADO.NET is walking in yourself: you find the shelf, read every label and carry each box out. Nothing is hidden, but it's slow work.
Dapper is handing the clerk a precise slip that says exactly which shelf and box (that's your SQL), and the clerk packs it into your bag (your objects).
EF Core is ordering by description ("all of customer 42's contracts", in LINQ). The warehouse works out where to look, and keeps a note of everything you took. When you hand items back with changes, it compares them to its note and updates only what changed. That note is change tracking.

The senior answer · about 2 minutes

What each one does for you

ADO.NET is the bottom layer every .NET data library uses. You create a , a with your SQL and parameters, and read rows from a , column by column. Nothing is hidden, so nothing is surprising, but every query is 15 lines and a renamed column only fails when that line runs.
Dapper is a small library on top. You still write the SQL, but one call, , runs it and builds the objects for you by matching column names to property names. It's close to hand-written ADO.NET speed, with a fraction of the code.
EF Core is a full ORM, an object-relational mapper. You query with LINQ and it writes the SQL. It also tracks every object it loads, so you change properties and call , and it works out the UPDATE statements and runs them in one transaction. Migrations keep the database schema in step with the code.

Where EF Core costs you

Tracking: every loaded entity (an object EF maps to a table row) gets a snapshot, a copy of its values, so EF can spot changes. For a read-only screen of thousands of rows that's wasted memory and time. , plus into a small object with only the columns the screen needs, fixes most of it.
Hidden queries: a loop that touches a related list can send one query per row, the N+1 problem. That's the try-first bug below. I turn on EF's SQL logging in development and watch the dependency count in , so I see how many queries a request really sends.
Complex SQL: window functions (SQL that ranks or totals across rows), query hints or a report the tuned by hand don't fit LINQ well. There I write the SQL myself, with Dapper or with EF's .

How I choose

Default to EF Core for an API with normal create, read, update and delete: it's the fastest to build and the safest to change. Use Dapper for read-heavy reports and hot endpoints where I want exact SQL. Plain ADO.NET only for special cases: bulk copy with , or a library that can't take a dependency.
They aren't exclusive. Dapper works on EF Core's own connection, so one request can save with EF and read a report with Dapper inside the same transaction.
Whatever I pick, I measure before I switch. Most "EF is slow" problems I've seen were N+1 queries or loading whole entities, and were fixed without leaving EF.

All in one flow

Coming Not written yet: the card pass adds it.

The story

Before
BMW FS's statement API was written years ago in plain ADO.NET. Every query was a connection, a command, a reader and a loop that read each column by hand. Twenty queries meant twenty copies of that pattern.
Why it had to change
A renamed a column. The C# still compiled, because column names are just strings inside the code. The error came at run time, on the first statement run that night. And every new screen took a day of copy-paste mapping.
How it works now
The team moved everyday create, read, update and delete to EF Core: schema changes now go through EF migrations, so a rename happens on the C# property first and every query that uses the old name breaks the build instead of production; and saving changes is one call. Then the monthly statement run, 200,000 rows, got slow under EF's tracking. That one report moved to Dapper with SQL the tuned. All three still run on ADO.NET underneath; they differ in how much of it you write. The diagram walks it.
What happens next
EF Core for the API's normal work, Dapper for the heavy report, on the same connection. Plain ADO.NET stayed only for a bulk import that uses .
What we gained
Less code to write and review, mapping errors caught at build time, and the hot report as fast as hand-written SQL. The trap that's left is EF writing more queries than you think: the try-first exercise below.

In the lending API

EF Core: LINQ in, SQL out. Read-only, so no tracking, and only the columns the screen needs.

Only the key lines are shown; Deep walks the whole file.

Lending.sln · where this file sits

src/Infrastructure/Data/

ContractRepository.cs · line 4 · 1 of 4_db is the field, EF Core's door to the database. ASP.NET Core creates it per request and passes it to the repository's constructor (dependency injection).

The code, file by file

The same kind of read three ways in the lending API. Tap Next to walk the diagram with the code.

Lending.sln · where these 3 files sit

src/Infrastructure/
- Data/
- Reports/

Diagram · step 1Before: the BMW FS statement API used plain ADO.NET. Every query: open, command, read each column.

ADO.NETSQL + mapping

ADO.NET underneathevery library ends here

Connection

CommandSQL + params

DataReaderrows

Azure SQLBMW FS contracts

StatementReaderAdo.cs · line 2 · 1 of 7Called by the statement job for one contract.

What they ask next

The follow-ups interviews really ask. Answer out loud, then tap to check.

ReportedDataReader vs DataSet vs DataTable: what is the difference?
A streams rows forward-only and read-only while the connection is open: fast, little memory, one row at a time. A is a disconnected copy in memory: one or more tables (each a ), with relations between them, filled by a , which opens the connection if it was closed and closes it again when done. A is one of those tables. Today you see DataSets mostly in older Web Forms code; new code reads with a reader, Dapper or EF Core.
Where this was reported
ReportedDoes ExecuteNonQuery return a value?
Yes: an int, the number of rows affected by an INSERT, UPDATE or DELETE. For any other statement, like a SELECT or CREATE TABLE, it returns -1, and it also returns -1 when the SQL turns on SET NOCOUNT ON (a setting that stops SQL Server reporting row counts). For a single value use ; for rows use .
```
int changed = cmd.ExecuteNonQuery();
// UPDATE of 3 rows -> 3
// SELECT            -> -1
```
Where this was reported

Follow-up tree

Answer, then open the next level. That is the order an interviewer digs in.

Reporteda candidate was asked Intervieweran interviewer asks it

ReportedDataReader vs DataSet vs DataTable: what is the difference?
A streams rows forward-only and read-only while the connection is open: fast, little memory, one row at a time. A is a disconnected copy in memory: one or more tables (each a ), with relations between them, filled by a , which opens the connection if it was closed and closes it again when done. A is one of those tables. Today you see DataSets mostly in older Web Forms code; new code reads with a reader, Dapper or EF Core.
Where this was reported
ReportedDoes ExecuteNonQuery return a value?
Yes: an int, the number of rows affected by an INSERT, UPDATE or DELETE. For any other statement, like a SELECT or CREATE TABLE, it returns -1, and it also returns -1 when the SQL turns on SET NOCOUNT ON (a setting that stops SQL Server reporting row counts). For a single value use ; for rows use .
```
int changed = cmd.ExecuteNonQuery();
// UPDATE of 3 rows -> 3
// SELECT            -> -1
```
Where this was reported
ReportedWhat are the steps to call a database with ADO.NET?
Connection string from config; create a in a using and open it; create a with the SQL and parameters; run , or ; read the results; let using close it. Close doesn't really disconnect: it returns the connection to the pool, so the next open is cheap.
Where this was reported
InterviewerCan you use EF Core and Dapper in the same app?
Yes, and it's common. Dapper works on any connection, including EF Core's own (_db.Database.GetDbConnection()). For one transaction across both, start it with _db.Database.BeginTransactionAsync() and pass GetDbTransaction() to Dapper's call. Then a save through EF and a write through Dapper commit or together.
Microsoft Learn: sharing a connection and transaction
InterviewerAre Dapper and EF Core safe from SQL injection?
Only if you use parameters. LINQ queries are safe: variables become parameters and constants are escaped. But with a string you built by concatenation, or Dapper with $"... WHERE Number = '{input}'", is as open as raw ADO.NET. Use with an interpolated string (EF turns the holes into parameters) or pass a parameter object to Dapper.
```
// injectable
_db.Contracts.FromSqlRaw(
  "... WHERE Number = '" + input + "'");
// safe: becomes @p0
_db.Contracts.FromSql(
  $"... WHERE Number = {input}");
```
Microsoft Learn: raw SQL and parameters
What does AsNoTracking actually save?
With tracking, EF keeps every loaded entity in its change tracker plus a snapshot of its values, so can compare and find changes. On a read-only screen that work and memory are wasted. skips it. If you also into a small object, nothing is tracked anyway, because it's not an entity.

Production bugs, and how to tell them

Each one: what you see, why, the fix in code, and the line to say.

Try first: why does one page send 301 queries?
```
BMW FS portal, "My contracts" for a fleet customer with 300 leases:
page load 9.4 s
Application Insights: 301 SQL dependency calls for one request

Read the Before code and find the bug before you open the cause.
```
Cause
This is the N+1 problem. The first query loads the 300 contracts. Then the loop asks the database for each contract's payments, one query per contract: 1 + 300 = 301 round trips. Each is fast on its own; together they take seconds. Tests used a customer with 2 contracts, so it looked fine.
Fix
Ask for everything in one query: a that includes the payment total, so EF Core writes one SELECT with a subquery (a small query inside the big one that sums each contract's payments). Add because the page only reads. A test counts the SQL commands for a customer with 3 contracts and expects 1.
Diagram · step 1Query 1 loads the fleet customer's 300 contracts. So far, fine.
ContractsPageone request
EF Core
Azure SQL
contracts for customer 42
SELECT ... FROM Contracts
300 rows
Before · ContractsPage.cs · line 1 · 1 of 4Query 1: all 300 contracts, fully loaded and tracked.
Say it
The page ran one query for the list and one more per contract inside a loop, the classic N+1. Fine with 2 contracts, 301 round trips for a fleet customer. We saw it as 301 dependency calls on one request in . We replaced the loop with one projection that sums payments in SQL, so it's one query whatever the count, and added a test that counts the queries.

Production incident

Not on this card A basic concept card: its production story is the try-first N+1 bug here; slow-query tuning has its own scenario card.

Traps

Don't say~~Dapper is always much faster than EF Core.~~SayFor simple reads with and a projection, EF Core is close. Measure first; the big wins are fixing N+1 and loading fewer columns.
Don't say~~EF Core protects you from SQL injection.~~SayLINQ does. with a concatenated string doesn't. Parameters, always, in every library.
Don't say~~Close the connection to disconnect from the database.~~Say or returns it to the connection pool; the real connection stays open for the next caller. Always using, so a leak can't empty the pool.

Traps

What should you say instead? Tap to check.

Don't sayDapper is always much faster than EF Core.
Say instead
For simple reads with and a projection, EF Core is close. Measure first; the big wins are fixing N+1 and loading fewer columns.
Don't sayEF Core protects you from SQL injection.
Say instead
LINQ does. with a concatenated string doesn't. Parameters, always, in every library.
Don't sayClose the connection to disconnect from the database.
Say instead
or returns it to the connection pool; the real connection stays open for the next caller. Always using, so a leak can't empty the pool.

Live coding

Easy first, then harder. Set a timer and type it before you look.

Task 1

Reportedeasy · 10 minutes

Write a program that connects to a SQL database, runs a parameterised query, prints the rows, and disconnects.

Sage IT: write a program to connect to and disconnect from a SQL database

Stuck? Hints

Which three ADO.NET types do you need?
How do you make sure the connection is closed even if the query throws?
Where does the value for the WHERE clause go, if not into the SQL string?

Show a solution

Lending.sln · where this file sits

Program.cs · line 2 · 1 of 5For .

What the interviewer checks

using (or await using) on connection, command and reader.
A parameter with its type (Add(..., SqlDbType.Decimal)), never string concatenation; can say why can hurt.
Async calls: , , .
Connection string from configuration, not hard-coded with a password.
Can say what "disconnect" really does: back to the pool.

With AI tools

Copilot writes EF Core fluently, and it writes N+1 loops just as fluently: an query inside a looks clean and passes small tests. It also suggests with string interpolation when asked for raw SQL. When I review AI data-access code I check for queries inside loops, on read-only paths, instead of whole entities, and parameters on every raw SQL call. Then I look at the SQL it really sends, in the EF logs.

Glossary: every name, one line each →

My notes

Paste the NOTES block from your doubt chat here. The grader never reads notes; the coach reads "Still unclear".

EF Core: efficient querying (Microsoft Learn)EF Core: tracking vs no-tracking EF Core: SQL queries and parameters Dapper (GitHub)SqlCommand.ExecuteNonQuery DataSet, DataTable and DataReader (ADO.NET)

Where it was asked

MSC Technology: EF vs Dapper Kellton: ADO.NET vs EF Core, pros and cons TraviYo: DataSet vs DataReader Fingent: steps to call a database with ADO.NET IDBI Intech: does ExecuteNonQuery return a value?DEV IT: what is Dapper?