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);
    });
}

Answer 3

One solution I got to maintain all dtos in the database was to create a view with a dummy return type.

The view

CREATE OR REPLACE VIEW public.result_function_metrics
AS SELECT 0::double precision AS date_year,
    0::double precision AS date_month,
    0::bigint AS metric,

The function

CREATE OR REPLACE FUNCTION fx_reports_goals (
    _user_id int8, 
    _date_from timestamp with time zone, 
    _date_to timestamp with time ZONE
)
RETURNS SETOF result_function_metrics
AS 
$$
BEGIN

... 

END
$$
LANGUAGE plpgsql;

var query = @"SELECT * FROM fx_reports_goals(
            @pUserId,
            @pDateFrom,
            @pDateTo);";
      var parameters = new NpgsqlParameter[3];

parameters[0] = new NpgsqlParameter("pUserId", NpgsqlTypes.NpgsqlDbType.Bigint);
parameters[0].Value = filter.userID ?? (object)DBNull.Value;
--- 
// same for the rest of params
--- 

var results = await db.ResultFunctionMetric.FromSqlRaw(query, parameters).ToListAsync();

Notice that db contains the dbSet ResultFunctionMetric scaffolded from the empty view.

TL/DR It is easier to set return type from the function or stored procedure add a dummy view with the expected return values and as a bonus you still get that everytime you scaffold (database first approach)