EF Core and Dapper provide an abstraction level over our data source. Nevertheless, both of them have some pros and cons. It is essential to bear in mind that, for every project we are working on, we should seek to find the right tool for the job.

Let's analyze some pros and cons for these two libraries. Before that, we should take a quick look at some demo queries in order to understand the differences between the two. The following snippet describes a sample query using EF Core:

using (var context = new CatalogContext()) {
     var items = context.Items         
                        .Where(b => b.Description.Contains("various 
                         artists"))
                        .ToList();
}

In the preceding code, we search for every Item entity with a corresponding description. Let's proceed by taking a Dapper query as an example:

connection.Query<Item>("select * from (select Id from dbo.Catalog where Description like '%@searchTerm%', new { searchTerm = "various artists" });

As you can see, EF Core provides a high level of abstraction over our data source.

Moreover, EF Core, by default, allows developers to query data using collections (integrated with LINQ). This type of approach is quick and easy, but it comes at a cost: EF Core translates queries into the SQL language, and, sometimes, it produces SQL queries that are not optimized. EF Core also encourages a code-first approach over the database, which means that all the entities that are present on the database side are generated using C# code. This may seem easy when you have a single object, but it can present maintainability problems when you have complex entities.

A common problem with EF Core, though, is the early fetching of resources. For example, consider a query such as this:

 List<Item> items = db.Items.ToList();
 List<Item> variousArtistItems = items
                     .Where(s => s.Description.Contains("various 
                          artist") == city).ToList();

It produces a SQL query similar to the following:

SELECT [i].[Id],
       [i].[Description],
       [i].[ArtistId], 
       [i].[GenreId],
FROM   [dbo].[Items] as [i]

As you can see, despite the fact we use the Where clause, earlier the ToList method evaluated the query without considering the Where clause. To get a better result, we should execute the Where statement before the evaluation:

 List<Item> variousArtistItems = db.Items
                     .Where(s => s.Description.Contains("various 
                          artist") == city).ToList();

This kind of procedure is better both for performance and network reasons. It may seem simple enough, but it is quite common that these kinds of error are introduced in the codebase in distributed teams, and they can be quite tricky to spot in the code review process.

In terms of Dapper, which is a micro-ORM, the abstraction level changes. Dapper provides more transparent access to the data source. It guarantees, by default, a clear way to query your data by using plain SQL or stored procedures. Consequently, it also ensures better performance. On the other hand, it is tightly coupled with the data source, because it executes queries using the data source query language. 

In summary, this chapter will cover both EF Core and Dapper libraries. To choose between them, you should consider the skills already present in your team and how the service can be performance-optimized. If your team has a strong knowledge of SQL, you may proceed by implementing stored procedures instead of using EF Core. On the other hand, if your team doesn't already have SQL skills, you should consider using EF Core.