Data Access Interview Questions

ADO.NET is the low-level part of .NET for talking to databases. For SQL Server, the main classes come from Microsoft.Data.SqlClient: SqlConnection (the connection), SqlCommand (SQL text or a procedure name), SqlParameter (a value), SqlDataReader (rows, forward only), SqlTransaction, and SqlException. Dapper and EF Core are built on top of it.

ExecuteScalar returns one value, the first column of the first row, such as a COUNT(*). ExecuteReader returns a reader over many rows. ExecuteNonQuery runs INSERT, UPDATE, or DELETE and returns the number of rows changed. Each has an Async version that should be preferred.

SQL injection is user input changing what a query does, because the input was joined into the SQL text. Typing x' OR '1'='1 into an unsafe student search returned all 10,012 students. Prevent it with parameters: the value travels separately from the SQL, so it is only ever a value.

ADO.NET keeps a pool of open connections per connection string. Disposing a connection returns it to the pool, and the next open takes it back almost instantly: 100 opens took 3 ms after a first open of 472 ms. So open as late as possible, dispose as early as possible, and never keep a connection in a field.

A NULL column arrives as DBNull.Value, not null, and calling GetString or GetInt32 on it throws. Check reader.IsDBNull(ordinal) first, then read the value, for example Address = reader.IsDBNull(i) ? null : reader.GetString(i).

Set CommandType = CommandType.StoredProcedure and use the procedure name as the command text. Add input parameters normally, and add the output parameter with Direction = ParameterDirection.Output and a size. After ExecuteNonQueryAsync, read its Value, as with @ReceiptNumber from usp_RecordFeePayment.

AddWithValue guesses the SQL type from the C# value, and a string becomes NVARCHAR. Against a VARCHAR column, SQL Server converts the column and can scan instead of seek: finding a student by admission number read 37 pages instead of 2. Use Parameters.Add with an explicit SqlDbType and size that match the column.

The pool is exhausted: connections are being opened and not returned. Look for connections, readers, or commands that are not disposed, especially on exception paths, and for connections held during slow work. Fix with await using everywhere, short-lived connections, and async calls; only then consider raising Max Pool Size.

When you need full control or maximum throughput with no mapping layer: bulk loading with SqlBulkCopy, streaming huge result sets, table-valued parameters, or a small hot path measured as too slow. For ordinary CRUD, the hand-written mapping costs more in time and bugs than it saves, so most teams use EF Core or Dapper and drop to ADO.NET only where measured.

Dapper is a micro-ORM: you write the SQL, and it adds parameters and maps result rows to objects. EF Core is a full ORM: it also writes the SQL from LINQ, tracks changes to entities, and saves them. Dapper gives more control with less magic; EF Core gives more productivity for everyday CRUD.

QueryAsync<T> for a list, QuerySingleOrDefaultAsync<T> for one row by a unique key (null if none, an exception if two), and ExecuteScalarAsync<int> for a count. ExecuteAsync runs INSERT, UPDATE, DELETE, or a procedure and returns the rows changed.

Pass an object, often anonymous: new { LastName = lastName } fills @LastName. Dapper creates real parameters, so the values never become part of the SQL text and injection is not possible. Joining user input into the SQL string yourself is still unsafe.

By name, ignoring case. A column with no matching property is ignored, and a property with no column keeps its default value; neither raises an error. That makes renamed columns or missing aliases a silent bug, so use explicit column lists with aliases and test each query.

Multi-mapping reads one joined row into several objects, for example an Enrollment and its Student, and a function you supply connects them. splitOn names the column where each next object starts; it defaults to Id. For one-to-many results, a dictionary keeps one parent per key.

Use DynamicParameters: add the inputs, and add the output with a type, a size, and direction: ParameterDirection.Output. Call ExecuteAsync with commandType: CommandType.StoredProcedure, then read it with Get<string>("@ReceiptNumber").

Dapper sends C# strings as NVARCHAR(4000). Against the VARCHAR column, SQL Server converts the column and may scan the index instead of seeking. Pass new DbString { Value = ..., IsAnsi = true, Length = 15 } so the parameter matches the column type, then confirm the seek in the plan.

Send them together with QueryMultipleAsync and read each result in order from the grid reader: one round trip instead of six. Select only the columns the dashboard shows, mapped to DTOs or tuples. If it is still slow, look at the queries' plans and consider caching (Module 12).

