How do I execute a raw SQL query to a custom object in Entity Framework Core 3.1, without migrations wanting to create a table?

Question

I'm querying a Store table to show the user the 10 closest Stores. I'd like to display the Name and Distance of the Store, but prefer to keep distance in a custom entity.

Store fields: Id, Name, Latitude, Longitude, etc
StoreDto fields: Id,Name,Distance`

This SO answer gets us on the right track, particularly with the comments. However, DbQuery is now deprecated.

The docs on Keyless Entity Types say we can use a Keyless Entity Type to serve as the return type for raw SQL queries.

My DbContext already has:

public DbSet<Store> Stores { get; set; }

Adding

public DbSet<StoreDto> StoreDtos { get; set; }

And

modelBuilder.Entity<QuestSiteDto>()
    .HasNoKey()
    .ToView(null); // Hack to prevent table generation

Allows my store search code to work. But the next time I run a migration, EF Core wants to create a StoreDto table, unless I add that ugly ToView(null) hack.

For reference, here is my query:

var sql = 
@"select 
    geography::Point({0}, {1}, 4326).STDistance(geography::Point(Latitude, Longitude, 4326)) / 1609.34 as Distance,
    Id,
    [Name]
from
    Store"

var results = await StoreDtos
    .FromSqlRaw(sql, latitudeUnsafe, longitudeUnsafe)
    .OrderBy(x => x.Distance)
    .Take(10)
    .ToListAsync();

What is the proper way to do this? If you believe you know the recommended way, can you please cite your source? As of the time of this posting, the Keyless Entity Types doc page focuses more on Views and Tables rather than raw queries (unless I missed something).

Answer 1

You can also query types not registered in your DbContext. The idea is to introduce introduce a separate single-entity DbContext type for each ad-hoc query type. Each would be initialized and cached seperately.

So just add an extension method like this:

   public static class SqlQueryExtensions
    {
        public static IList<T> SqlQuery<T>(this DbContext db, Func<T> targetType, string sql, params object[] parameters) where T : class
        {
            return SqlQuery<T>(db, sql, parameters);
        }
        public static IList<T> SqlQuery<T>(this DbContext db, string sql, params object[] parameters) where T : class
        {

            using (var db2 = new ContextForQueryType<T>(db.Database.GetDbConnection()))
            {
                return db2.Query<T>().FromSql(sql, parameters).ToList();
            }
        }


        class ContextForQueryType<T> : DbContext where T : class
        {
            DbConnection con;

            public ContextForQueryType(DbConnection con)
            {
                this.con = con;
            }
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                //switch on the connection type name to enable support multiple providers
                //var name = con.GetType().Name;

                optionsBuilder.UseSqlServer(con);

                base.OnConfiguring(optionsBuilder);
            }
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                var t = modelBuilder.Query<T>();

                //to support anonymous types, configure entity properties for read-only properties
                foreach (var prop in typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public ))
                {
                    if (!prop.CustomAttributes.Any(a => a.AttributeType == typeof(NotMappedAttribute)))
                    {
                        t.Property(prop.Name);
                    }
                    
                }
                base.OnModelCreating(modelBuilder);
            }
        }

    }

Or for EF Core 5:

public static class SqlQueryExtensions
{
    public static IList<T> SqlQuery<T>(this DbContext db, Func<T> targetType, string sql, params object[] parameters) where T : class
    {
        return SqlQuery<T>(db, sql, parameters);
    }
    public static IList<T> SqlQuery<T>(this DbContext db, string sql, params object[] parameters) where T : class
    {

        using (var db2 = new ContextForQueryType<T>(db.Database.GetDbConnection()))
        {
            return db2.Set<T>().FromSqlRaw(sql, parameters).ToList();
        }
    }


    class ContextForQueryType<T> : DbContext where T : class
    {
        DbConnection con;

        public ContextForQueryType(DbConnection con)
        {
            this.con = con;
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //switch on the connection type name to enable support multiple providers
            //var name = con.GetType().Name;

            optionsBuilder.UseSqlServer(con);

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

            //to support anonymous types, configure entity properties for read-only properties
            foreach (var prop in typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public))
            {
                if (!prop.CustomAttributes.Any(a => a.AttributeType == typeof(NotMappedAttribute)))
                {
                    t.Property(prop.Name);
                }

            }
            base.OnModelCreating(modelBuilder);
        }
    }

}

Use would look like:

using (var db = new Db())
{
    var results = db.SqlQuery<ArbitraryType>("select 1 id, 'joe' name");
    //or with an anonymous type like this
    var results2 = db.SqlQuery(() => new { id =1, name=""},"select 1 id, 'joe' name");
}

This originally appeared here, but github issue comment threads aren't very discoverable: https://github.com/dotnet/efcore/issues/1862#issuecomment-451671168

Answer 2

To create the equivalent of DbQuery in ef core 3.x you add HasNoKey() and ToView() to your Entity in your modelcreating. This will prevent Migrations from creating a table.

public DbSet<Store> Stores { get; set; }
public DbSet<StoreDto> StoreDtos { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<StoreDtos>(sd =>
    {
        sd.HasNoKey().ToView(null);
    });
}