Skip to content
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

Support for querying objects without keys #1862

Closed
mikary opened this issue Mar 19, 2015 · 59 comments
Closed

Support for querying objects without keys #1862

mikary opened this issue Mar 19, 2015 · 59 comments

Comments

@mikary
Copy link
Contributor

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

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

@divega divega commented Mar 21, 2015

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

@GArrigotti
Copy link

@GArrigotti 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
Copy link
Contributor

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

@Vasim-DigitalNexus
Copy link

@Vasim-DigitalNexus Vasim-DigitalNexus 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
Copy link

@sirentek 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
Copy link
Contributor

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

@KallDrexx
Copy link

@KallDrexx 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
Copy link

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

@davidbaxterbrowne
Copy link

@davidbaxterbrowne davidbaxterbrowne commented Jan 5, 2019

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");
        }
@divega
Copy link

@divega divega commented Jan 5, 2019

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

@divega
Copy link

@divega divega commented Jan 5, 2019

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

@yeahe83
Copy link

@yeahe83 yeahe83 commented Feb 22, 2019

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.

@xrkolovos
Copy link

@xrkolovos xrkolovos commented Jun 10, 2019

@divega divega changed the title Raw store access APIs: Support for ad hoc mapping of arbitrary types Support for querying objects without keys Jun 24, 2019
@rmorgan0076
Copy link

@rmorgan0076 rmorgan0076 commented Oct 1, 2019

Is there an update to this for 3.0?

@rmorgan0076
Copy link

@rmorgan0076 rmorgan0076 commented Oct 1, 2019

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?

@Steve887
Copy link

@Steve887 Steve887 commented Oct 8, 2019

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

@ErikEJ
Copy link
Contributor

@ErikEJ ErikEJ commented Mar 10, 2020

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

@gayancc
Copy link

@gayancc gayancc commented Apr 24, 2020

What if use dapper for mapping executed stored procedure results

using var connection = context.GetDbConnection();
var result= connection.Query<StringQueryType>("sp_someStoredProcedure").ToList();
@kobruleht
Copy link

@kobruleht kobruleht commented Feb 14, 2021

Code in davidbaxterbrowne comment

#1862 (comment)

causes

A Command is already in progress

exception when NpgSql EF Core provider is used. It is posted in

npgsql/efcore.pg#1698

and

https://stackoverflow.com/questions/66191043/how-to-use-npgsql-ef-provider-as-scoped-service

@hidegh
Copy link

@hidegh hidegh commented Mar 26, 2021

This is also an option: #10365 (comment)
But I prefer this solution: #1862 (comment)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet