Add ability to provide a raw custom generated query to the db

[TehWardy]

I've hit issues like this #6717 and there's no way I can work round the fact that EF is forcing "boxed" queries that it always wraps.

I'm sure EF (not Core) could do this, why would you take this out?

I can't see why I can't just be given the ability to do something like ...

var result = RawQuery<RowType>("SELECT x FROM y WHERE ...") 

... and have that absolute RAW blob of SQL given directly to the db without EF getting involved.

Not having this means i'm forced to work within the constraints of scenarios that the EF Core team has already thought of and handled which is often frustrating for devs out in the wild when I come to github and see tickets floating around for 4 or 5 years at a time and my issue in only a month or 2 old.

It's made worse by the fact that each new version of the framework is built against each new version of .Net Core forcing whole .Net versions on us to solve DB issues which then ultimately causes other unrelated stack issues.

For example:

It doesn't appear that I can take EF Core 3.x without taking whole chunks of .Net Core 3.x and my current stack runs on 2.x because other frameworks (e.g. OData) are not stable in the current .Net Core version or cause fallout that would take months to resolve.

So whilst Microsoft is insistent on this fast iteration lifecycle and constant breaking changes between versions I would like to hold back a bit with this "simple" catch all for all the scenarios that you guys haven't covered.

[ajcvickers]

@TehWardy Can you explain more about what you mean by "boxed"?

[ErikEJ]

@TehWardy Are you referring to something like this? #1862 (comment)

[TehWardy]

Sort of Eric, but that's the bit of functionality in EF core i'm looking to avoid.

With the current implementation of EF Core, if i find myself in a situation where I just need to do a one off "complex query" that I want to build manually because maybe it uses something in the db that isn't part of my model / would be complex to have EF implement through a LINQ query I have to do something like this ...

class GuidValue
{
     public Guid Value { get; set; }
}

protected virtual DbQuery<GuidValue> GuidList { get; set; }

public IEnumerable<Guid> DoSomethingCustom(string someValue)
{
      return GuidList.FromSql("some custom sql", someValue);
}

What this actually generates is something like this ...

exec sp_executesql N'SELECT [Value] FROM (<some custom SQL>)',N'@__arg_1 nvarchar(4000)',@__arg_1 =N'some value'

So my issue is twofold ...

Firstly I want to build a query where I can specify the SQL "OPTIONS" clause to a query as documented ....
https://docs.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver15

... based on the documentation I can NEVER use this with EF Core since it will always wrap my SQL code with its own SELECT resulting in my OPTIONS usage not being at that root of the query.

in the example above the OPTIONS clause would be injected inside the braces generated by the framework code.

What I would like to do is something like ...

public IEnumerable<Guid> DoSomethingCustom(string someValue)
{
      return RawQuery<Guid>("some custom sql", someValue);
}

... and I have no issue with it being wrapped in the "exec sp_executesql" sproc call, but I want my RawSQL presented to the DB verbatim inside that call.

Also note, I don't need all that extra declaration of telling the context that it has a GuidList type or DbQuery, that's just bloat and serves no additional value to the situation.

Also assume I could give the generic anything that might even be an entity but it shouldn't need to be an entity, it just needs to be something that EF can map to.

With this in mind I can build a raw query even if it contains the OPTIONS clause and problems like this ...
#6717

... go away, as I have full control over what I want and it's clean code in my context.
I would also expect that something like this could be used as a filter during OnModelCreating like this ...

builder.Entity<SomeEntityType>().HasQueryFilter(i => RawQuery<bool>("some SQL", i.Id));

... I'm pretty sure (with the exception of the filter scenario) all of this was part of EF before EF Core came along.

[ajcvickers]

@TehWardy Most of what you are describing is covered by #11624.

[TehWardy]

I think you're right, further input from from what i read on that thread ....

why is this needed at all?

modelBuilder.Query();

... and this is wrapped in an EF generated parent select preventing me from using OPTIONS clauses

var manyGuids = context.Query().FromSql(sql, params).Select(r => r.Guid);

What's the reasoning behind Database.SqlQuery() being different to context.Query() and of course the need for both?

I'm after a truely raw query that would be derived from doing something like this ...

using (var ctx = new MyContext())
{
    using (var conn = ctx.Database.GetDbConnection())
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM some_table";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
                Console.WriteLine(reader.GetString(0));
        }
    }
}

... but with the ability to then do ...

var result = AsIEnumerable(reader);

assuming I have that same block of sql then I should also be able to do ...

class Mycontext : DbContext
{
        void ApplyFilters(ModelBuilder builder) 
        {
              builder.Entity<SomeT>().HasQueryFilter(b => Database.Where(rawSql, b.Id));
        }
}