Yes, commonly: EF Core for writes and ordinary reads of entities, where change tracking and LINQ save time, and Dapper for heavy reports and dashboards, where hand-tuned SQL matters. Both use the same connection string and can share a transaction. Keep each behind a repository or query interface so callers do not know which tool answers.

EF Core is Microsoft's ORM for .NET: you query with LINQ, and it writes the SQL, creates objects, tracks changes, and saves them. A DbContext is one session with the database; each DbSet<T> is a table you can query. A context should be short-lived, such as one per web request.

Database-first starts from an existing database: schema changes are made in SQL, and EF Core maps to the result, often helped by scaffolding. Code-first starts from C# classes, and EF Core migrations create and change the database. Database-first fits shared or older databases; code-first fits new apps that own their database.

It looks at every tracked entity, finds the added, modified, and deleted ones, and sends the matching INSERT, UPDATE, and DELETE statements in one transaction. Changing one property of a loaded student produced an UPDATE of that single column.

It reads a database and writes one class per table and view, plus a DbContext with the full mapping: lengths, types, keys, indexes, defaults, and relationships. Without --no-onconfiguring, it also puts the connection string in the generated code, which leaks credentials into source control.

It translates a property value to a column value and back. In the SMS, Gender is an enum in C# and a CHAR(1) code in the database: HasConversion(g => ((char)g).ToString(), c => (Gender)c[0]). HasConversion<string>() stores an enum by name. Converters also apply to query parameters.

The query calls something SQL cannot run, such as your own C# method (s.GetAgeOn(date)). EF Core throws instead of loading everything. Rewrite the condition with translatable parts, such as a date comparison, rather than calling ToList first and filtering in memory.

Keep the entities in the core project and put all mapping in IEntityTypeConfiguration classes in the data project. EF Core can call a constructor whose parameter names match properties, map read-only properties that are configured explicitly, and use value converters for enums. The entities need no EF Core attributes or base classes.

Regenerating overwrites anything added by hand, such as rules, constructors, or enums, and makes the domain model follow the database. Keep scaffolded output as a reference only, map hand-written entities through configuration classes, and change one configuration line when a column changes.

The database scripts are the source of truth, reviewed and versioned like code, and run in order by deployment. EF Core mappings follow them. Keep both in one repository, deploy the script before the code that needs it, prefer additive changes, and run integration tests against a database built from the scripts so drift fails the build.

A property that points to a related entity instead of holding a value. A reference navigation points to one entity, such as Enrollment.Section; a collection navigation holds many, such as FeeInvoice.Payments. The foreign key, such as SectionId, is always loaded; navigations only when requested.

Include loads a navigation of the entity being queried; ThenInclude continues from that navigation to the next level. Kabir's student, enrollments, sections, classes, and years came back in one query with Include(s => s.Enrollments).ThenInclude(e => e.Section).ThenInclude(...).

Eager loading fetches related data in the same query with Include. Explicit loading fetches it later, on request, with Entry(...).Collection(...).LoadAsync(). Lazy loading fetches it automatically the first time a navigation is read, through proxies, which hides queries and often causes N+1 problems.

From either side, for example builder.HasMany(i => i.Payments).WithOne(p => p.FeeInvoice) .HasForeignKey(p => p.FeeInvoiceId). Use WithMany() with no argument when the other side has no collection. Configuring it explicitly is needed when names do not follow conventions, such as ClassId with a SchoolClass navigation.

A condition EF Core adds to every query on an entity, configured with HasQueryFilter. It suits rules that must never be forgotten, such as hiding soft-deleted rows or, in multi-tenant systems, other tenants' rows. IgnoreQueryFilters switches it off; EF Core 10 can switch off one named filter.

EF Core reads generated values back with an OUTPUT clause, and SQL Server rejects OUTPUT without INTO on tables with enabled triggers. Declare each trigger with ToTable("Marks", t => t.HasTrigger("tr_Marks_Audit")), and EF Core switches to a method that works.

The invoices were loaded without their payments, so Payments is empty and AmountPaid is 0. Nothing throws. Add Include(i => i.Payments), or better for a report, project the sum of payments in SQL, and add a test with a partly paid invoice so the bug cannot return quietly.

Generally no. Every navigation access can issue a query, so loops become N+1 problems, the queries are invisible in the code, and serializing an entity can trigger loads across the whole graph. Load deliberately with Include or project to DTOs, which also keeps the API's shape independent of the entities.

Put both rules in named global query filters on every affected entity, set the tenant from the request through the DbContext, and allow switching a filter off only in specific admin or repair code paths. Add database-side protection too, such as row-level security or filtered views for tenancy, and tests that fail if an entity lacks the filter.

It tells EF Core not to keep snapshots of the loaded entities for change tracking. Use it for read-only queries such as lists, reports, and searches. Loading 10,012 students took about 65-77 ms with it and 376-456 ms without it, with identical SQL.

Running one query for a list and then one more query for each item: 1 + N round trips. It often hides in a loop or behind lazy loading. Fix it with one query that returns everything, such as an Include or a projection with a subquery: 51 queries became 1 for a section's average marks.

Order the query, then use Skip((page - 1) * pageSize).Take(pageSize), which becomes OFFSET and FETCH in SQL. Return the total count for page numbers, and end the ordering with a unique column such as Id, so rows never repeat or disappear between pages.

It reads only the columns the DTO needs, creates no entities, and is never tracked. It also keeps internal columns, such as audit fields, away from the screen or API. Use entities when you will change and save them; use projections for everything that only reads.

They send one UPDATE or DELETE statement for every row matching a LINQ query, without loading entities. Graduating 1,006 students took 1 command instead of 25. They skip the change tracker, entity rules, and anything your SaveChanges logic does, such as setting audit columns, and tracked objects in memory are not refreshed.

Including several collections in one query repeats each parent row for every combination of child rows, so the result can grow enormously. AsSplitQuery() loads each collection with its own query. The trade-off is extra round trips and possible inconsistency if data changes between the queries.

Measure first: log the SQL and count the queries for one page load. Many similar queries mean N+1; one huge query may mean too many includes or columns; a slow single query needs its execution plan and indexes. Then apply the matching fix and measure again with the same data.

For very frequent queries with the same shape, such as a lookup by key on every request. It saves EF Core's work of matching the LINQ expression to its cached SQL, not the database time. On SchoolDb, 1,000 lookups were about 20% faster. For ordinary queries, the extra code is not worth it.

Agree on defaults: projections for reads, no lazy loading, paging on every list, bulk methods for simple mass changes. Log slow queries and query counts per request in every environment, add tests or alerts that fail when a request exceeds a query budget, and run load tests on realistic data volumes such as 10,000 students before release.

A unit test checks one class's logic alone, with fakes for its dependencies, and runs in milliseconds. An integration test checks parts working together, such as a repository with the real SQL Server, so it finds wrong SQL, mapping, and constraints that fakes cannot.

A [Fact] is a test with no inputs. A [Theory] runs once for each set of inputs supplied by [InlineData] rows or a [MemberData] property; each set is reported as its own test. The fee tests use a theory for zero, negative, and too-large amounts.

A structure for each test: Arrange sets up the objects and data, Act calls the one behaviour being tested, and Assert checks the result. It keeps tests short, readable, and focused on one behaviour.

A fake is a simple working implementation, such as a repository that stores students in a list. A mock is usually generated by a library and set up with expected calls and answers. Fakes are easy to read and reuse; mocks are handy for checking interactions. Both depend on the code using interfaces.

Run each test inside a transaction that is rolled back. With TransactionScope created with TransactionScopeAsyncFlowOption.Enabled and never completed, every connection opened inside joins the transaction, whatever the tool, and disposal rolls it back. Alternatives are a fresh database per run or deleting test data, both slower or more fragile.

A test written for a bug that already happened, so it cannot come back unnoticed. In SchoolApp, an invoice loaded without its payments showed the full balance; a test now loads Diya's partly paid invoice and checks that the balance is 25,500.

Write one integration test class against the interface, and run every test once per implementation with a theory and MemberData. Four tests became twelve for ADO.NET, Dapper, and EF Core. Where behaviour legitimately differs, such as exception types, assert on what matters to callers or translate the differences in the data layer.

Shared state: tests that rely on data left by others, missing rollbacks, identity or sequence values assumed to be fixed, or a transaction lost after await because the scope was not created with async flow. Make each test arrange its own data, roll back everything it writes, and never assume run order.

Many fast unit tests for rules and services with fakes; integration tests for every repository and query against a database built from the real scripts; a few end-to-end API tests for key flows such as admission and fee payment; and data-consistency checks. Run unit tests on every change, integration tests in CI, and keep realistic data volumes for performance tests.

