Support for querying objects without keys
mikary opened this issue · 62 comments
Note: The feature tracked in this issue could help with using EF Core with database views. However, the feature is not limited to database views and its completion would not mean that every aspect of database view support has been implemented. See #827 for an overview of the areas where EF Core interacts with database views.
While the FromSql() method on DbSet<TEntity> can already be used to bootstrap raw queries which through standard LINQ composition end up projecting arbitrary types (i.e. types that are not mapped in the model), the method requires those queries to be rooted on a mapped type TEntity.
E.g. assuming Product is an entity type and ProductListEntry is just an arbitrary CLR type that is not mapped in the mode, this works:
var data = db.Set<Product>()
.FromSql("SELECT * FROM Product WHERE 1=1")
.Select(t => new ProductListEntry{Id = t.Id, Name = t.Name})
.ToList();But this doesn't:
var data = db.Set<ProductListEntry>()
.FromSql("SELECT Id, Name FROM Product WHERE 1=1")
.ToList();This item was used initially to track the ability to produce results from raw queries which cannot be expressed as a transformation over a known TEntity and hence cannot be rooted on a DbSet<TEntity>.
In the end we decided to enable mapping "query types", latter renamed to "entities without keys" in the model, which allowed us to support a large portion of the important scenarios this was about. We are now using #10753 to track working with other non-scalar types without having to add them first to the model.
(made some edits to the original issue to clarify exactly what this is and isn't about)
I'm not sure where to add this, but a bulk of people utilized Inversion Of Control with Entity Framework. So wouldn't we want to be able to do:
public class EfContext : DbContext, IDbCommand
{
public TEntity ExecuteRawSql(string query, params SqlParameter[] parameters) where TEntity : class, IEntity
{
return this.Database.SqlQuery<TEntity>(query, parameters).ToList();
}
}
In EF6 you supported the above, I know it currently doesn't. SqlParameter is object Also FromSql appears to be tightly coupled to DbSet.
@GArrigotti this feature is exactly for the scenario you describe. Being able to pass in SqlParameter/DbParameter is tracked by #3115.
I love Ef7 and was wondering if you would reconsider adding the Raw Store Access of arbitrary types to the initial release
It’s hard to imagine any complex app not needing this ability, every singly MVC app that I created in the past had many read-only view models that had subset of column selections from the entities; currently the workaround is to add them to the DbSet and give them fake keys if no keys are available
This clutters the DbSets with unnecessary entries that don’t belong there, I can’t imagine I am the only one running into this issue for any relatively complex apps
My preference would be to leave FromSql() as is and do something like DbContext.QueryRawSql() as mikary suggested
Hello,
Totally agree with @mikary and @Vasimovic.
I've found a solution for this while reading the source code.
I think it can be used until this issue is solved:
Add this class to your project.
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using SirenTek.Areas.TechDemo.Areas.SirenTransferTests.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
namespace Microsoft.EntityFrameworkCore
{
public static class RDFacadeExtensions
{
public static RelationalDataReader ExecuteSqlQuery(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
{
var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = databaseFacade
.GetService<IRawSqlCommandBuilder>()
.Build(sql, parameters);
return rawSqlCommand
.RelationalCommand
.ExecuteReader(
databaseFacade.GetService<IRelationalConnection>(),
parameterValues: rawSqlCommand.ParameterValues);
}
}
public static async Task<RelationalDataReader> ExecuteSqlCommandAsync(this DatabaseFacade databaseFacade,
string sql,
CancellationToken cancellationToken = default(CancellationToken),
params object[] parameters)
{
var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = databaseFacade
.GetService<IRawSqlCommandBuilder>()
.Build(sql, parameters);
return await rawSqlCommand
.RelationalCommand
.ExecuteReaderAsync(
databaseFacade.GetService<IRelationalConnection>(),
parameterValues: rawSqlCommand.ParameterValues,
cancellationToken: cancellationToken);
}
}
}
}
Usage:
// Execute a query.
var dr= await db.Database.ExecuteSqlQueryAsync("SELECT \"ID\", \"Credits\", \"LoginDate\", (select count(DISTINCT \"MapID\") from \"SampleBase\") as \"MapCount\" " +
"FROM \"SamplePlayer\" " +
"WHERE " +
"\"Name\" IN ('Electro', 'Nitro')");
// Output rows.
while (dr.Read())
Console.Write("{0}\t{1}\t{2}\t{3} \n", dr[0], dr[1], dr[2], dr[3]);
// Don't forget to dispose the DataReader!
dr.Dispose();
You may use your own query. It works for me..
Currently planned for 1.1 - see https://blogs.msdn.microsoft.com/dotnet/2016/07/29/entity-framework-core-1-1-plans/ for details.
Will anonymous type projections be allowed? They are so incredibly handy.
I'm super curious about one other thing- will EF Core support LINQ projections with C# 7 tuples? I don't see an issue on it.
@jnm2 At the top-level, our LINQ projections are always client-eval'd and so most simple cases will "just work".
Just wanted to add to @sirentek's solution. If you're lazy like me and don't mind using reflection, I created another extension (for non-async...easy to make it async though). This assumes that the model has data annotations. This is only really necessary if your property name and the return column have a different casing (or a removal of an _ in my case). It handles the case when the property does not have a mutator, and the case when the value is System.DBNull.Value, and also the case when the sql value is this System.DBNull.Value, but the property is not a reference type nor a nullable type. What I have done is basically inherit the main entity and add the fields missing from the query/SP return. In my case I have a lot of columns coming from 1 SP, and this way I can just add only the missing fields that I need, instead of creating a property for each one. Hope it helps someone until the team adds better support for raw sql/SPs.
public static IEnumerable<T> GetModelFromQuery<T>(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
where T : new()
{
using(DbDataReader dr = databaseFacade.ExecuteSqlQuery(sql, parameters).DbDataReader)
{
List<T> lst = new List<T>();
PropertyInfo[] props = typeof(T).GetProperties();
while(dr.Read())
{
T t = new T();
IEnumerable<string> actualNames = dr.GetColumnSchema().Select(o => o.ColumnName);
for(int i = 0; i < props.Length; ++i)
{
PropertyInfo pi = props[i];
if(!pi.CanWrite) continue;
System.ComponentModel.DataAnnotations.Schema.ColumnAttribute ca = pi.GetCustomAttribute(typeof(System.ComponentModel.DataAnnotations.Schema.ColumnAttribute)) as System.ComponentModel.DataAnnotations.Schema.ColumnAttribute;
string name = ca?.Name ?? pi.Name;
if(pi == null) continue;
if(!actualNames.Contains(name))
{
continue;
}
object value = dr[name];
Type pt = pi.DeclaringType;
bool nullable = pt.GetTypeInfo().IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>);
if(value == DBNull.Value)
{
value = null;
}
if(value == null && pt.GetTypeInfo().IsValueType && !nullable)
{
value = Activator.CreateInstance(pt);
}
pi.SetValue(t, value);
}//for i
lst.Add(t);
}//while
return let;
}//using dr
}
@sirentek How is this RDFacadeExtensions.ExecuteSqlQueryAsync different from the existing RelationalDatabaseFacadeExtensions.ExecuteSqlQueryAsync ? (or did the latter not exist at the time?)
@ztl8702 The main difference is the return type. RDFacadeExtensions.ExecuteSqlQueryAsync method returns an instance of RelationalDataReader. You may use RelationalDataReader to read the rows from database.
RelationalDatabaseFacadeExtensions.ExecuteSqlQueryAsync method returns Task<int> which is a number that shows the number of affected rows. You don't have row data.
Possibly a little off topic, but does anyone have any advice for stubbing results from a RelationalDataReader?
So I'm guessing this feature didn't make 1.1? So it's now planned for 1.2 or 2.0?
@rykr it seems to have been attached to the 2.0 milestone.
I have made this to call stored procedure and map the DataReader to a specified Model. I hope it helps.
@verdie-g Thanks for providing that. It's working for me.
Anyone know what release this will be in, comments here seem to suggest it's in the 2.0 milestone but can't see it. Any advice would be appreciated @rowanmiller @ajcvickers
@Ricky-G this is something we are still trying to get in 2.0, but the schedule is thight and there is a high risk that we will need to punt it to the next minor release after 2.0.
May I ask, what is the current status on this functionality? It is much needed, and I am hopeful it will be available soon.
Unfortunately, we don't have time to do this for the 2.0 release. We are considering it a high priority for post 2.0.
Pls consider it for release 2.1.0
Hi,
I don't see this issue listed in the 2.1.0 road map (only in the critical section). Will it be implemented only in the next year (since 2.1.0 will be in the Q4 2017)?
Thanks in advance.
Here is an extension method that works with .NET CORE 2.0 to execute RAW SQL and map results to LIST of arbitrary types:
USAGE:
var theViewModel = new List<SomeObjectType>();
string theQuery = @"SELECT * FROM dbo.Something";
theViewModel = DataSQLHelper.ExecSQL<SomeObjectType>(theQuery,_context);
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
public static List<T> ExecSQL<T>(string query, myDBcontext context)
{
using (context)
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
List<T> list = new List<T>();
T obj = default(T);
while (result.Read())
{
obj = Activator.CreateInstance<T>();
foreach (PropertyInfo prop in obj.GetType().GetProperties())
{
if (!object.Equals(result[prop.Name], DBNull.Value))
{
prop.SetValue(obj, result[prop.Name], null);
}
}
list.Add(obj);
}
return list;
}
}
}
}Here's what I use to respect EF mapping configuration: #4675 (comment)
@tonybourdeaux Hi Tony, Thanks for your extension. Do you think you should Close Database Connection after you have got data?
I was using @sirentek's solution. But I am now (after upgrading to .net core 2) getting the following error:
System.MissingMethodException occurred
HResult=0x80131513
Message=Method not found: 'Microsoft.EntityFrameworkCore.Storage.RawSqlCommand Microsoft.EntityFrameworkCore.Storage.IRawSqlCommandBuilder.Build(System.String, System.Collections.Generic.IReadOnlyList`1<System.Object>)'.
Who knows what might have happened ?
EDIT: Turns out that I forgot to update the EF version from 1.1.1 to 2.0.0 in the project that had the code that threw the error. It works now
We are adding support for Query Types in 2.1, which take care of the ability to query types that are not entities. Created #10753 to track not requiring a configuration call in OnModelCreating.
entityframework core 2.1 resolove raw queries
1.add modelBuilder.Query<YourModel>() to OnModelCreating(ModelBuilder modelBuilder)
2.use db.Query<YourModel>().FromSql(rawSql) get data
Any update for this feature?
@hugepanda This feature was implemnted and shipped with EF Core 2.1.
In Core 2.1 you can do something like this:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<YourModel>();
}
and then define your SQL Procedure, like:
public async Task<List<YourModel>> GetYourModel(string value1, Nullable<decimal> value2)
{
SqlParameter value1Input = new SqlParameter("@Param1", value1?? (object)DBNull.Value);
SqlParameter value2Input = new SqlParameter("@Param2", value2?? (object)DBNull.Value);
List<YourModel> getYourModel = await this.Query<YourModel>().FromSql("STORED_PROCEDURE @Param1, @Param2", value1Input, value2Input).ToListAsync();
return getYourModel;
}
This way YourModel model will not be created in your DB.
Now in your controller/action you can call:
List<YourModel> gettingYourModel = _DbContext.GetYourModel(value1,value2).Result.ToListAsync();
I tried modelBuilder.Query<string>();, but when I execute await _context.Query<string>().FromSql(sql).ToListAsync(); I get System.InvalidOperationException: No suitable constructor found for entity type 'string'. The following parameters could not be bound to properties of the entity: 'value', 'value', 'startIndex', 'length', 'value', 'value', 'startIndex', 'length', 'value', 'value', 'startIndex', 'length', 'value', 'startIndex', 'length', 'enc', 'c', 'count', 'value'. at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConstructorBindingConvention.Apply(InternalModelBuilder modelBuilder) what am I missing?
@Paul-Dempsey You should use your DbSet instead string
await _context.Query<YourEntity>().FromSql(sql);
Thnak you!, #11624 showed something that worked (well close enough - the example neglected to open the connection). DbSet doesn't make sense because there isn't one.
Is there any way to not map the Query to a T but rather to an object?
@pantonis Generally, no, but depending what you mean there may be ways to make it work. I would suggest you open a new issue describing in more detail what you are trying to do and why.
Can someone explain why we have to assign an ad-hoc model we want to query by onto the database context? We have a lot of stored procedures in our code as well as ad-hoc raw sql that does not return single table entities back. Right now these are done in EF6 via ExecuteSql<T> calls but nothing seems appropriate in EF core. We cannot map the ad-hoc POCOs to the database context for several reasons:
- It's pretty useless ceremony, especially since we are not doing full code first (schema changes are done in the database with flyway, then mirror them manually in the CF entities)
- Our DbContext is massive already due to the number of tables we have right now and I really have no desire to clutter it up even more by adding in ad-hoc POCOs
- Since we have multiple deployable applications all connecting to the same database EF is in it's own project that does not have access to the POCOs that are related to the specific DAL project that contains the different POCOs. Therefore even if I wanted to manually wire them all up it would be impossible without reconfiguring our whole project structure (not going to happen, especially for this).
It appears that my options for using EF Core mean I have to also use a ton of dapper with it.
Can someone explain why we have to assign an ad-hoc model we want to query by onto the database context? We have a lot of stored procedures in our code as well as ad-hoc raw sql that does not return single table entities back. Right now these are done in EF6 via
ExecuteSql<T>calls but nothing seems appropriate in EF core. We cannot map the ad-hoc POCOs to the database context for several reasons:
We are facing the same issue; we have so many stored procedures and queries that return complex results that do not belong in a DbSet/DbQuery
In EF 6 this was trivial dc.Database.SqlQuery<T> seems like this is missing in EF Core even with DbQuery feature
See this article for how to do this. https://docs.microsoft.com/en-us/ef/core/modeling/query-types
@tonybourdeaux that article does not help as far as I can tell, as I explicitly said I didn't want to (nor can I) add all these ad-hoc query models to the db context.
Can someone explain why we have to assign an ad-hoc model we want to query by onto the database context?
In order to materialize type of T through EF, EF needs to figure out to construct the object from database result. EF does this through reflection and construct metadata but all this is currently happening during model building phase. By adding ad-hoc type to context you are allowing EF to do the discovery so that it can be used during the query. (Bonus point that query would be faster since, you don't need to do metadata discovery during runtime). Since currently model building can do this task for query, we added it through mapping.
On the other hand, it is desirable not to have all ad-hoc types mapped in the model especially when you have a lot of them. Hence we do have issue #10753 to avoid requiring to add ad-hoc types to context and discover them when they are used first time in the query.
Thanks for the explanation, that does make sense that it's caching related and at what time materialization methods are cached. Since #10753 seems to not have any release timeline I guess we have to call all of our stored procs using dapper if we want to move to EF Core.
Would QueryFirst be of any use here? POCO's generated at design time from the schema returned by 'ad hoc' sql. Should be an improvement over dapper? Disclaimer: I'm the author of QueryFirst.
VS Marketplace
Github
I was thinking about this, and it occurred to me if you used a generic DbContext subtype, you could 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);
}
}
}
Then you can use it like this, creating a one-query-use DbContext sharing your DbConnection, and @ajcvickers helped me figure out how to make this work for mapping to anonymous type, which is handy since you can define the return type inline:
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");
}
@davidbaxterbrowne that is a quite ingenious way to approach the problem!
I suspect it is possible to avoid binding to provider methods in the OnConfiguring method and still get the caching, but not sure right now what the code looks like. That would make the solution very generic.
@bbsimonbb From reading the examples and watching some of the video content, I think the QueryFirst approach is very interesting.
Given that the main point we haven't addressed yet with EF Core is the amount of ceremony required to root a query on a new type, I suspect some customer in this thread won't like the idea of writing all their SQL queries in a separate file either. Code generation that depends on Visual Studio would also limit things. But QueryFirst still sounds like a cool .NET data access tool to have under your belt, if you can stick to that.
It also seems it would provide value using it side-by-side with EF Core or Dapper, rather than as a replacement. Have you considered building it as an extension that integrates with them? For example, I can imagine borrowing configuration details from a DbContext to connect to the database and discover result schemas, similar to how our design-time tooling works.
Oh, I don't want to change the DbContext file, I always keep it read only. because Scaffold-DbContext often be ran, any changes will miss.
@davidbaxterbrowne super
Is there an update to this for 3.0?
I upgraded to 3.0. Using the solution at #1862 (comment), I am now getting the error "The best overload for the 'ExecuteReader' does not have parameter named 'parameterValues'
Any suggestions on a work around for this?
I upgraded to 3.0. Using the solution at #1862 (comment), I am now getting the error "The best overload for the 'ExecuteReader' does not have parameter named 'parameterValues'
Any suggestions on a work around for this?
It seems like you can just create a RelationalCommandParameterObject and use that in the ExecuteReader. It requires a DbContext and IDiagnosticsLogger but they are marked with a CanBeNullAttribute so I assume they are not
public static RelationalDataReader ExecuteSqlQuery(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
{
var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = databaseFacade
.GetService<IRawSqlCommandBuilder>()
.Build(sql, parameters);
var paramObject = new RelationalCommandParameterObject(databaseFacade.GetService<IRelationalConnection>(), rawSqlCommand.ParameterValues, null, null);
return rawSqlCommand
.RelationalCommand
.ExecuteReader(paramObject);
}
}
Note, I have not tested this running since I am in the middle of upgrading my application to 3.0, but it compiles at least...
Update version of @davidbaxterbrowne code for EF Core 3:
public static class SqlQueryExtensions
{
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();
}
}
private class ContextForQueryType<T> : DbContext where T : class
{
private readonly DbConnection connection;
public ContextForQueryType(DbConnection connection)
{
this.connection = connection;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<T>().HasNoKey();
base.OnModelCreating(modelBuilder);
}
}
}
What if use dapper for mapping executed stored procedure results
using var connection = context.GetDbConnection();
var result= connection.Query<StringQueryType>("sp_someStoredProcedure").ToList();
Code in davidbaxterbrowne comment
causes
A Command is already in progress
exception when NpgSql EF Core provider is used. It is posted in
and
https://stackoverflow.com/questions/66191043/how-to-use-npgsql-ef-provider-as-scoped-service
This is also an option: #10365 (comment)
But I prefer this solution: #1862 (comment)
RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync encapsulates calling IRelationalCommand.ExecuteNonQueryAsync.
Is there a compelling reason we shouldn't just write extension methods that call IRelationalCommand.ExecuteReader[Async] too?
Allowing direct access to a DbDataReader can be useful for other use cases too, like streaming blobs.
While executing the SP in the middle of transaction I'm getting the below error. Please help me
ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
@DNLMurthy Please open a new issue and attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.