EFCore: Raw Sql Query with custom types

Hi everyone!
It has been too long since I posted on this blog.

Here is a small article about a workaround I had to do using ASP.Net Core and its Entity Framework Core (EFCore).

A very useful method on Entity Framework is the raw sql query with a custom type, which goes pretty much like this:

var myCustomList = await context.Database.SqlQuery<CustomType>("SELECT col1, col2, … FROM MyTable").ToListAsync();

This allow you to use custom types to define quick out-of-the-box entities / query mapping, and unfortunately EFCore does not support this (at least right now).

I could not go without this super-useful method, so I made an extension to support it.

---

Basics

I chose to use AutoMapper for the mapping and AutoMapper.Data which supports implicit IDataReader mapping. (Edit: Note that Jeremy Sinclair wrote a custom method to map the entities that I did no see before, the link is down below).

We need a generic class for the logic (CustomTypeSqlQuery<T>) and an extension to mimic the behavior we like (DatabaseExtensions).

Please beware that only classes are supported, not basic types like string or int.

---

DatabaseExtensions

First the extension, in a file named DatabaseExtensions.cs :

public static class DatabaseExtensions
{
    public static CustomTypeSqlQuery<T> SqlQuery<T>(
    		this DatabaseFacade database, 
    		string sqlQuery) where T : class
    {
        return new CustomTypeSqlQuery<T>() 
        { 
        	DatabaseFacade = database, 
        	SQLQuery = sqlQuery 
        };
    }
}

This is an extension to DatabaseFacade which will allow us to to something like:

context.Database.SqlQuery<CustomType>("SELECT … FROM …")

and returns a CustomTypeSqlQuery<T>, which holds the logic.

---

CustomTypeSqlQuery

The full class goes like this:

public class CustomTypeSqlQuery<T> where T : class
{
  private IMapper _mapper;
  
  public DatabaseFacade DatabaseFacade { get; set; }
  public string SQLQuery { get; set; }

  public CustomTypeSqlQuery()
  {
    _mapper = new MapperConfiguration(cfg => {
        cfg.AddDataReaderMapping();
        cfg.CreateMap<IDataRecord, T>();
    }).CreateMapper();
  }

  public async Task<IList<T>> ToListAsync()
  {
    IList<T> results = new List<T>();
    var conn = DatabaseFacade.GetDbConnection();
    try
    {
      await conn.OpenAsync();
      using (var command = conn.CreateCommand())
      {
        command.CommandText = SQLQuery;
        DbDataReader reader = await command.ExecuteReaderAsync();

        if (reader.HasRows)
          results = _mapper.Map<IDataReader, IEnumerable<T>>(reader)
                           .ToList();
        reader.Dispose();
      }
    }
    finally
    {
        conn.Close();
    }
    return results;
  }

  public async Task<T> FirstOrDefaultAsync()
  {
    T result = null;
    var conn = DatabaseFacade.GetDbConnection();
    try
    {
      await conn.OpenAsync();
      using (var command = conn.CreateCommand())
      {
        command.CommandText = SQLQuery;
        DbDataReader reader = await command.ExecuteReaderAsync();

        if (reader.HasRows)
        {
          var results = _mapper.Map<IDataReader, IEnumerable<T>>(reader)
          					 .ToList();
          result = results.FirstOrDefault();
        }
        reader.Dispose();
      }
    }
    finally
    {
        conn.Close();
    }
    return result;
  }
}

As you can see, I create the mapping configuration on the go, which allows me not to declare the mapping. cfg.AddDataReaderMapping() is the method that allows me to map IDataReader directly.

Then I added two methods : ToListAsync() and FirstOrDefaultAsync(). It basically uses a connection and a DbDataReader, which is what Microsoft recommends for raw SQL Queries.

Please note that FirstOrDefaultAsync() is absolutely barbaric inefficient. A better solution would be to parse the results one by one (await reader.ReadAsync()) and return as soon as we have one result. I did this is just for the example.

---

This allows us to use the extension and the class like this:

var myList = await context.Database.SqlQuery<CustomType>("SELECT … FROM …")
								   .ToListAsync();

« Et voilà ! »

Almost as easy to use as in Entity Framework 6. I am missing support for basic types, it should not be really difficult to do but I do not need it right now. Feel free to contact me if you want your own appendix.

If you want it on github I can do that too.

See you soon!

PS: Jeremy Sinclair did a pretty nice job writing a method to parse from DbDataReader to a class without AutoMapper here