... using the same mechanism.

In the former case I would expect ONLY my defined raw sql blob to be executed, in the latter I would expect EF to generate it's own SQL then embed my "custom" boolean clause in to the selection as a normal query filter.

It seems that I can either have ORM or I can write custom SQL, I can't have both.
I also have to tell EF about any non primitive type that I may ever want to map to so I can't ever do something like ...

var ctx = new MyContext(args);
ctx.Query<IEnumerable>(someSql);

without first defining inside the context itself that type T as a DbQuery property.

Since I as a DbModel designer can't tell how the business logic above will consume the Db it's hard for me to register all known Dto's or ad hoc type information that the business layer may want.

The suggestion seems to be to dapper the problem and do ...

using (var ctx = new MyContext())
{
    using (var conn = ctx.Database.GetDbConnection())
        return conn.Query<Guid>("SELECT something FROM somewhere");
}

which due to the design of EF would it not make sense to just support ctx.Query(sql) in the same way?

The sentiment in the other thread seems to be "if EF can't do it, pull in something else", but the argument against that stands as "but it could, with what appears to be a minor API change since the underlying framework can already do this, so where's the issue here?"

@roji states ...

AFAIK the term ORM usually (always?) applies to components which generate SQL for you, taking care of saving/loading/querying objects in your languages to relational database tables. In fact, unless I'm mistaken Dapper calls itself a "micro-ORM" precisely because it doesn't generate SQL but executes your own SQL

Which i'm not sure I agree with, the ORM should deal with taking whatever problem we give it and map that to an object graph (if the problem presented was a query result somehow), or generate the appropriate SQL then map that to an object graph.
Regardless of how the query is generated, i'm simply asking "can I generate the query not you in this case?".

EF goes a step further giving us modelling for a DB, and change tracking but suggestion seems to be that we use EF to manage the state of our DB (migrations ect), then something like Dapper to actually query it. Why can't EF do both?

In short I agree with the sentiment in @Eirenarch 's opening sentence there, I had a functionality piece that was crazy useful, and now I don't.

[smitpatel]

Duplicate of #10753 & #11624

As long as you don't compose over your raw sql query, EF Core preserves it as is. If you compose over it in LINQ, then to make SQL resemble LINQ query, we compose over it in database to causing a subquery.
e.g.

var manyGuids = context.Query().FromSql(sql, params).Select(r => r.Guid);

This is wrapped because there is additional select. In terms of data transfer, if your raw sql is returning 10 different columns and only one of them you need in your query then not composing in SQL side can cause huge perf degradation. So if you want to use the raw SQL intact then all the composition should be done inside raw sql itself.

[TehWardy]

Not quite, and here's why I don't see this as a duplicate ...

My actual scenario:

var manyTs = context.Query().FromSql<T>(sql, params);

... few things to note ...

1. This should be able to handle scalar values like Guids too.
2. The current design as I understand it requires me to tell the context (by declaring DbSets or DbQueries as properties) that these types are part of the model (not wanted).
3. It would be nice to use this in side HasQueryFilter where I would expect composition to take place.
4. Only using .FromSql(sql, params) with no further chain still has EF composing over it, not wanted.

I explicitly don't want EF to do anything more than run what I give it AS IS then return me a set of T's.

[TehWardy]

I've just noticed on ticket #10753 that @John0King wrote ...

from #17516 :

I'm suggestion add a new method that create IQuerable directly from sql, and the property map must use Sql to match model instead of use model to match sql so there no need to configurate the query model in the dbContext.

DbContext.FromSqlRaw(string sql)
DbContext.FromSqlRaw(string sql, T objectProjection> // some idea of dynamic/anonymous support , .FromSql("SELECT **", new { Name= "", Age = 0, BirthDay = (DateTime?)null })

And dapper is quoted often as having something like ...

Database.SqlQuery<T>(sql)

... which has more in common with my request here in terms of underlying framework behaviour.

EF 6 Also used to do this but for some reason this functionality hasn't made it's way in to EF Core.

As I see it EF has a bunch ofr core "features", I guess i'm asking to be able to choose between using some of them instead always being forced to use them all in some situations where you guys haven't thought of my scenario I can then therefore "compose my own scenario" by using the mapper but not the query generation features.

[smitpatel]

Only using .FromSql(sql, params) with no further chain still has EF composing over it, not wanted.

This is incorrect assertion. If the SQL is not composed over then EF sends raw query as is.

Test

efcore/test/EFCore.Relational.Specification.Tests/Query/FromSqlQueryTestBase.cs

Lines 149 to 159 in f8ac8b1

	[ConditionalFact]
	public virtual void FromSqlRaw_queryable_simple()
	{
	using var context = CreateContext();
	var actual = context.Set<Customer>()
	.FromSqlRaw(NormalizeDelimitersInRawString("SELECT * FROM [Customers] WHERE [ContactName] LIKE '%z%'"))
	.ToArray();
	Assert.Equal(14, actual.Length);
	Assert.Equal(14, context.ChangeTracker.Entries().Count());
	}

Generates following SQL

efcore/test/EFCore.SqlServer.FunctionalTests/Query/FromSqlQuerySqlServerTest.cs

Lines 22 to 28 in c0d18b1

	public override void FromSqlRaw_queryable_simple()
	{
	base.FromSqlRaw_queryable_simple();
	AssertSql(
	@"SELECT * FROM ""Customers"" WHERE ""ContactName"" LIKE '%z%'");
	}

[TehWardy]

Now do that without the set.
Lets say I have a DTO not in the model that I want to write an arbitrary SQL query for.

Also that won't generate (from what i've seen) the result you think.
Firstly it wraps it in a call to sp_executesql then it builds its own select inside that, and then it puts my SQL in that the net result is something like this ...

exec sp_executesql N'SELECT [Value] FROM (<some custom SQL>)',N'@__arg_1 nvarchar(4000)',@__arg_1 =N'some value'

... i've just pulled that from SQL profiler today.

Instead of that I literally want to see EF execute my sql and NOTHING else then map the results to result rows of type T that I would specify as part of the call.

The usual case that presents this result in my second post on this thread from what I have seen.
Specifying a set as the source type information presumably goes down the same internal EF chain.

[smitpatel]

Now do that without the set.

That is NOT supported in EF Core so you cannot make any assertion about a feature not implemented.

Also that won't generate (from what i've seen) the result you think.

Then please provide a runnable repro code which demonstrate what you are saying.

There is no new information in your arguments from those duplicates as of now.

[TehWardy]

The current implementation as shown above (see my second post on this thread is implemented in EF and a feature I commonly use.

As stated above if I add something like this to my context class ...

class GuidValue
{
     public Guid Value { get; set; }
}

protected virtual DbQuery<GuidValue> GuidList { get; set; }

public IEnumerable<Guid> DoSomethingCustom(string someValue)
{
      return GuidList.FromSql("some custom sql", someValue);
}

... then it will allow me to query the DB without going through a DbSet BUT, because I told it to expect rows of Type GuidValue EF takes it upon itself to wrap my SQL code in its own select clause, again as stated above what I saw in profiler was the following result from this type of code ...

exec sp_executesql N'SELECT [Value] FROM (<some custom SQL>)',N'@__arg_1 nvarchar(4000)',@__arg_1 =N'some value'

... I cannot produce a runable example of a feature i'm requesting only show the current behaviour of the framework (which I believe I have done).

What I am asking for, as a "change or addition to the framework" is to be able to do something like this ...

public IEnumerable<Guid> DoSomethingCustom(string someValue)
{
      return FromSql<Guid>("SELECT x FROM y WHERE z = '@0'", someValue);
}

... which removes all the (imo at least), un-needed bloat but when this is run it would instead produce the following SQL ...

SELECT x FROM y WHERE z = '@0'

... this would allow me to use the mapping features in EF but avoid the "query handling" so that if my SQL contained something like ...

SELECT x FROM someRecursiveFunction(@0) OPTIONS MAXRECURSION=1000

... EF doesn't mess up the query or tell me its invalid because the OPTIONS part ends up being wrapped inside another select query (as per the current behaviour).

For this scenario I have no interest in using defined entity types that are part of the model, and I may want to use an arbitrary DTO type to return multiple values per row not just a list of guids.
I reduced it to a guid list to simplify the question but it seems that doing so somehow matters.

[ErikEJ]

sp_executesql is how the ADO.NET provider executes SQL statements, it has nothing to do with EF Core.

[smitpatel]

public IEnumerable<Guid> DoSomethingCustom(string someValue)
{
      return GuidList.FromSql("some custom sql", someValue);
}

Don't hide your code. You are composing over it. GuidList.FromSql(..) returns IQueryable<GuidValue>. Even with enumerating it you get IEnumerable<GuidValue>. In order to get IEnumerable<Guid> as in your method signature, you need to put Select(c => c.Value) which is composition.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            using (var db = new MyContext())
            {
                // Recreate database
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                // Seed database


                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                try
                {
                    db.DoSomethingCustomGuid("a").ToList();
                }
                catch (Exception)
                { }

                try
                {
                    db.DoSomethingCustom("a").ToList();
                }
                catch (Exception)
                { }
            }
            Console.WriteLine("Program finished.");
        }
    }


    public class MyContext : DbContext
    {
        private static ILoggerFactory ContextLoggerFactory
            => LoggerFactory.Create(b =>
            {
                b
                .AddConsole()
                .AddFilter("", LogLevel.Debug);
            });

        // Declare DBSets
        public DbSet<GuidValue> GuidList { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(ContextLoggerFactory);
        }

        public IEnumerable<Guid> DoSomethingCustomGuid(string someValue)
        {
            return GuidList.FromSqlRaw("some custom sql", someValue).Select(e => e.Value);
        }

        public IEnumerable<GuidValue> DoSomethingCustom(string someValue)
        {
            return GuidList.FromSqlRaw("some custom sql", someValue);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
            modelBuilder.Entity<GuidValue>().HasNoKey().ToView(null);
        }
    }

    public class GuidValue
    {
        public Guid Value { get; set; }
    }
}

Generated SQLs in profiler

exec sp_executesql N'some custom sql
',N'@p0 nvarchar(4000)',@p0=N'a'

exec sp_executesql N'some custom sql
',N'@p0 nvarchar(4000)',@p0=N'a'

First SQL needs to generate following which is a bug tracked at #16079

exec sp_executesql N'SELECT [s].[Value] FROM (some custom sql) AS [s]
',N'@p0 nvarchar(4000)',@p0=N'a'

Above repro code is on EF Core 3.1. Modifying it to show us behavior you are stating. Your code snippets don't compile.

[TehWardy]

So here's a method I have inside my existing context class ...

public void DeleteFolder(Guid folderId)
{
    var folder = GetAll<Folder>()
          .Include(f => f.Roles)
                .ThenInclude(fr => fr.Role)
          .FirstOrDefault(f => f.Id == folderId);

    if (folder != null && folder.UserCan(User, "folder_delete"))
    {
        var tree = GuidList.FromSql(@"
declare @root uniqueidentifier = @p0;
WITH folderTree (Id, [Path]) 
AS (
  SELECT Id, [Path]
    FROM DMS.Folders
    WHERE Id = @root

   UNION ALL

  SELECT f.Id, f.[Path]
    FROM DMS.Folders f
   INNER JOIN folderTree cte ON cte.Id = f.ParentId
)
SELECT Id as Value FROM folderTree", folderId)
        .Select(i => i.Value)
        .ToArray()
        .Reverse()
        .ToArray();

        var folderScript = string.Join("\n", tree.Select(f => $"DELETE FROM [DMS].[Folders] WHERE Id = '{f.ToString()}';").ToArray());
        var fileScript = string.Join("\n",
            GetAll<File>()
                .Where(f => tree.Contains(f.FolderId))
                .Select(f => @"DELETE FROM [DMS].[FileVersions] WHERE FileId = '" + f.Id + "'; DELETE FROM [DMS].[Files] WHERE Id = '" + f.Id + "';")
                .ToArray()
        );

        Database.ExecuteSqlRaw($"{fileScript}\n{folderScript}");
    }
}

In the event that the CTE goes beyond the SQL system default I can't apply the OPTIONS clause to override this as it stands.

I hit this (somewhat unrelated but gives me concrete question for here) ...
#20200
... I have been advised to upgrade to EF Core 3.1

How would guys suggest that I solve this in a framework compliant manner that allows me to get the same result (a recursive deletion of a folder and all it's children from the DB).

These are simply rows "about" a managed file system, in a typical DMS implementation.

Further explanation ...

I use a CTE with potentially deep recursion to compute the tree of folder row Id's
then reverse that set to get the order of "leaf first" then I build a script to run through delete each row and its related file rows with their file version rows.

[smitpatel]

.Select(i => i.Value)

Composition after your FromSql method

[TehWardy]

Not helpful @smitpatel

Yes in this context the composition is there at the moment because of the partial C# implementation, I wasn't claiming this didn't.
This example is of some code in my EF Core 2.2 implementation which i'm here trying to get you guys to at least help me with a rewrite by providing basic functionality that I used to have in EF6.

At the time I couldn't see a clean way to implement this, the framework got in my way on several attempts at various versions so this is the mess i ended up with just to get something to work.

I'm trying to follow best practice here and all i'm getting is "not my problem" style responses from you guys.
The code sample is a typical problem ... how would you solve it given an unknown depth of recursion?

[ajcvickers]

@TehWardy We're not saying "not my problem". What we are saying is that #11624 is tracking this issue already.

[TehWardy]

I give up, so i'll just agree.