Support for querying objects without keys

[mikary]

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]

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

[GArrigotti]

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]

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

[Vasim-DigitalNexus]

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]

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]

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

[divega]

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

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

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]

@davidbaxterbrowne super

[rmorgan0076]

Is there an update to this for 3.0?

[rmorgan0076]

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?

[AndriySvyryd]

https://docs.microsoft.com/en-us/ef/core/modeling/keyless-entity-types

[Steve887]

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]

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]

What if use dapper for mapping executed stored procedure results

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

[kobruleht]

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]

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

[lakeman]

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

[DNLMurthy]

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.

[ajcvickers]

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