Each SaveChangesAsync call runs all its INSERT, UPDATE, and DELETE statements in one transaction: either all are saved or none. Taking a payment saved the payment and the invoice's new status together. For several saves or mixed raw SQL, use Database.BeginTransactionAsync().

Two users read the same data, both change it based on what they read, and the second save overwrites or contradicts the first. Two clerks each took 6,000 from an invoice with 6,000 due, because both screens showed 6,000; without a check, the invoice ended paid 16,000 of 10,000.

A binary value that SQL Server changes automatically on every update of the row; you never set it. Comparing the version you read with the current one tells you whether someone changed the row in between, which is the basis of optimistic concurrency.

Pessimistic concurrency locks the data while working, so nobody else can change it, as usp_RecordFeePayment does with UPDLOCK. Optimistic concurrency takes no lock and checks at save time that the data has not changed, such as with a rowversion. Optimistic suits screens where users think between reading and saving; pessimistic suits short operations with frequent conflicts.

Map a ROWVERSION column as a concurrency token with IsRowVersion(), either on a byte[] property or as a shadow property, builder.Property<byte[]>("RowVersion").IsRowVersion(). EF Core then adds AND RowVersion = @original to every UPDATE and DELETE, and throws DbUpdateConcurrencyException when no row matches.

Nothing: the rollback undoes the database, but the objects keep their changed values, and the change tracker treats them as saved. The cancelled invoice still said Cancelled in memory while the database said Unpaid. Discard the DbContext and reload after a rollback.

Not show the raw exception. Translate DbUpdateConcurrencyException into a clear error, such as 50014 "the invoice was changed by someone else", reload the invoice so the clerk sees the current balance, and let them decide again. Never retry a money operation automatically with stale values.

When one business operation needs several SaveChangesAsync calls, mixes EF Core with raw SQL or another tool, or must read and write under one isolation level. Keep it short, commit at the end, and roll back on any error. If connection retries are enabled, run the transaction inside the execution strategy, because retries cannot replay a user-started transaction.

Default to EF Core for everyday work with rules, because units of work, concurrency tokens, and LINQ remove much code and many bugs. Use Dapper for heavy reports where hand-tuned SQL matters, and ADO.NET for special cases such as bulk copy or streaming. Keep all of them behind repository interfaces, and keep critical rules in the database too.

A C# class, written by dotnet ef migrations add, with an Up method that changes the database to match the model and a Down method that undoes it. EF Core records applied migrations in the __EFMigrationsHistory table, so database update runs only the ones a database does not have yet.

Database-first: the database is designed in SQL, and EF Core reads it. Code-first: the C# classes are the design, and migrations create and change the database. Database-first suits shared or older databases; code-first suits a database that one application owns.

migrations add <Name> to create one, migrations script to see its SQL, database update to apply all pending ones, database update <Name> to go back or forward to one, migrations remove to delete the last unapplied one, and migrations list to see them.

EF Core guesses from the model difference, and some guesses lose data. A rename combined with another change becomes DropColumn plus AddColumn. A new required foreign key gets default 0 that matches no row. Defaults such as cascade delete may not be what the business wants. Read Up, Down, and the SQL, and fix them.

Edit the generated migration: create the new table, copy the data with migrationBuilder.Sql, add and fill the new key column, and only then drop the old column and add the foreign key. Write Down to move the data back. Wrap hand-written SQL in EXEC(N'...') so idempotent scripts still compile, and test both directions.

Not from the app at startup. Generate an idempotent script (migrations script --idempotent) for a reviewer or DBA to run, or build a migration bundle (migrations bundle) that the release pipeline runs with the production connection string. Both skip migrations already in __EFMigrationsHistory.

Usually not in production. EF Core 9 and later take a lock so two instances do not both migrate, but the app's login then needs permission to change the schema, a long migration delays start-up, and a failed migration stops the app from starting. Run migrations once in the release, with a script or bundle, under a separate login.

One kind of change per migration, reviewed in pull requests with its SQL. Never edit or remove a migration others have applied; add a new one. Resolve snapshot merge conflicts carefully and run migrations has-pending-model-changes in the build so a model that disagrees with the migrations fails CI. Test migrations, including Down, against a copy of real data before release.

Use expand and contract: first add new structures without breaking the old ones (new nullable columns or tables, both written by the app), backfill data in batches, switch reads to the new structure in a release, and only later remove the old columns. Keep every step backward compatible with the running version, avoid long blocking operations (online index builds, batched updates), and have a tested rollback for each step.