EF Core - Basic SELECT Queries

Note: All examples will use .AsNoTracking().ToListAsync() for optimized performance. Be sure to evaluate if this is suitable for your needs in each scenario.

Books Table Sample Data

The Books table contains the following sample data:

Executing a Raw SQL Query

EF Core automatically generates SQL queries when using LINQ. However, sometimes raw SQL is necessary for more complex queries or improved performance. Here's how to execute raw SQL queries with FromSqlRaw().

Raw SQL Example - SELECT *

using (var context = new BookStoreContext(connectionString))
{
    var allBooks = await context.Books
        .FromSqlRaw("SELECT * FROM Books")
        .AsNoTracking()
        .ToListAsync();
}

LINQ Example - SELECT *

using (var context = new BookStoreContext(connectionString))
{
    var allBooks = await context.Books.AsNoTracking().ToListAsync();
    foreach (var book in allBooks)
    {
        Console.WriteLine(book.Title);
    }
}

The SQL Profiler shows the generated query:

SELECT [b].[Id], [b].[Author], [b].[Genre], [b].[Price], [b].[Title], [b].[YearOfPublication]
FROM [Books] AS [b]

SELECT * WHERE

LINQ Example - Books Published in 1960

using (var context = new BookStoreContext(connectionString))
{
    var books = await context.Books
        .Where(b => b.YearOfPublication == 1960)
        .AsNoTracking()
        .ToListAsync();
}

SQL Profiler:

SELECT [b].[Id], [b].[Author], [b].[Genre], [b].[Price], [b].[Title], [b].[YearOfPublication]
FROM [Books] AS [b]
WHERE [b].[YearOfPublication] = 1960

Raw SQL Example - Books Published in 1960

int yearOfPublication = 1960;
using (var context = new BookStoreContext(connectionString))
{
    var books = await context.Books
        .FromSqlInterpolated($"SELECT * FROM Books WHERE YearOfPublication={yearOfPublication}")
        .AsNoTracking()
        .ToListAsync();
}

SELECT * WHERE LIKE

LINQ Example - Books with 'Dystopian' Genre

using (var context = new BookStoreContext(connectionString))
{
    var books = await context.Books
        .Where(b => b.Genre.Contains("Dystopian"))
        .AsNoTracking()
        .ToListAsync();
}

SQL Profiler:

SELECT [b].[Id], [b].[Author], [b].[Genre], [b].[Price], [b].[Title], [b].[YearOfPublication]
FROM [Books] AS [b]
WHERE [b].[Genre] LIKE N'%Dystopian%'

Raw SQL Example - Books with 'Dystopian' Genre

using (var context = new BookStoreContext(connectionString))
{
    var genreLike = "%Dystopian%";
    var books = await context.Books
        .FromSqlInterpolated($"SELECT * FROM Books WHERE Genre LIKE {genreLike}")
        .AsNoTracking()
        .ToListAsync();
}

SELECT TOP N + ORDER BY

LINQ Example - Top 3 Most Expensive Books

using (var context = new BookStoreContext(connectionString))
{
    var books = await context.Books
        .OrderByDescending(b => b.Price)
        .Take(3)
        .AsNoTracking()
        .ToListAsync();
}

SQL Profiler:

exec sp_executesql N'SELECT TOP(@__p_0) [b].[Id], [b].[Author], [b].[Genre], [b].[Price], [b].[Title], [b].[YearOfPublication]
FROM [Books] AS [b]
ORDER BY [b].[Price] DESC',N'@__p_0 int',@__p_0=3

Raw SQL Example - Top 3 Most Expensive Books

using (var context = new BookStoreContext(connectionString))
{
    var books = await context.Books
        .FromSqlRaw($"SELECT TOP 3 * FROM Books ORDER BY Price DESC")
        .AsNoTracking()
        .ToListAsync();
}

SELECT a Subset of Columns

LINQ Example - Select Only Title and Price

using (var context = new BookStoreContext(connectionString))
{
    var books = await context.Books
        .Where(b => b.YearOfPublication >= 1950)
        .Select(b => new { b.Title, b.Price })
        .AsNoTracking()
        .ToListAsync();
}

SQL Profiler:

SELECT [b].[Title], [b].[Price]
FROM [Books] AS [b]
WHERE [b].[YearOfPublication] >= 1950

Raw SQL Example - Select Only Title and Price

using (var context = new BookStoreContext(connectionString))
{
    var year = 1950;
    var books = await context.Books
        .FromSqlInterpolated($"SELECT Title, Price FROM Books WHERE YearOfPublication >= {year}")
        .Select(b => new { b.Title, b.Price })
        .AsNoTracking()
        .ToListAsync();
}