concept dapper in category entity framework

appears as: Dapper
Entity Framework Core in Action

This is an excerpt from Manning's book Entity Framework Core in Action.

Listing 9.13 An example of using a low-level database access library with EF Core
var bookDtos = new List<RawSqlDto>();
var conn = context.Database.GetDbConnection();    #1  
try
{
    conn.Open();    #2  
    using (var command = conn.CreateCommand())  #3  
    {
        string query = "SELECT b.BookId, b.Title, " +    #4  
        "(SELECT AVG(CAST([NumStars] AS float)) " +    #4  
        "FROM dbo.Review AS r " +    #4  
            "WHERE b.BookId = r.BookId) AS AverageVotes " +    #4  
            "FROM Books b"; 
        command.CommandText = query;    #5  
        using (DbDataReader reader = command.ExecuteReader())    #7  
        {
            while (reader.Read())    #7  
            {
                var row = new RawSqlDto
                {
                    BookId = reader.GetInt32(0),    #8  
                    Title = reader.GetString(1),    #8  
                    AverageVotes = reader.IsDBNull(2)    #8  
                       ? null    #8  
                       : (double?) reader.GetDouble(2)    #8  
                };
                bookDtos.Add(row);
            }
        }
    }
}
finally 
{
    conn.Close();    #9  
}

#1   Asks EF Core for a DbConnection, which the low-level SqlClient library can use
#2   You need to open the connection before you use it.
#3   Creates a DbCommand on that connection
#4   The ADO.NET library transfers SQL directly to the database server; hence all the database accesses must be defined in SQL.
#5   Assigns your command to the DbCommand instance
#7   The ExecuteReader method sends the SQL command to the database server and then creates a reader to read the data that the server will return.
#7   This tries to read the next row and returns true if it was successful.
#8   You have to hand-code the conversion and copying of the data from the reader into your class. Have a look at Dapper for a slightly easier way to read data into a class.
#9   When the read has finished, you need to close the connection to the database server.

13.3.1 Introducing Dapper

Dapper (https://github.com/StackExchange/Dapper) is a NuGet package, available on .NET and .NET Core, that executes SQL code and then copies the results back to .NET classes. It uses ADO.NET underneath, so it can work with any databases that ADO.NET supports, such as SQL Server, SQLite, Oracle, MySQL, PostgreSQL, and others. It’s well-known and has been downloaded over a million times.

Getting Dapper to work with EF Core is easy, because Dapper matches database columns to the names of properties in the same way as EF Core’s By Convention configuration. Also, EF Core can provide the correct type of database connection that Dapper needs via its Database.GetDbConnection method. Here’s an example of using Dapper to read all the rows in the Books table into a collection of Book entity classes. The Dapper part is in bold.

Listing 13.4 Using Dapper with EF Core to read the Books table
var books = context.Database    #1  
    .GetDbConnection()    #2  
    .Query<Book>("SELECT * FROM Books");    #3  

#1   Use the application’s DbContext to run the Dapper query.
#2   You need to get a DbConnection for EF Core, as that’s what Dapper needs to access the database.
#3   The Dapper call executes the SQL code provided in the first parameter, and Dapper then maps the results to the class type you supplied, in this case, Book.


!@%STYLE%@!
{"css":"{\"css\": \"font-weight: bold;\"}","target":"[[{\"line\":2,\"ch\":5},{\"line\":2,\"ch\":39}]]"}
!@%STYLE%@!

Although you could do the same thing with EF Core’s FromSql method (see section 9.5.1), the advantage of Dapper is that it’ll work with any class, whereas FromSql maps only to an entity class. You can use Dapper with your BooksListDto class, but you can’t use FromSql to do that.