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 { get; set; }
public DbSet<Order> Orders { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"<ConnectionString>");;
}
}
public class Customer(string name)
{
public int Id { get; init; }
public string Name { get; init; } = name;
}
public class Order
{
public int Id { get; init; }
public Customer Customer { get; set; }
public decimal Amount { get; set; }
}
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<int> orderIds = context.Database
.SqlQuery<int>($"""
SELECT
Id
FROM Orders
""");
foreach (int orderId in orderIds)
{
Console.WriteLine(orderId);
}
decimal sumOfAmounts = context.Database
.SqlQuery<decimal>($"""
SELECT
SUM(Amount) AS Value
FROM Orders
""")
.Single();
Console.WriteLine(sumOfAmounts);
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 Id, string CustomerName, decimal Amount);
And now I can write a query to map data to the above type.
IQueryable<OrderDto> orderDetails = context.Database
.SqlQuery<OrderDto>($"""
SELECT
o.Id,
c.Name AS CustomerName,
o.Amount
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id
""");
foreach (OrderDto orderDto in orderDetails)
{
Console.WriteLine(orderDto);
}
Happy Coding.
Regards,
Jaliya
