SQL and EF Core Queries With AI Help

Queries are a great fit for AI: the rules are precise, and a good query can save hours of manual work. But AI does not know your schema, and a LINQ query that looks fine can produce slow or surprising SQL. This lesson shows how to get both right.

Give it the schema

The AI cannot write a correct query against tables it has never seen. Paste the relevant entity classes or table definitions:

SQL Server 2022. Tables (only the relevant columns):
Orders(Id int PK, CustomerId int FK, CreatedAt datetime2, Total decimal(18,2), Status tinyint)
Customers(Id int PK, Name nvarchar(200), City nvarchar(100))
Write a query for the top 10 customers by total order value in the last
90 days, with their order count, newest first on ties. Explain any index
that would help.

For EF Core, paste the entity classes and the DbSet names instead, and say which EF Core version you use.

Check the SQL that EF Core generates

A LINQ query is only as good as the SQL it becomes. EF Core can show you:

var query = db.Orders
    .Where(o => o.CreatedAt >= since)
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.Total) });

Console.WriteLine(query.ToQueryString());

Or log all SQL during development:

options.UseSqlServer(connectionString)
       .LogTo(Console.WriteLine, LogLevel.Information);

Paste the generated SQL back into the chat:

This is the SQL EF Core generated for my LINQ query. Is it efficient?
Would anything here cause a table scan or load more data than needed?

The N+1 problem

The most common EF Core performance bug: loading a list, then running one extra query per item.

var orders = await db.Orders.ToListAsync();
foreach (var o in orders)
    Console.WriteLine(o.Customer.Name);   // with lazy loading: 1 query per order

Ask the AI to spot it, and to suggest a fix such as a projection:

var rows = await db.Orders
    .Select(o => new { o.Id, o.Total, CustomerName = o.Customer.Name })
    .ToListAsync();

Projections with Select load only the columns you need and usually produce one efficient query.

Other things to ask the AI to check

• AsNoTracking() for read-only queries, so EF Core does not track entities it will never save.
• Filtering in the database, not in memory. A ToList() too early pulls the whole table, and filtering happens in C#.
• Large Include chains that create huge joins. Consider split queries (AsSplitQuery()) or projections.
• Paging with Skip/Take always with an OrderBy, otherwise the order is not guaranteed.
• Indexes that match your Where and OrderBy columns. Ask: "What index supports this query?" and check the execution plan in SQL Server Management Studio.

Keep raw SQL safe

If you must use raw SQL in EF Core, use parameters. Prefer:

var city = "Pune";
var customers = await db.Customers
    .FromSql($"SELECT * FROM Customers WHERE City = {city}")
    .ToListAsync();

FromSql with an interpolated string turns {city} into a SQL parameter. Building SQL by string concatenation with user input, or using FromSqlRaw with concatenated values, opens the door to SQL injection. If an AI suggestion concatenates user input into SQL, reject it.

Migrations

AI can draft entity changes and explain a migration, but always read the generated migration before applying it. Watch for dropped columns or tables, data loss on type changes, and missing data migration steps. Never run a new migration against production without a backup and a tested rollback plan.

Practice task

Take one slow or complicated query from your project. Give the AI the schema and the LINQ, inspect the generated SQL with ToQueryString(), and ask for an improved version. Measure before and after with the SQL log or execution plan.

Key takeaways

• Always give the schema or entity classes and your database/EF Core version.
• Inspect the generated SQL with ToQueryString() or logging.
• Watch for N+1 queries, early ToList(), missing AsNoTracking and paging without OrderBy.
• Use parameterised raw SQL only, and review every migration before applying it.