Entity Framework Core has many useful features, and in this article, we will see how to use it to show a generated SQL query.
EF Core is an open source Object-relational mapper that supports several databases like SQLite, PostgreSQL, MySQL, and also LINQ (Language Integrated Query). One key benefit to developers is the ability to explore the feature to view or extract the raw SQL content used in applications.
Without further ado, let’s learn about this together.
Use ToQueryString() Method
Firstly, we cannot overemphasize the importance of logs in an application. It is tasking to implement logs in different aspects of an application. The first thing to remember is that logging in EF Core is easy to set up with minimal configuration.Â
The ToQueryString() method is a part of an extension of Entity Framework Core and it returns a query string representation. It’s a great method for debugging.Â
Let’s demonstrate this by starting with setting up our project.
Environment Set up to Show the Generated SQL Query
First, let’s set up an EF Core application.
We’ll begin with creating our model object class that represents Car:
public class Car
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public string? Brand { get; set; }
public string? Model { get; set; }
public int Year { get; set; }
public string? Name { get; set; }
}
Here we have the Car class that holds properties of a car.Â
We won’t go into details about creating DbContext, running migrations, and seeding data into the database in this article because our focus is on showing generated SQL queries in EF Core. For more information on these topics, check out our article on migrations and seeding data into database.Â
To be able to get data from the database, let’s create a repository class:
public class CarRepository(ApiContext context) : ICarRepository
{
public List<Car> GetCars()
{
var cars = context.Cars;
Console.WriteLine(cars.ToQueryString());
return cars.ToList();
}
}
We start by invoking our Cars method from the context of our implemented GetCars() method. We then return the result as a list and use the ToQueryString() method to write our generated SQL query to the console.
At last, let’s register a new endpoint in our Program class:
app.MapGet("/cars", (ICarRepository carRepository) =>
{
return carRepository.GetCars();
});
In the MapGet() method, we add the API route /cars. Afterwards, we invoke the GetCars() method of our ICarRepository interface.
Having established a connection from the model to the database and an endpoint that reads from it, we have now reached a checkpoint.
Testing Using the Text Visualizer of Visual Studio IDE
It is a common practice for developers to place breakpoints at different parts of a code to debug an issue.Â
To see this in action, let’s run our application and call our /cars endpoint:
[
{
"id": 1,
"brand": "BMW",
"model": "1 Series",
"year": 2012,
"name": "118d HatchBack"
},
{
"id": 2,
"brand": "BMW",
"model": "1 Series",
"year": 2013,
"name": "118d M Sport 3 Door HatchBack"
},
{
"id": 3,
"brand": "BMW",
"model": "2 Series",
"year": 2015,
"name": "228i Sport Convertible"
},
{
"id": 4,
"brand": "BMW",
"model": "3 Series",
"year": 2016,
"name": "328i HatchBack"
}
]
When we execute our endpoint without the breakpoints, we see that we get our seeded data as expected.
Now, let’s add breakpoints, and monitor our debugger. Thereafter, we verify that our debugger hit our breakpoint, in the Text Visualizer:
We see our SQL query in the preceding image by expanding the Query field.
Alternatively, we can use another approach which is the VS output window in the IDE to view the logs of our generated SQL query:
SELECT [c].[Id], [c].[Brand], [c].[Model], [c].[Name], [c].[Year] FROM [Cars] AS [c]
Use Microsoft.EntityFrameworkCore.Database Logging
Entity Framework Core features an important class Microsoft.EntityFrameworkCore.Database.Command, which serves as a command executed against the database.
Internally, EF Core utilizes this command for running SQL queries and other database commands. To enable SQL logging, we can include this command in our app settings. However, it’s crucial to note that this command is enabled by default.
Moreover, this implies that when we create a new application, there’s no need to configure anything, as the logs will be automatically written to the console of our application.
By default, we receive information-level logs for the Microsoft.EntyFrameworkCore.Database.Command namespace when it is added to the appsettings.json file:
"Microsoft.EntityFrameworkCore.Database.Command": "Information"
Meanwhile, let’s modify our GetCars() method and remove the Console.WriteLine() method:
public List<Car> GetCars()
{
var cars = _context.Cars;
return cars.ToList();
}
Adding the command namespace to the appsettings.json file ensures that we will still see a logged result in our console.
Finally, let’s repeat our steps on testing the /cars endpoint for verification and checking our VS output:Â
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [c].[Id], [c].[Brand], [c].[Model], [c].[Name], [c].[Year]
FROM [Cars] AS [c]
Microsoft.EntityFrameworkCore.Database.Command: Information:
Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [c].[Id], [c].[Brand], [c].[Model], [c].[Name], [c].[Year]
FROM [Cars] AS [c]
We notice that our generated SQL content is visible in the VS output window.Â
Conclusion
In this article, we have demonstrated ways to show generated SQL queries from our application using Entity Framework Core. At first, we utilized the ToQueryString() method provided by Entity Framework Core. Subsequently, we leveraged the built-in logging capabilities of Entity Framework Core to avoid the need to directly call the ToQueryString() method.