EF Core – Basic SELECT queries

In this article, I’ll show examples of how to execute basic SELECT queries when using EF Core. You can execute queries using LINQ or by writing raw SQL. I’ll use SQL Profiler to show the queries generated by LINQ.

Note: I’ll be using .AsNoTracking().ToListAsync() in all cases. You’ll need to decide if that’s the right thing to do in your specific situation.

Movies table sample data

I’ll be executing queries against the Movies table. This table contains the following data:

Id	Name	YearOfRelease	Description	Director	BoxOfficeRevenue
1	John Wick	2014	A revenge-seeking assassin goes after EVERYONE	Chad Stahelski	$86 million
3	Office Space	1999	A relatable comedy about a programmer who hates work	Mike Judge	$12.2 million
8	Mad Max: Fury Road	2015	A car chase through the desert with guns, exploding spears, and the most metal guitarist ever	George Miller	$375.4 million

Read more about how to create tables with EF Core.

Executing a raw SQL query

When you use LINQ to query, it auto-generates a SQL query for you. Usually this is good enough. But sometimes you’ll want the option of executing raw SQL. Perhaps you don’t agree with the auto-generated query, or using LINQ for a particular scenario would be way too complicated. In any case, it’s always a good idea to understand what SQL query is being executed.

To execute a raw SQL query, you can use FromSqlRaw(), like this:

using (var context = new StreamingServiceContext(connectionString))
{
	var allMovies = await context.Movies
		.FromSqlRaw("SELECT * FROM Movies")
		.AsNoTracking()
		.ToListAsync();
}
Code language: C# (cs)

If you’re passing parameters, use FromSqlInterpolated() instead, like this:

using (var context = new StreamingServiceContext(connectionString))
{
	var movies = await context.Movies
		.FromSqlInterpolated($"SELECT * FROM Movies WHERE YearOfRelease={yearOfRelease}")
		.AsNoTracking()
		.ToListAsync();
}
Code language: C# (cs)

This converts values into DbParameters, which parameterizes the query, and protects it against SQL Injection. If you were using pure ADO.NET, this is equivalent to calling sqlCommand.Parameters.AddWithValue(“@yearOfRelease”, yearOfRelease).

SELECT *

LINQ

Select all records from the table using LINQ:

using (var context = new StreamingServiceContext(connectionString))
{
	var allMovies = await context.Movies.AsNoTracking().ToListAsync();

	foreach(var movie in allMovies)
	{
		Console.WriteLine(movie.Name);
	}
}
Code language: C# (cs)

According to SQL Profiler, this generated the following query:

SELECT [m].[Id], [m].[BoxOfficeRevenue], [m].[Description], [m].[Director], [m].[Name], [m].[YearOfRelease]
FROM [Movies] AS [m]
Code language: SQL (Structured Query Language) (sql)

Running this results in it writing all of the movie names to the console:

John Wick
Office Space
Mad Max: Fury RoadCode language: plaintext (plaintext)

Raw SQL

Select all records from a table using raw SQL:

using (var context = new StreamingServiceContext(connectionString))
{
	var allMovies = await context.Movies
		.FromSqlRaw("SELECT * FROM Movies")
		.AsNoTracking()
		.ToListAsync();

	foreach(var movie in allMovies)
	{
		Console.WriteLine(movie.Name);
	}
}
Code language: C# (cs)

Running this results in it writing all of the movie names to the console:

John Wick
Office Space
Mad Max: Fury RoadCode language: plaintext (plaintext)

SELECT * WHERE

LINQ

Select all movies that were released in 2014:

using (var context = new StreamingServiceContext(connectionString))
{
	var movies = await context.Movies
		.Where(m => m.YearOfRelease == 2014)
		.AsNoTracking()
		.ToListAsync();
		
	foreach(var movie in movies)
	{
		Console.WriteLine(movie.Name);
	}
}
Code language: C# (cs)

According to SQL Profiler, here is the generated query:

