New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Raw store access APIs: Support for ad hoc mapping of arbitrary types #1862

Closed
mikary opened this Issue Mar 19, 2015 · 46 comments

Comments

Projects
None yet
@mikary
Contributor

mikary commented Mar 19, 2015

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 is about 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>.

Note that we wouldn't necessary make DbSet<TEntity> work for arbitrary types but we could use something different to bootstrap queries base on ad-hoc mapping, e.g. DbContext.Query().

@divega divega changed the title from Ad-hock materialization from raw queries to Raw data access APIs: Support for ad hoc mapping of arbitrary types Mar 21, 2015

@divega divega changed the title from Raw data access APIs: Support for ad hoc mapping of arbitrary types to Raw store access APIs: Support for ad hoc mapping of arbitrary types Mar 21, 2015

@divega

This comment has been minimized.

Member

divega commented Mar 21, 2015

(made some edits to the original issue to clarify exactly what this is and isn't about)

@GArrigotti

This comment has been minimized.

GArrigotti commented Feb 6, 2016

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.

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented Feb 9, 2016

@GArrigotti this feature is exactly for the scenario you describe. Being able to pass in SqlParameter/DbParameter is tracked by #3115.

@Vasimovic

This comment has been minimized.

Vasimovic commented Mar 17, 2016

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

@sirentek

This comment has been minimized.

sirentek commented May 21, 2016

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..

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented Jul 29, 2016

Currently planned for 1.1 - see https://blogs.msdn.microsoft.com/dotnet/2016/07/29/entity-framework-core-1-1-plans/ for details.

@xie-sheng

This comment has been minimized.

xie-sheng commented Mar 14, 2018

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

@hugepanda

This comment has been minimized.

hugepanda commented Jun 18, 2018

Any update for this feature?

@ajcvickers

This comment has been minimized.

Member

ajcvickers commented Jun 18, 2018

@hugepanda This feature was implemnted and shipped with EF Core 2.1.

@RodrigoFCampos

This comment has been minimized.

RodrigoFCampos commented Jun 19, 2018

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

@Paul-Dempsey

This comment has been minimized.

Paul-Dempsey commented Sep 11, 2018

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?

@behzad888

This comment has been minimized.

behzad888 commented Sep 12, 2018

@Paul-Dempsey You should use your DbSet instead string

await _context.Query<YourEntity>().FromSql(sql);
@smitpatel

This comment has been minimized.

Contributor

smitpatel commented Sep 12, 2018

@Paul-Dempsey

This comment has been minimized.

Paul-Dempsey commented Sep 12, 2018

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.

@pantonis

This comment has been minimized.

pantonis commented Oct 24, 2018

Is there any way to not map the Query to a T but rather to an object?

@ajcvickers

This comment has been minimized.

Member

ajcvickers commented Oct 24, 2018

@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.

@KallDrexx

This comment has been minimized.

KallDrexx commented Oct 31, 2018

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:

  1. 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)
  2. 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
  3. 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.

@Vasimovic

This comment has been minimized.

Vasimovic commented Oct 31, 2018

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

@tonybourdeaux

This comment has been minimized.

tonybourdeaux commented Oct 31, 2018

See this article for how to do this. https://docs.microsoft.com/en-us/ef/core/modeling/query-types

@KallDrexx

This comment has been minimized.

KallDrexx commented Oct 31, 2018

@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.

@smitpatel

This comment has been minimized.

Contributor

smitpatel commented Oct 31, 2018

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.

@KallDrexx

This comment has been minimized.

KallDrexx commented Oct 31, 2018

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.

@bbsimonbb

This comment has been minimized.

bbsimonbb commented Nov 26, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment