Wednesday, August 23, 2023

EF Core 8.0 Preview: Raw SQL Queries for Unmapped Types

This is one of my favorite features in EF Core 8.0 (or EF 8). Actually, it's likely this is my all-time favorite in EF Core and I have been waiting so long for this one. 

With EF Core 8.0, now you can write SQL queries that return unmapped types. With EF Core 7.0, we are able to query ONLY scalar (non-entity) types.

Let's have a look at this feature with an example code.

Consider the following Entities and the DbContext.

public class MyDbContext : DbContext
    public DbSet<Customer> Customers { getset; }

    public DbSet<Order> Orders { getset; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

public class Customer(string name)
    public int Id { getinit; }

    public string Name { getinit; } = name;

public class Order
    public int Id { getinit; }

    public Customer Customer { getset; }

    public decimal Amount { getset; }

Now let's get some data seeded.

using var context = new MyDbContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

Customer customer = new("John Doe");
await context.Customers.AddAsync(customer);

List<Order> orders = new()
    new() { Customer = customer, Amount = 200 },
    new() { Customer = customer, Amount = 300 }
await context.Orders.AddRangeAsync(orders);

await context.SaveChangesAsync();

With EF Core 7.0, we were able to do something like below.

IQueryable<intorderIds = context.Database
        FROM Orders

foreach (int orderId in orderIds)

decimal sumOfAmounts = context.Database
            SUM(Amount) AS Value
        FROM Orders


Note: Here in RelationalDatabaseFacadeExtensions.SqlQuery<TResult> Method<TResult> needs to be a scalar type. 

Now let's move to EF Core 8.0. With EF Core 8.0, we no longer have the restriction where <TResult> needs to be a scalar type. 

I am declaring a Custom DTO, something like the one below.

public record OrderDto(int Idstring CustomerNamedecimal Amount);

And now I can write a query to map data to the above type.

IQueryable<OrderDto> orderDetails = context.Database
            c.Name AS CustomerName,
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.Id

foreach (OrderDto orderDto in orderDetails)

Isn't it just great?

Hope this helps.

Happy Coding.


No comments:

Post a Comment