SELECT [m].[Id], [m].[BoxOfficeRevenue], [m].[Description], [m].[Director], [m].[Name], [m].[YearOfRelease]
FROM [Movies] AS [m]
WHERE [m].[YearOfRelease] = 2014
Code language: SQL (Structured Query Language) (sql)

Running this outputs a single movie name in the console:

John WickCode language: plaintext (plaintext)

Raw SQL

Execute a raw SQL query with a WHERE clause, use FromSqlInterpolated() like this:

int yearOfRelease = 2014;

using (var context = new StreamingServiceContext(connectionString))
{
	var movies = await context.Movies
		.FromSqlInterpolated($"SELECT * FROM Movies WHERE YearOfRelease={yearOfRelease}")
		.AsNoTracking()
		.ToListAsync();


	foreach (var movie in movies)
	{
		Console.WriteLine(movie.Name);
	}
}
Code language: C# (cs)

When you need to pass a parameter in, use FromSqlInterpolated() instead of FromSqlRaw(). This allows you to use string interpolation, which is better than using format placeholders.

Running this outputs a single movie name in the console:

John WickCode language: plaintext (plaintext)

SELECT * WHERE LIKE

LINQ

Select all movies that have a description with the word “programmer” in it:

using (var context = new StreamingServiceContext(connectionString))
{
	var movies = await context.Movies
		.Where(m => m.Description.Contains("programmer"))
		.AsNoTracking()
		.ToListAsync();
		
	foreach (var movie in movies)
	{
		Console.WriteLine(movie.Name);
	}
}
Code language: C# (cs)

Here is the generated query according to SQL Profiler:

SELECT [m].[Id], [m].[BoxOfficeRevenue], [m].[Description], [m].[Director], [m].[Name], [m].[YearOfRelease]
FROM [Movies] AS [m]
WHERE [m].[Description] LIKE N'%programmer%'
Code language: SQL (Structured Query Language) (sql)

Only one movie matched the condition of having “programmer” in the description:

Office SpaceCode language: plaintext (plaintext)

Raw SQL

To execute a query with WHERE LIKE, use FromSqlInterpolated() like this:

using (var context = new StreamingServiceContext(connectionString))
{
	var descriptionLike = "%programmer%";
	var movies = await context.Movies
		.FromSqlInterpolated($"SELECT * FROM Movies WHERE Description LIKE {descriptionLike}")
		.AsNoTracking()
		.ToListAsync();
		
	foreach (var movie in movies)
	{
		Console.WriteLine(movie.Name);
	}
}
Code language: C# (cs)

This wrote out the name of the one movie that matched:

Office SpaceCode language: plaintext (plaintext)

SELECT TOP N + ORDER BY

LINQ

Select the top 2 movies based on their box office revenue:

using (var context = new StreamingServiceContext(connectionString))
{
	var movies = await context.Movies
		.OrderByDescending(m => m.BoxOfficeRevenue)
		.Take(2)
		.AsNoTracking()
		.ToListAsync();
		
	foreach (var movie in movies)
	{
		Console.WriteLine($"{movie.Name} made ${movie.BoxOfficeRevenue/1_000_000}M");
	}
}
Code language: C# (cs)

According to SQL Profiler, this generated this parameterized query:

exec sp_executesql N'SELECT TOP(@__p_0) [m].[Id], [m].[BoxOfficeRevenue], [m].[Description], [m].[Director], [m].[Name], [m].[YearOfRelease]
FROM [Movies] AS [m]
ORDER BY [m].[BoxOfficeRevenue] DESC',N'@__p_0 int',@__p_0=2
Code language: SQL (Structured Query Language) (sql)

This returned the top 2 movies and how much they earned:

Mad Max: Fury Road made $375.40M
John Wick made $86.00MCode language: plaintext (plaintext)

Raw SQL

To execute a query with SELECT TOP + ORDER BY with raw SQL:

using (var context = new StreamingServiceContext(connectionString))
{
	var movies = await context.Movies
		.FromSqlRaw($"SELECT TOP 2 * FROM Movies ORDER BY BoxOfficeRevenue DESC")
		.AsNoTracking()
		.ToListAsync();


	foreach (var movie in movies)
	{
		Console.WriteLine($"{movie.Name} made ${movie.BoxOfficeRevenue/1_000_000}M");
	}
}
Code language: C# (cs)

This output the top 2 movies:

Mad Max: Fury Road made $375.40M
John Wick made $86.00MCode language: plaintext (plaintext)

SELECT a subset of columns

Let’s say you’ve created an index with columns (Name, YearOfRelease), and you want to take advantage of the performance benefits of having this index by only including Name and YearOfRelease in your query.

LINQ

To select a subset of columns, use Select() with an anonymous type containing the columns you want:

using (var context = new StreamingServiceContext(connectionString))
{
	var movies = await context.Movies
		.Where(m => m.YearOfRelease >= 2014)
		.Select(m => new { m.Name, m.YearOfRelease})
		.AsNoTracking()
		.ToListAsync();

	foreach (var movie in movies)
	{
		Console.WriteLine($"{movie.Name} was made in {movie.YearOfRelease}");
	}
}
Code language: C# (cs)

This generated the following SQL query according to SQL Profiler:

SELECT [m].[Name], [m].[YearOfRelease]
FROM [Movies] AS [m]
WHERE [m].[YearOfRelease] >= 2014
Code language: SQL (Structured Query Language) (sql)

This wrote the following matching movies to the console:

John Wick was made in 2014
Mad Max: Fury Road was made in 2015Code language: plaintext (plaintext)

Take a look at the execution plan by executing the generated query with SHOWPLAN_TEXT ON:

SET SHOWPLAN_TEXT ON;  
GO  
SELECT [m].[Name], [m].[YearOfRelease]
FROM [Movies] AS [m]
WHERE [m].[YearOfRelease] >= 2014
Code language: SQL (Structured Query Language) (sql)

Notice that it’s using the (Name, YearOfRelease) index, as intended:

  |--Index Scan(OBJECT:([StreamingService].[dbo].[Movies].[IX_Movies_Name_YearOfRelease] AS [m]),  WHERE:([StreamingService].[dbo].[Movies].[YearOfRelease] as [m].[YearOfRelease]>=CONVERT_IMPLICIT(int,[@1],0)))Code language: plaintext (plaintext)

Raw SQL

To select a subset of columns with raw SQL, use FromSqlInterpolated() and Select() with an anonymous type, like this:

using (var context = new StreamingServiceContext(connectionString))
{
	var year = 2014;
	var movies = await context.Movies
		.FromSqlInterpolated($"SELECT Name, YearOfRelease FROM Movies WHERE YearOfRelease >= {year}")
		.Select(m => new { m.Name, m.YearOfRelease })
		.AsNoTracking()
		.ToListAsync();

	foreach (var movie in movies)
	{
		Console.WriteLine($"{movie.Name} was made in ${movie.YearOfRelease}");
	}
}
Code language: C# (cs)

If you don’t add the Select(), you get the following error:

System.InvalidOperationException: The required column ‘Id’ was not present in the results of a ‘FromSql’ operation.

This is because EF Core is trying to map the results to a full Movie model object. This is why you have to add the Select(), so that it maps the results to the anonymous type instead:

.Select(m => new { m.Name, m.YearOfRelease })
Code language: C# (cs)

According to SQL Profiler, the following strange query is generated:

exec sp_executesql N'SELECT [m].[Name], [m].[YearOfRelease]
FROM (
    SELECT Name, YearOfRelease FROM Movies WHERE YearOfRelease >= @p0
) AS [m]',N'@p0 int',@p0=2014
Code language: SQL (Structured Query Language) (sql)

EF Core is executing the raw SQL query as a subquery. This makes no difference in the execution plan though, and it does use the (Name, YearOfRelease) index – it just looks strange.