Recently, I've come across the news that EF Core 8.0 has introduced a new feature for querying unmapped data. This sounds like an exciting development and brings to mind a challenge I faced last year. I had to query unmapped data using EF Core and, after some research, I was able to find a solution and successfully implement it.

In this article, I will be providing a detailed guide on how to query unmapped data using EF Core and comparing its performance to that of Dapper. I aim to make this article useful for developers who are looking to implement this feature but may not have access to EF Core 8.0 yet. Additionally, I will also show how to use this feature in earlier versions of EF Core. The goal of this article is to provide a comprehensive overview of querying unmapped data using both EF Core and Dapper, and help developers make an informed decision on which approach to use in their projects.

It's important to note that the method I will be demonstrating in this article is my own implementation and should not be confused with the new feature introduced in EF Core 8.0. As I have not had the opportunity to examine the inner workings of the new feature in EF Core 8.0, I am unable to make a direct comparison between my implementation and the official feature. However, I will be providing an overview of how to query unmapped data using EF Core and Dapper and comparisons between the performance, ease of use, and flexibility of both approaches which will give readers a general idea of how these implementation work.

Introduction

Entities Framework (EF) Core is an open-source Object-Relational Mapping (ORM) framework for .NET. It allows developers to interact with databases using C# object-oriented code, instead of writing raw SQL queries. EF Core supports a wide range of relational databases and provides a way to create, read, update, and delete (CRUD) operations through its DbContext class. It also supports advanced features such as lazy loading, eager loading, and change tracking.

Dapper is a simple, lightweight Object-Relational Mapping (ORM) framework for .NET. It is known for its high performance and low overhead, as it uses dynamic method generation at runtime to minimize the amount of code generated by the framework. Dapper also supports advanced features such as multi-mapping, and it supports both synchronous and asynchronous operations. Unlike EF, Dapper does not have a change tracking mechanism and does not support lazy loading and eager loading.

Both EF Core and Dapper are popular choices among .NET developers for data access. EF Core provides a more complete and feature-rich framework, while Dapper is more focused on performance and simplicity. EF Core is great for rapid development and prototyping, while Dapper is a good choice for high-performance scenarios.

Project structure

In this article, I will also be referencing a sample project that can be found in a provided repository. The sample project features two simple classes, "Student" And "Lesson", and a many-to-many relationship between them, represented by the "StudentLesson" class. The goal of this project is to demonstrate how to query the "StudentLesson" class using a new structure that is different from the base class. This example will serve as a practical demonstration of how to query unmapped data using EF Core and Dapper and help readers understand the concepts discussed in the article.

Let's begin by examining the "UnmappedStudentLesson" class, which is used to represent the desired structure of the query results. This class includes properties for the "StudentName", "LessonTitle", and "Date" of each student-lesson relationship. It serves as an example of how we can query data from multiple tables and map it to a new structure that is different from the base classes. This will be the structure that we will use to retrieve the data from the database using both EF Core and Dapper and compare the results.

namespace QueryUnmappedTypes.ViewModels
{
    public class UnmappedStudentLesson
    {
        public string StudentName { get; set; } = string.Empty;
        public string LessonTitle { get; set; } = string.Empty;
        public DateTime Date { get; set; }
    }
}

In the sample project, you can see how to query the unmapped data using EF Core by looking at lines 14 to 23 in the "Program.cs" file.

var mappedQuery = dbContext.StudentLessons.Select(x => new UnmappedStudentLesson
{
    Date = x.Date,
    LessonTitle = x.Lesson.Title,
    StudentName = $"{x.Student.Firstname} {x.Student.Lastname}"
});
foreach (var item in mappedQuery)
{
    Console.WriteLine(item.ToJson());
}

this is a way to query unmapped data using EF Core by using the LINQ Select statement as shown in the code snippet you provided, this will allow you to create a new instance of the "UnmappedStudentLesson" class and populate its properties with data from the "StudentLessons" table and related tables. This code uses the LINQ Select method to select the properties you want to query and maps them to the "UnmappedStudentLesson" class. The result of this query is an IEnumerable of "UnmappedStudentLesson" which you can iterate through using the foreach loop and print the result using Console.WriteLine(item.ToJson());

This approach is more readable and maintainable than the raw SQL query approach, it also allows you to use the full power of LINQ for querying and filtering data. You can do it in other ways.

The sample project also includes an example of querying unmapped data which can be found on lines 27 to 31 of the "Program.cs" file.

