MSAccess Queries to Entity Framework Core: What Actually Changes (And What Breaks)

Written by DeeDee Walsh | Mar 29, 2026 11:09:51 PM

Microsoft Access has seven types of queries. Entity Framework Core has LINQ. That sentence makes the migration sound simple, and the simplicity is exactly where projects go wrong.

Access queries aren't just SQL. They're a runtime environment. A select query in Access auto-generates a scrollable, editable datasheet. An action query pops a confirmation dialog. A crosstab query pivots data using a syntax that doesn't exist in standard SQL. A parameter query prompts the user with an input box - mid-execution.

When you move to Entity Framework Core, every one of these behaviors needs to go somewhere. Some translate cleanly. Others require rethinking. A few need to be decomposed into multiple layers of your new architecture.

This guide catalogs what happens to each Access query type during modernization to Blazor/.NET with EF Core, with code for the patterns that trip people up.

The Seven Query Types and Where They Land

Before gettung into code, here's the map:

Access Query Type	What It Does	EF Core Equivalent
Select	Retrieves data, auto-generates editable datasheet	LINQ query → read-only projection or tracked entities
Action: Update	Modifies existing records in bulk	`ExecuteUpdateAsync()` or tracked entity updates
Action: Append	Inserts records from one table to another	`AddRangeAsync()` + `SaveChangesAsync()`
Action: Delete	Removes records matching criteria	`ExecuteDeleteAsync()` or tracked removal
Action: Make-Table	Creates a new table from query results	EF Core migration + seeding, or raw SQL
Crosstab	Pivots rows into columns dynamically	LINQ GroupBy + client-side pivot (no direct equivalent)
Parameter	Prompts user for input at runtime	Method parameters + Blazor UI
Union	Combines result sets from multiple queries	LINQ `.Union()` or `.Concat()`
Pass-Through	Sends raw SQL directly to a backend database	`FromSql()` or `ExecuteSqlAsync()`

Now let's walk through each one.

Select Queries: The Straightforward Part

Access select queries are the closest thing to a 1:1 translation. The SQL concepts, WHERE, ORDER BY, JOIN, GROUP BY, all have direct LINQ equivalents.

Access SQL (saved query: qryActiveCustomers):


 SELECT CustomerID, CompanyName, ContactName, Phone
FROM tblCustomers
WHERE IsActive = True
ORDER BY CompanyName;

C# (EF Core):

csharp
public async Task<List<CustomerDto>> GetActiveCustomersAsync()
{
    return await _context.Customers
        .Where(c => c.IsActive)
        .OrderBy(c => c.CompanyName)
        .Select(c => new CustomerDto
        {
            CustomerId = c.CustomerId,
            CompanyName = c.CompanyName,
            ContactName = c.ContactName,
            Phone = c.Phone
        })
        .ToListAsync();
}

What changes: The query isn't a standalone object anymore. In Access, qryActiveCustomers exists in the navigation pane so you can open it, edit it, bind a form to it. In EF Core, it's a method on a service class, called by a Blazor component. The query result isn't editable by default. If you want edit-in-place behavior (like Access's datasheet), you need to build that into your Blazor component explicitly.

Multi-Table Joins

Access makes joins easy through the visual query designer, but the underlying SQL is standard. The translation is clean:

Access SQL:

sql
SELECT o.OrderID, o.OrderDate, c.CompanyName, 
       p.ProductName, od.Quantity, od.UnitPrice
FROM ((tblOrders AS o 
    INNER JOIN tblCustomers AS c ON o.CustomerID = c.CustomerID)
    INNER JOIN tblOrderDetails AS od ON o.OrderID = od.OrderID)
    INNER JOIN tblProducts AS p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= #1/1/2024#
ORDER BY o.OrderDate DESC;

C# (EF Core with navigation properties):

csharp
var orders = await _context.Orders
    .Where(o => o.OrderDate >= new DateTime(2024, 1, 1))
    .Select(o => new OrderSummaryDto
    {
        OrderId = o.OrderId,
        OrderDate = o.OrderDate,
        CompanyName = o.Customer.CompanyName,
        Items = o.OrderDetails.Select(od => new OrderItemDto
        {
            ProductName = od.Product.ProductName,
            Quantity = od.Quantity,
            UnitPrice = od.UnitPrice
        }).ToList()
    })
    .OrderByDescending(o => o.OrderDate)
    .ToListAsync();

Notice that EF Core's navigation properties eliminate explicit JOIN syntax. If your entity model is configured correctly, o.Customer.CompanyName generates the join automatically. This is one of the genuine wins of modernization; the code expresses relationships semantically rather than mechanically.

The Self-Join Trap

Access developers use self-joins for hierarchical data (employees with managers, categories with parent categories). These work the same way in EF Core, but the entity model configuration matters:

Access SQL:

sql
SELECT e.EmployeeName, m.EmployeeName AS ManagerName
FROM tblEmployees AS e 
    LEFT JOIN tblEmployees AS m ON e.ManagerID = m.EmployeeID;

C# (Entity model + query):

 // In your entity class
public class Employee
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public int? ManagerId { get; set; }

    // Navigation properties
    public Employee? Manager { get; set; }
    public ICollection<Employee> DirectReports { get; set; }
}

// In your service
var employees = await _context.Employees
    .Select(e => new
    {
        e.EmployeeName,
        ManagerName = e.Manager != null ? e.Manager.EmployeeName : null
    })
    .ToListAsync();

Update Queries: Bulk Modifications

Access update queries modify records in place. Click Run, get a confirmation dialog ("You are about to update 347 rows"), click Yes, done.

Access SQL (update query):

sql
UPDATE tblProducts 
SET UnitPrice = UnitPrice * 1.05
WHERE CategoryID = 3 AND Discontinued = False;

C# (EF Core — two approaches):

csharp
// Approach 1: ExecuteUpdateAsync (.NET 7+) — best for bulk operations
await _context.Products
    .Where(p => p.CategoryId == 3 && !p.Discontinued)
    .ExecuteUpdateAsync(p => p
        .SetProperty(x => x.UnitPrice, x => x.UnitPrice * 1.05m));

// Approach 2: Load and modify — use when you need business logic per record
var products = await _context.Products
    .Where(p => p.CategoryId == 3 && !p.Discontinued)
    .ToListAsync();

foreach (var product in products)
{
    product.UnitPrice = _pricingService.ApplyIncrease(product);
}

await _context.SaveChangesAsync();

What breaks: The confirmation dialog. Access bakes user confirmation into the query runtime. In Blazor, you need to implement this yourself; show the user how many records will be affected, get confirmation, then execute. If your Access app has update queries triggered by button clicks on forms, you need to replicate this flow in your component:

csharp
// In your Blazor component
private async Task ApplyPriceIncrease()
{
    var affectedCount = await _context.Products
        .CountAsync(p => p.CategoryId == selectedCategory && !p.Discontinued);

    var confirmed = await DialogService.ConfirmAsync(
        $"This will update {affectedCount} products. Continue?");

    if (confirmed)
    {
        await _productService.ApplyPriceIncreaseAsync(selectedCategory, 0.05m);
        await LoadProducts();  // refresh the grid
    }
}

Update Queries with Joined Tables

Access allows you to update one table based on values from another. This is common in data cleanup and synchronization:

Access SQL:

sql
UPDATE tblOrders INNER JOIN tblCustomers 
    ON tblOrders.CustomerID = tblCustomers.CustomerID
SET tblOrders.ShipRegion = tblCustomers.Region
WHERE tblOrders.ShipRegion Is Null;

C# (EF Core):

csharp
// Load orders with null ShipRegion and their customers
var ordersToFix = await _context.Orders
    .Include(o => o.Customer)
    .Where(o => o.ShipRegion == null)
    .ToListAsync();

foreach (var order in ordersToFix)
{
    order.ShipRegion = order.Customer.Region;
}

await _context.SaveChangesAsync();

For large datasets, consider raw SQL to avoid loading everything into memory:

csharp
 await _context.Database.ExecuteSqlAsync($@"
    UPDATE o SET o.ShipRegion = c.Region
    FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE o.ShipRegion IS NULL");

Append Queries: Moving Data Between Tables

Access append queries copy records from one table (or query) into another. They're commonly used for archiving, staging imported data, and consolidating records.

Access SQL (append query):

 INSERT INTO tblOrderArchive (OrderID, OrderDate, CustomerID, Total)
SELECT OrderID, OrderDate, CustomerID, Total
FROM tblOrders
WHERE OrderDate < #1/1/2023#;

C# (EF Core):

 public async Task ArchiveOldOrdersAsync(DateTime cutoffDate)
{
    var ordersToArchive = await _context.Orders
        .Where(o => o.OrderDate < cutoffDate)
        .Select(o => new OrderArchive
        {
            OrderId = o.OrderId,
            OrderDate = o.OrderDate,
            CustomerId = o.CustomerId,
            Total = o.Total
        })
        .ToListAsync();

    _context.OrderArchives.AddRange(ordersToArchive);
    await _context.SaveChangesAsync();
}

What breaks at scale: If you're archiving 100,000 records, loading them all into memory with ToListAsync() then calling AddRange is going to hurt. For large data movements, drop to raw SQL:

 await _context.Database.ExecuteSqlAsync($@"
    INSERT INTO OrderArchive (OrderID, OrderDate, CustomerID, Total)
    SELECT OrderID, OrderDate, CustomerID, Total
    FROM Orders
    WHERE OrderDate < {cutoffDate}");

This executes entirely on the database server; no round trips, no memory pressure.

Delete Queries: Removing Records

Access SQL (delete query):

SQL
DELETE FROM tblTempImport
WHERE ImportDate < #1/1/2024# AND Status = 'Processed';

C# (EF Core):

 csharp
// .NET 7+ bulk delete — no loading into memory
await _context.TempImports
    .Where(t => t.ImportDate < new DateTime(2024, 1, 1) && t.Status == "Processed")
    .ExecuteDeleteAsync();

The cascade trap: In Access, delete queries on related tables either fail (referential integrity enforced) or cascade (cascade delete enabled). In EF Core, cascade behavior is configured in your entity model:

csharp 
// In your DbContext OnModelCreating
modelBuilder.Entity<Order>()
    .HasMany(o => o.OrderDetails)
    .WithOne(od => od.Order)
    .OnDelete(DeleteBehavior.Cascade);  // mirrors Access cascade delete

Get this wrong and you'll either get foreign key constraint exceptions at runtime or accidentally delete more data than intended. Audit every relationship's delete behavior during migration.

Make-Table Queries: The Awkward One

Make-table queries create a new table from query results. Access developers use them for staging data, creating snapshots, and building denormalized reporting tables.

Access SQL (make-table query):

SQL
SELECT CustomerID, CompanyName, Sum(Total) AS TotalRevenue,
       Count(OrderID) AS OrderCount
INTO tblCustomerSummary
FROM tblOrders INNER JOIN tblCustomers 
    ON tblOrders.CustomerID = tblCustomers.CustomerID
GROUP BY CustomerID, CompanyName;

There is no EF Core equivalent. EF Core doesn't create tables at runtime. This is one of those Access patterns that requires an architecture rethink.

Your options, depending on what the make-table query is actually for:

If it's for reporting snapshots:

csharp 
// Use a materialized view or a scheduled background job
// that populates a permanent summary table
public async Task RefreshCustomerSummaryAsync()
{
    await _context.Database.ExecuteSqlAsync(
        $"TRUNCATE TABLE CustomerSummary");

    await _context.Database.ExecuteSqlAsync($@"
        INSERT INTO CustomerSummary (CustomerID, CompanyName, TotalRevenue, OrderCount)
        SELECT o.CustomerID, c.CompanyName, SUM(o.Total), COUNT(o.OrderID)
        FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID
        GROUP BY o.CustomerID, c.CompanyName");
}

If it's for temporary staging during an import:

 csharp
// Use temp tables or table-valued parameters, or
// work with in-memory collections instead
var stagingData = await _importService.ParseFileAsync(uploadedFile);
var validated = _validationService.Validate(stagingData);
_context.TargetTable.AddRange(validated);
await _context.SaveChangesAsync();

If it's for "I need a disposable table to experiment with", that workflow doesn't exist in production web apps. During modernization, trace what downstream process consumes the make-table output and replace the pattern with a service method that returns the data directly.

Crosstab Queries: Where Things Get Genuinely Hard

Crosstab queries are Access's killer feature that nobody talks about during migration planning. They pivot rows into columns dynamically; months become column headers, categories become row headers, and the intersection shows aggregated values. The Access-specific SQL uses TRANSFORM and PIVOT, which don't exist in standard SQL or LINQ.

Access SQL (crosstab query):

SQL
 TRANSFORM Sum(tblOrderDetails.Total) AS SumOfTotal
SELECT tblProducts.CategoryName
FROM tblProducts INNER JOIN tblOrderDetails 
    ON tblProducts.ProductID = tblOrderDetails.ProductID
GROUP BY tblProducts.CategoryName
PIVOT Format(tblOrderDetails.OrderDate, "yyyy-mm");

This produces a grid like:

CategoryName	2024-01	2024-02	2024-03	...
Beverages	$4,200	$3,800	$5,100	...
Condiments	$1,900	$2,100	$1,750	...

C# (EF Core + client-side pivot):

csharp
public async Task<CrosstabResult> GetSalesByCategoryAndMonthAsync()
{
    // Step 1: Get the flat data from the database
    var rawData = await _context.OrderDetails
        .Include(od => od.Product)
        .Select(od => new
        {
            od.Product.CategoryName,
            Month = od.OrderDate.ToString("yyyy-MM"),
            od.Total
        })
        .ToListAsync();

    // Step 2: Pivot client-side
    var months = rawData
        .Select(d => d.Month)
        .Distinct()
        .OrderBy(m => m)
        .ToList();

    var pivoted = rawData
        .GroupBy(d => d.CategoryName)
        .Select(g => new CrosstabRow
        {
            CategoryName = g.Key,
            Values = months.ToDictionary(
                m => m,
                m => g.Where(d => d.Month == m).Sum(d => d.Total))
        })
        .OrderBy(r => r.CategoryName)
        .ToList();

    return new CrosstabResult { Columns = months, Rows = pivoted };
}

// Supporting classes
public class CrosstabResult
{
    public List<string> Columns { get; set; }
    public List<CrosstabRow> Rows { get; set; }
}

public class CrosstabRow
{
    public string CategoryName { get; set; }
    public Dictionary<string, decimal> Values { get; set; }
}

Why this is hard: The column headers are dynamic. In Access, the PIVOT clause auto-generates columns based on data. In Blazor, you need to build the table dynamically:

 Blazor
<table class="table">
    <thead>
        <tr>
            <th>Category</th>
            @foreach (var month in result.Columns)
            {
                <th>@month</th>
            }
        </tr>
    </thead>
    <tbody>
        @foreach (var row in result.Rows)
        {
            <tr>
                <td>@row.CategoryName</td>
                @foreach (var month in result.Columns)
                {
                    <td>@row.Values.GetValueOrDefault(month, 0m).ToString("C")</td>
                }
            </tr>
        }
    </tbody>
</table>

Performance note: For small to medium datasets, the client-side pivot works fine. For large datasets (100K+ rows), push the aggregation to SQL using GROUP BY and only transfer the summarized data to the client. Or consider a SQL Server PIVOT statement via FromSql().

Parameter Queries: The Input Box Problem

Access parameter queries prompt the user for input when the query runs. You see a dialog box: "Enter CustomerID:", type a value, click OK, and the query filters on it.

Access SQL:

 SELECT * FROM tblOrders 
WHERE CustomerID = [Enter Customer ID:]
AND OrderDate BETWEEN [Start Date:] AND [End Date:];

This has no EF Core equivalent because it conflates two responsibilities: user input and data retrieval. In Blazor, these are separate layers:

Blazor component (user input):

 <div class="filter-panel">
    <label>Customer ID:</label>
    <input type="number" @bind="customerId" />

    <label>Start Date:</label>
    <input type="date" @bind="startDate" />

    <label>End Date:</label>
    <input type="date" @bind="endDate" />

    <button @onclick="SearchOrders">Search</button>
</div>

@code {
    private int? customerId;
    private DateTime? startDate;
    private DateTime? endDate;
    private List<OrderDto> orders = new();

    private async Task SearchOrders()
    {
        orders = await OrderService.SearchOrdersAsync(customerId, startDate, endDate);
    }
}

Service method (data retrieval):

C#
 public async Task<List<OrderDto>> SearchOrdersAsync(
    int? customerId, DateTime? startDate, DateTime? endDate)
{
    var query = _context.Orders.AsQueryable();

    if (customerId.HasValue)
        query = query.Where(o => o.CustomerId == customerId.Value);

    if (startDate.HasValue)
        query = query.Where(o => o.OrderDate >= startDate.Value);

    if (endDate.HasValue)
        query = query.Where(o => o.OrderDate <= endDate.Value);

    return await query
        .Select(o => new OrderDto { /* projection */ })
        .ToListAsync();
}

The conditional query building with AsQueryable() is actually more powerful than Access parameters. Access parameters are all-or-nothing. If you don't provide a value, the query returns nothing. The pattern above makes each filter optional, which is usually what users actually want.

Union Queries

Access union queries combine results from multiple SELECT statements. They require writing raw SQL in Access's SQL view because the query designer doesn't support them.

Access SQL:

 SELECT CustomerID, CompanyName, 'Customer' AS SourceType
FROM tblCustomers
UNION ALL
SELECT VendorID, VendorName, 'Vendor' AS SourceType
FROM tblVendors
ORDER BY CompanyName;

C# (EF Core):

 var customers = _context.Customers
    .Select(c => new ContactDto
    {
        Id = c.CustomerId,
        Name = c.CompanyName,
        SourceType = "Customer"
    });

var vendors = _context.Vendors
    .Select(v => new ContactDto
    {
        Id = v.VendorId,
        Name = v.VendorName,
        SourceType = "Vendor"
    });

var combined = await customers
    .Union(vendors)       // or .Concat() for UNION ALL behavior
    .OrderBy(c => c.Name)
    .ToListAsync();

Watch the difference: LINQ's .Union() deduplicates (like SQL UNION). Use .Concat() for UNION ALL behavior, which keeps all rows including duplicates. Access developers who used UNION ALL for performance should use .Concat().

Pass-Through Queries

Access pass-through queries send raw SQL directly to a linked SQL Server (or other ODBC backend), bypassing the Jet/ACE engine entirely. They're typically used for stored procedures, complex T-SQL, or performance-critical operations.

Access pass-through query:

SQL
 EXEC sp_GetMonthlyRevenue @Year = 2024, @Region = 'West'

C# (EF Core):

 csharp
// Calling a stored procedure that returns entity data
var revenue = await _context.MonthlyRevenues
    .FromSql($"EXEC sp_GetMonthlyRevenue @Year = {year}, @Region = {region}")
    .ToListAsync();

// For procedures that don't return entities
var results = await _context.Database
    .SqlQuery<MonthlyRevenueDto>(
        $"EXEC sp_GetMonthlyRevenue @Year = {year}, @Region = {region}")
    .ToListAsync();

Pass-through queries are actually the easiest to migrate because they're already raw SQL. The main change is wrapping them in proper service methods with typed parameters instead of string concatenation.

Domain Aggregate Functions in Queries

Access developers embed DLookup(), DSum(), DCount(), and other domain functions directly in queries, not just in VBA code. This is a significant source of hidden performance problems.

Access SQL with domain functions:

SQL
 SELECT OrderID, OrderDate, CustomerID,
    DLookup("CompanyName", "tblCustomers", 
        "CustomerID = " & [CustomerID]) AS CustomerName,
    DCount("*", "tblOrderDetails", 
        "OrderID = " & [OrderID]) AS ItemCount
FROM tblOrders;

Every row in the result set fires two additional queries, one DLookup and one DCount. For a table with 10,000 orders, that's 20,000 extra database round trips.

C# (EF Core — all in one query):

csharp
 var orders = await _context.Orders
    .Select(o => new
    {
        o.OrderId,
        o.OrderDate,
        o.CustomerId,
        CustomerName = o.Customer.CompanyName,        // navigation property = JOIN
        ItemCount = o.OrderDetails.Count()             // subquery, not separate call
    })
    .ToListAsync();

EF Core generates a single SQL statement with a JOIN and a subquery. The performance difference is not incremental. It's orders of magnitude. This is one of the strongest arguments for modernization that you can demonstrate with real numbers from a customer's actual database.

Calculated Fields in Queries

Access queries support calculated columns using VBA functions inline:

Access SQL:

SQL
 SELECT OrderID,
    Quantity * UnitPrice AS LineTotal,
    IIf(Quantity > 100, "Bulk", "Standard") AS OrderType,
    Format(OrderDate, "mmmm yyyy") AS OrderMonth,
    DateDiff("d", OrderDate, Date()) AS DaysSinceOrder
FROM tblOrderDetails;

C# (EF Core):

 var items = await _context.OrderDetails
    .Select(od => new
    {
        od.OrderId,
        LineTotal = od.Quantity * od.UnitPrice,
        OrderType = od.Quantity > 100 ? "Bulk" : "Standard",
        OrderMonth = od.OrderDate.ToString("MMMM yyyy"),
        DaysSinceOrder = EF.Functions.DateDiffDay(od.OrderDate, DateTime.Today)
    })
    .ToListAsync();

Most calculated fields translate directly. The gotchas:

IIf() → ternary operator ? : — straightforward, but nested IIf chains (which Access developers love) should be refactored into switch expressions or helper methods for readability.
Format() → .ToString() — format strings are different. Access uses "mmmm" for full month name; C# uses "MMMM".
DateDiff() → EF.Functions.DateDiffDay() — EF Core translates this to SQL Server's DATEDIFF. Using (DateTime.Today - od.OrderDate).Days may not translate to server-side SQL.
Nz() → ?? — the null-coalescing operator. Nz(ShipDate, "Pending") becomes ShipDate?.ToString() ?? "Pending".

The Queries That Don't Exist in Your Navigation Pane

The hardest part of migrating Access queries isn't the saved queries in the navigation pane. It's the SQL strings embedded everywhere else:

Form RecordSource properties — set at design time or dynamically in VBA
Combo box and list box RowSource properties — inline SQL that populates dropdowns
Report RecordSource properties — often with parameter references to open forms
VBA code — CurrentDb.OpenRecordset("SELECT...") scattered through modules

VELO's Scout Agent inventories all of these, not just the saved queries. A typical Access application with 50 saved queries might have another 200+ SQL statements embedded in forms, reports, and VBA modules. Scoping your migration based only on the navigation pane will undercount the effort by 50-75%.

What to Do Next

If you're planning an Access modernization, start by understanding the full scope of your query landscape including saved queries and embedded SQL alike. Our free assessment tool, ByteInsight, scans your .accdb file and produces a complete inventory of every query, every embedded SQL statement, and every domain function call, with complexity metrics to estimate migration effort.

For the queries themselves, the translation patterns in this guide cover the core mechanics. But the real work isn't translating individual queries. It's re-architecting the implicit runtime behaviors that Access provides for free (editable datasheets, parameter prompts, confirmation dialogs, auto-refresh) into explicit Blazor component logic. That's the architectural work where VELO's agents earn their keep.

Start with a free code assessment → ByteInsight

Watch VELO migrate a live Access app to Blazor on our upcoming livestream with Microsoft's Jeff Fritz. Register here →

View full post