Developer bit

Raw SQL Queries for Unmapped Types in Entity Framework 8

Entity Framework 8 has a new feature that allows you to execute raw SQL queries against the database and return results as unmapped types. To use this...

By Tim Deschryver Published August 15, 2023 #dotnet · #entity-framework

Raw SQL Queries for Unmapped Types in Entity Framework 8

Entity Framework 8 has a new feature that allows you to execute raw SQL queries against the database and return results as unmapped types. To use this feature, use the new SqlQuery method on the Database property of your DbContext instance.

This feature is useful when you want your query to return a specific type for a specific purpose. For example, in many cases you don't need/want the overhead of returning your full-blown entity for search queries. Instead, you want a optimized entity (e.g. a DTO) that only contains the data you need for that specific purpose. Usually this results in a faster query and less data transferred over the wire.

See my blog post You can now return unmapped types from raw SQL select statements with Entity Framework 8 for more info about this new feature.


 Select Query to retrieve a collection 
var customers  = await dbContext.Database
    // 👇 Map to a unmapped type
    .SqlQuery<CustomerDto>(
        $"""
            SELECT
                c.Id as CustomerId,
                c.FirstName,
                a.Street
            FROM dbo.Customers c
            JOIN dbo.Addresses a ON c.Id = a.CustomerId
            """
    )
    .ToListAsync();


 Select Query to retrieve a collection 
SELECT
       c.Id as CustomerId,
       c.FirstName,
       a.Street
FROM dbo.Customers c
JOIN dbo.Addresses a ON c.Id = a.CustomerId


 Select Query with a parameter within the where clause 
var customers = await dbContext.Database
    .SqlQuery<CustomerDto>(
        $"""
            SELECT
                c.Id as CustomerId,
                c.FirstName,
                a.Street
            FROM dbo.Customers c
            JOIN dbo.Addresses a ON c.Id = a.CustomerId
            --                             👇 Use parameters in your query
            WHERE c.FirstName like '%' + {customerName} + '%'
            """
    )
    .ToListAsync();


 Select Query with a parameter within the where clause 
exec sp_executesql N'SELECT
       c.Id as CustomerId,
       c.FirstName,
       a.Street
FROM dbo.Customers c
JOIN dbo.Addresses a ON c.Id = a.CustomerId
--                             👇 Parameterized
WHERE c.FirstName like ''%'' + @p0 + ''%''
',N'@p0 nvarchar(4000)',@p0=N'ali'


 Select Query using LINQ to retrieve a single entity 
var customer = await dbContext.Database
    .SqlQuery<CustomerDto>(
        $"""
            SELECT
                c.Id as CustomerId,
                c.FirstName,
                a.Street
            FROM dbo.Customers c
            JOIN dbo.Addresses a ON c.Id = a.CustomerId
            """
    )
    // 👇 SqlQuery returns a IQueryable<TResult> so you can use LINQ as well
    .SingleOrDefaultAsync(c => c.CustomerId == customerId);


 Select Query using LINQ to retrieve a single entity 
exec sp_executesql N'SELECT TOP(2) [c].[CustomerId], [c].[FirstName], [c].[Street]
FROM (
    SELECT
        c.Id as CustomerId,
        c.FirstName,
        a.Street
    FROM dbo.Customers c
    JOIN dbo.Addresses a ON c.Id = a.CustomerId
) AS [c]
--                       👇 LINQ filters also are parameterized
WHERE [c].[CustomerId] = @__customerId_1',N'@__customerId_1 int',@__customerId_1=1

Feel free to update this developer bit on GitHub, thanks in advance!

Support my work

Share this bit