var unmappedQuery = await dbContext.FromSqlRawAsync<UnmappedStudentLesson>(@"SELECT        Students.Firstname+' '+ Students.Lastname as StudentName, Lessons.Title as LessonTitle, StudentLessons.Date
FROM            Lessons INNER JOIN
                         StudentLessons ON Lessons.Id = StudentLessons.LessonId INNER JOIN
                         Students ON StudentLessons.StudentId = Students.Id");

This code uses the "FromSqlRawAsync" extension method, which is a custom method that I have implemented to query the database using EFCore and map the results to the "UnmappedStudentLesson" class. This method allows you to execute a raw SQL query and return the results as a list of "UnmappedStudentLesson" objects.

It's important to note that the "FromSqlRawAsync" method is not a built-in method of EFCore, but rather a custom method implemented by the developer.

To understand how the "FromSqlRawAsync" method is implemented, you can take a look at the "DbContextSqlQueryExtension" class in the sample project. This class contains the implementation of the custom method, which uses EFCore to execute a raw SQL query and map the results to the "UnmappedStudentLesson" class.

using System.Data.Common;
using System.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;

namespace QueryUnmappedTypes.Extensions
{
    public static class DbContextSqlQueryExtension
    {
        private static readonly Dictionary<string, DbContext> _dbContexts = new();
        public static IList<T> FromSqlRaw<T>(this DbContext db, string sql, params object[] parameters) where T : class
        {
            if (!_dbContexts.ContainsKey(typeof(T).Name))
            {
                _dbContexts.Add(typeof(T).Name, new ContextForQueryType<T>(db.Database.GetDbConnection()));
            }
            var db2 = _dbContexts[typeof(T).Name];
            return db2.Set<T>().FromSqlRaw(sql, parameters).ToList();
        }

        public static IList<T> FromSqlRaw<T>(
        this DbContext db,
        string sql,
        IDictionary<string, object> parameters,
        Func<string, object, IDbDataParameter> getParameter) where T : class =>
            parameters == null ? throw new ArgumentNullException(nameof(parameters)) :
            getParameter == null ? throw new ArgumentNullException(nameof(getParameter)) :
            db.FromSqlRaw<T>(sql, parameters.Select(p => getParameter(p.Key, p.Value))
                    .Cast<object>()
                    .ToArray());

        public static async Task<IList<T>> FromSqlRawAsync<T>(this DbContext db, string sql, params object[] parameters) where T : class => await Task.FromResult(db.FromSqlRaw<T>(sql, parameters));
        public static async Task<IList<T>> FromSqlRawAsync<T>(this DbContext db, string sql, IDictionary<string, object> parameters, Func<string, object, IDbDataParameter> getParameter) where T : class => await Task.FromResult(db.FromSqlRaw<T>(sql, parameters, getParameter));

        private class ContextForQueryType<T> : DbContext where T : class
        {
            private readonly DbConnection _connnection;

            public ContextForQueryType(DbConnection connnection)
            {
                _connnection = connnection;
            }
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                switch (_connnection.GetType().Name)
                {
                    case nameof(SqlConnection):
                        optionsBuilder.UseSqlServer(_connnection);
                        break;
                    default:
                        throw new NotImplementedException();
                }

                base.OnConfiguring(optionsBuilder);
            }
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<T>(entity => { entity.HasNoKey(); });
                base.OnModelCreating(modelBuilder);
            }
        }

    }
}

The "DbContextSqlQueryExtension" class is a static class that contains several extension methods that allow you to query unmapped data using EF Core.

The FromSqlRaw<T> method is the main method, it takes in two parameters, the SQL in string and the parameters, it's a generic method that works with any type T that is a class.

The method first checks if the _dbContexts dictionary already contains a key of the T type, if not it creates a new DbContext that inherits from the original DbContext, but it's only used to create and query the T type of data, it also opens a new connection to the database using the "DbContext.Database.GetDbConnection()" method.

Then it uses the new DbContext to create a DbSet<T> and calls the FromSqlRaw method on it, passing the SQL and parameters, and finally, it returns the result as a list of T.

The other methods are similar but they are async methods, and they also provide a way to pass the parameters as a dictionary and a function to convert them to IDbDataParameter.

The ContextForQueryType<T> is an inner class that is used to create the new DbContext that works only with the Generic type, it overrides the OnConfiguring and OnModelCreating methods, the OnConfiguring method it configures the options builder to use the correct provider based on the connection type, and in the OnModelCreating method it sets the T type to have no key, this is important for unmapped data.

Overall, this class provides a way to use the FromSqlRaw method on DbContext and return the result as a list of T, it also provides async methods and a way to pass the parameters as a dictionary and a function to convert them to IDbDataParameter.

I understand that the "DbContextSqlQueryExtension" class provides a simple solution for querying unmapped data using EF Core, but it has some limitations.

By creating a new instance of the DbContext each time the method is called, it can negatively impact performance and lead to increased memory usage. This is due to the fact that each new instance of the DbContext will have its own state, and it will consume resources.

Additionally, creating a new instance of the DbContext each time the method is called can also create issues with caching and query optimization.

It's worth noting that this is a hacky solution and it's not recommended for production use. If you want to achieve better performance, you should consider a different approach, such as implementing a caching mechanism or using a more efficient method for querying unmapped data.

I implemented a better solution for a company, but as it's closed-source, I can't publish it. However, I suggest that you could share some general information about the improvements you made and how it works.

In any case, I hope the provided sample project and the explanation I've given will give readers an idea of how to query unmapped data

Benchmarking

In addition to providing a guide on how to query unmapped data using EF Core and Dapper, I also implemented a benchmark to test and compare the performance of both approaches. By running the benchmark, you will find that the performance of querying unmapped data using EF Core is slightly faster than Dapper, however, due to the additional object initialization, the memory allocation is higher than Dapper. While it is possible to reduce memory usage through various techniques, Dapper is known for its efficient memory usage. Despite this, the results overall are good and acceptable. The benchmark provides insight into the trade-offs of each approach, and it can help developers make an informed decision on which approach to use in their projects.

Conclusion

In conclusion, querying unmapped data can be a challenging task, but with the right tools and techniques, it can be done efficiently. EF Core and Dapper are two popular options for querying data in a C# application. EF Core provides a rich set of features for querying data, including a powerful LINQ provider, change tracking, and lazy loading. Dapper, on the other hand, is a lightweight and fast micro-orm, known for its low memory footprint and high performance.

In this article, we have discussed how to query unmapped data using EF Core and Dapper and provided a sample project that demonstrates the concepts discussed. We also examined the implementation of a custom extension method that allows you to query unmapped data using EF Core and map the results to a new class, providing similar functionality as the "FromSqlRaw" method in EF Core. Additionally, we have discussed the advantages and disadvantages of both approaches and benchmarked their performance and memory usage.

Overall, both EF Core and Dapper provide a way to query unmapped data, but each has its own strengths and weaknesses. It's important to consider the specific requirements of your project and the specific use case when choosing which approach to use.