Practicing and exploring

Test your knowledge and understanding by answering some questions, getting some hands-on practice, and exploring this chapter’s topics with deeper research.

Exercise 2.1 – Test your knowledge

Answer the following questions:

1. Which NuGet package should you reference in a .NET project to get the best performance when working with data in SQL Server?
2. What is the safest way to define a database connection string for SQL Server?
3. What must T-SQL parameters and variables be prefixed with?
4. What must you do before reading an output parameter of a command executed using ExecuteReader?
5. What can the dotnet-ef tool be used for?
6. What type would you use for the property that represents a table, for example, the Products property of a data context?
7. What type would you use for the property that represents a one-to-many relationship, for example, the Products property of a Category entity?
8. What is the EF Core convention for primary keys?
9. Why might you choose the Fluent API in preference to annotation attributes?
10. Why might you implement the IMaterializationInterceptor interface in an entity type?

Exercise 2.2 – Practice benchmarking ADO.NET against EF Core

In the Chapter02 solution/workspace, create a console app named Ch02Ex02_ADONETvsEFCore that uses Benchmark.NET to compare retrieving all the products from the Northwind database using ADO.NET (SqlClient) and using EF Core.

Exercise 2.3 – Explore topics

Use the links on the following page to learn more details about the topics covered in this chapter:

https://github.com/markjprice/apps-services-net7/blob/main/book-links.md#chapter-2---managing-relational-data-using-sql-server

Exercise 2.4 – Explore Dapper

Dapper is an alternative ORM to EF Core. It is more efficient because it extends the low-level ADO.NET IDbConnection interface with very basic functionality.

In the Northwind.Console.SqlClient project, add a package reference for Dapper, and then add a class to represent a supplier, as shown in the following code:

public class Supplier
{
  public int SupplierId { get; set; }
  public string? CompanyName { get; set; }
  public string? City { get; set; }
  public string? Country { get; set; }
}

In Program.cs, add statements to retrieve Supplier entities in Germany, as shown in the following code:

IEnumerable<Supplier> suppliers = connection.Query<Supplier>(
  sql: "SELECT * FROM Suppliers WHERE Country=@Country",
  param: new { Country = "Germany" });
foreach (Supplier supplier in suppliers)
{
  WriteLine("{0}: {1}, {2}, {3}",
    supplier.SupplierId, supplier.CompanyName,
    supplier.City, supplier.Country);
}

You can learn more about Dapper at the following link:

https://github.com/DapperLib/Dapper/blob/main/Readme.md