Support raw SQL queries without defining an entity type for the result

[anpete]

For Query Types it could be nice to not require a configuration call in OnModelCreating to add the type to the model. Instead, it may be possible to lazily add a type to the model on first access (usually query).

[mrlife]

@anpete I see where this allows raw sql to be run for a given context (existing class), but what about raw sql that is custom? Is there a way to just run a SQL command (e.g. a select) and get an object that contains the resulting rows (regardless of what tables the command includes)?

Source 1 Source 2

[anpete]

@mrlife A Query Type can be any shape you want. Define the type; add it to the model as a Query Type; and then query it using FromSql.

[mrlife]

@anpete That's definitely nice to have. I think the heart of what I'm looking for is a way to skip creating any model classes and just run a query and get a result. Are there any plans to support that, either in EF Core or in some way within an ASP.NET Core project?

[anpete]

@mrlife What would be the type of the result?

[mrlife]

@anpete This kind of query is a huge part of our business, where we use joins and row_number a lot.

I suppose there are many ways to approach how to dynamically create one or more objects to hold whatever is returned from the database (based on detected database column types). That's probably a long discussion on a desirable way to do that. It's not clear if there are any options currently available in ASP.NET Core that handle this.

Examples in .NET: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples

[anpete]

@mrlife As you point out, ADO is the currently recommended way of doing this. NB: You can easily integrate ADO usage with EF, take a look at the DbContext.Database property.

It sounds like you want EF to do something here. Given that you don't want any mapping capabilities, what exactly would EF be adding over ADO? Perhaps providing an example code snippet would help.

[mrlife]

@anpete An ORM by itself would not provide any value with the types of queries we're running, however if EF Core with ADO is the way in ASP.NET Core to run any SQL and get a result set, then that's what we need.

I looked at Configuring a DbContext, but it's not clear how to use it without an explicit model file. Is that possible?

[anpete]

@mrlife It is completely fine to just use ADO from ASP.NET Core - you don't need to use EF at all.

[Vasim-DigitalNexus]

RE: it may be possible to lazily add a type to the model on first access

That would be fantastic; although, I was wondering if you could also give us the option to do add a DbQuery at runtime ourselves as well

Something like:

if (dbContext.Model.FindEntityType(typeof(T)) == null) dbContext.AddQuery(typeof(T));

[mrlife]

@anpete From your last comment, I'm having trouble finding documentation for this for aspnet core. Are you aware of any?

[smitpatel]

@mrlife - There is nothing special in ASP.NET core when it comes to using ADO.NET provider directly. We are not aware of any docs for that. But it wouldn't be much different from how you would use ADO.NET provider from any code.

[Vasim-DigitalNexus]

Is there any way I could implement this in EF 2.2 (meant 2.1) as it is right now?

I was looking for a way to add DbQuery to the DbContext at runtime

[ajcvickers]

@Vasimovic What do you mean by, "implement this in EF 2.2"? Do you mean can you implement this in the EF code and send a PR? Or do you mean, can you implement something on top of EF in your own code that will allow types to be added to the model dynamically?

[Vasim-DigitalNexus]

Hi @ajcvickers, sorry, I meant 2.1, and yes something on top of EF in my own code that will allow types to be added to the model dynamically

[ajcvickers]

@Vasimovic The main limitation in EF Core to be aware of is that the model is read-only once it is being used by a context instance. (Mostly because multiple threads may be accessing it and mutation is not thread-safe.) So, the way to do this now is to build a new model when you need to add a new type, then create a new context instance that uses that model. Whether or not this is practical depends mostly on performance. That is, building a model is expensive, and caching many models will use a lot of memory.

[Vasim-DigitalNexus]

I need it in the same context, I am in the process of migrating a project from EF 6 to EF Core and have about 200 view-only models, adding the DbQuery to the context for 200 of them seemed a bit :smile: - Anyhow #10753 will be very useful for me

Thank you for taking your time to respond @ajcvickers much appreciated

[jenergm]

Hi @ajcvickers, We would like to know if SqlQuery<TElement> will be released in the next version of .NET Core. It's so important to us to make compatible on changing our .NET Framework applications to .NET Core and to the Cloud in the close future. Have you any schedule for this implementation? Cheers,

[ajcvickers]

@jenergm The milestone for each issue indicates the release in which a fix for the issue is planned to ship. Currently we have tentative planning (subject to change) for two releases: 2.2, and 3.0. Issues, like this one, that are in the Backlog milestone are planned to be post 3.0.

[jenergm]

Hi @ajcvickers. Thanks for sharing the roadmap.

[jenergm]

Hi @ajcvickers , how's going? I overhear .NET Core 3.0 will release starting 2019. https://blogs.msdn.microsoft.com/dotnet/2018/05/07/net-core-3-and-support-for-windows-desktop-applications/

Are you know if SqlQuery<TElement> will be released in that next version of .NET Core?

Best regards,

[ajcvickers]

@jenergm Not as currently planned.

[jenergm]

Hi @ajcvickers , You told us on this year 9 Aug that would be in the Backlog milestone planned to be post in 3.0 version as later. There are some problem to don't put it on? We are changing some important applications to .NET Core run in the cloud and this SqlQuery<TElement> is the key to success. I wondering when you intend release, because our schedule is compromised. Can you share us some dates about it? Will this be done? Best regards,

[pantonis]

Any news on this one? When do you plan to release it?

[ajcvickers]

@jenergm @pantonis There is no change to the status. For future reference, the reason we put things in milestones is to provide this information without anyone needing to ask. Since this is in the "Backlog" milestone, this means that the issue will not be fixed for the 2.2 or 3.0 releases.

Things in the Backlog milestone don't have any planned release, but are still things we plan to do at some point. That doesn't mean that we don't think they are very important, but the number of issues on the backlog and the amount of resources dedicated to EF means that even very important things will realistically not get done for a long time.

[ajcvickers]

When looking at this, consider also the issues raised in #14063. Specifically how would different mappings--keys, facets, etc--can be handled for the same CLR type dynamically added in different ways.

[ericnewton76]

@ajcvickers I think the implied implementation for SqlQuery here is much simpler implementation than is being imagined from your team.

Not sure how EF6 does it, but just basic column name to Tresult property name mappings and type conversions handled via Convert.To[PropertyType] is requested here. Eventually people will complain about the performance of it, but frankly it'll always theoretically be x^n faster than the actual network IO to grab the query from a real database connection.

A way to address performance would be mappings but not attached to the context but a separate mapping attached to the Tresult type itself (via thread safe hashset access on the context itself) with thread safe construction of the initial mapping. Once thats baked in, that Tresult cant be fed query shape A and query shape B... it must be used always for query shape A and only if query shape B is a superset of A will that same mapping work... otherwise the developer must use a different Tresult for query shape B and theres no equivalency or whatever to Tresult-A.

If one were to attempt to implement this (like me) I would almost imagine the SqlQuery code to ironically be almost entirely separate code paths from all the model mapping caching etc code in the rest of EFcore. Sometimes we just need a super simple column name to property name deserializer... lol

[divega]

In https://github.com/aspnet/EntityFrameworkCore/issues/1862#issuecomment-451671168, @davidbaxterbrowne described a SqlQuery extension method that works by creating an instance of a derived DbContext type that is generic on the new type to be mapped. And I actually think this is a promising approach:

• Creating a new DbContext type and instance implies some overhead, but this way it leverages existing building blocks, including model caching for the DbContext.

• It is constrained compared to query types in the sense that you cannot customize the mapping, but I think a story that sets this constraint for ad-hoc types and points to mapping types explicitly in OnModelCreating for more advanced scenarios works well. Incidentally, this is the feedback from @ericnewton76 above.

• The only wrinkle I can see is what happens if the ad-hoc type references (through navigation properties) types already explicitly mapped in the original DbContext. Should the model configuration for the closure of types referenced in this way be borrowed from the root DbContext?

[smitpatel]

The only wrinkle I can see is what happens if the ad-hoc type references (through navigation properties) types already explicitly mapped in the original DbContext.

Do we need to support navigations on QueryTypes? What does it represent in terms of database?

[ericnewton76]

I dont see why navigations needs to be supported. Again, I believe this is being way over engineered.

Sometimes I just need a query projection deserialized into POCO objects. SqlQuery in EF noncore does this.

I dont expect support for navigation, I dont even expect being able to pass whatever pops out and serialize to SQL strings... (how does one serialize a projection?)

I just need a simple way to take the columns of a resultset, map them to the like-named columns of a POCO class, do some SqlType->CLRType value conversions and return it.

Cache the mapping results? Okay fine. Don't really care. After the first few records, the time spent is not on the mapping but on actually deserializing the resultset from the wire.

[AndriySvyryd]

Related to https://github.com/aspnet/EntityFrameworkCore/issues/2282

[John0King]

from #17516 :

I'm suggestion add a new method that create IQuerable<T> 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<T>(string sql)
DbContext.FromSqlRaw<T>(string sql, T objectProjection> // some idea of dynamic/anonymous support , .FromSql("SELECT **", new { Name= "", Age = 0, BirthDay = (DateTime?)null })

[ErikEJ]

@john0king See https://github.com/aspnet/EntityFrameworkCore/issues/1862#issuecomment-451671168

[jez9999]

Another vote from me on implementing Database.SqlQuery<TElement> in EF Core. It was extremely useful.

[AndriySvyryd]

Design proposal: instead of creating an IEntityType use a multi-column value converter as it's much more lightweight.

[angelaki]

Is this feature still planned for EFC6? Or does a preview already contain it?

[roji]

@angelaki this feature has been punted for EF Core 6.0 (as per the milestone above).

[angelaki]

Oh sorry, just seen it. Isn't it pretty trivial? Anyways, thank you for drawing attention.

[langdonx]

I've been using this, sourced from a few different places (#1862 and StackOverflow). It works great, but is somewhat annoying because (1) you have provide a type on the left side of your assignment (can't use var) because of the dynamic and (2) you have to create a class (can't use an anonymous object).

I prefer this over others I've seen because I like dealing with parameters as an object vs an array.

Usage:

public class GetCustomerData
{
    public int Id { get; set; }
    public string Name { get; set;}
}

dynamic sqlParameters = new ExpandoObject();
sqlParameters.CustomerId = 1;
sqlParameters.Skip = 0;
sqlParameters.Take = 10;

var sql = @"
select CustomerId as Id, Name
  from Customer
 where customerid = @CustomerId
 order by CustomerId
offset @Skip rows fetch next @Take rows only";

List<GetCustomerData> rows = await context.SqlQueryAsync<GetCustomerData>(sql, sqlParameters);

Implementation:

public static class SqlQueryPolyfill
{
    // here because SqlQuery<T> doesn't exist in EF Core https://github.com/dotnet/efcore/issues/10753
    public static async Task<List<T>> SqlQueryAsync<T>(DatabaseFacade database, string sql, dynamic parameters = null) where T : new()
    {
        using (var command = database.GetDbConnection().CreateCommand())
        {
            command.CommandText = sql;
            database.OpenConnection();

            if (parameters != null)
            {
                if (parameters is ExpandoObject)
                {
                    foreach (var kvp in parameters)
                    {
                        var parameter = command.CreateParameter();
                        parameter.ParameterName = kvp.Key;
                        parameter.Value = kvp.Value;
                        command.Parameters.Add(parameter);
                    }
                }
                else
                {
                    foreach (var prop in parameters.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public))
                    {
                        var parameter = command.CreateParameter();
                        parameter.ParameterName = prop.Name;
                        parameter.Value = prop.GetValue(parameters, null);
                        command.Parameters.Add(parameter);
                    }
                }
            }

            using (var reader = await command.ExecuteReaderAsync())
            {
                if (reader.HasRows)
                {
                    var dt = new DataTable();
                    dt.Load(reader);
                    return DataTableToList<T>(dt);
                }
                else
                {
                    return new List<T>();
                }
            }
        }
    }

    private static List<T> DataTableToList<T>(DataTable table) where T : new()
    {
        var list = new List<T>();

        var typeProperties = typeof(T)
            .GetProperties()
            .Select(propertyInfo => new
            {
                PropertyInfo = propertyInfo,
                Type = Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType
            })
            .ToList();

        foreach (var row in table.Rows.Cast<DataRow>())
        {
            T obj = new T();

            foreach (var typeProperty in typeProperties)
            {
                if (row.Table.Columns.Contains(typeProperty.PropertyInfo.Name))
                {
                    object value = row[typeProperty.PropertyInfo.Name];
                    object safeValue = value == null || DBNull.Value.Equals(value)
                        ? null
                        : Convert.ChangeType(value, typeProperty.Type);

                    typeProperty.PropertyInfo.SetValue(obj, safeValue, null);
                }
            }
            list.Add(obj);
        }
        return list;
    }
}

[ErikEJ]

I have published a NuGet package, that gives you this:

var resultList = await dbContext.SqlQueryAsync<ResultItem>("SELECT Id, Name, PostCode FROM Person");

https://www.nuget.org/packages/ErikEJ.EntityFrameworkCore.SqlServer.SqlQuery

[RainingNight]

Any news?

[roji]

@RainingNight this issue is currently on the backlog, so is not planned for the EF Core 6.0 release.

[angelaki]

@RainingNight I too hoped for this feature quite long until I found https://github.com/DapperLib/Dapper. I use EFCore for my regular / more complex queries now and Dapper for primitive ones. And they both together work like a charm.

[ErikEJ]

@RainingNight and @angelaki You can also use this

[CwjXFH]

Yea, sometimes we need to execute the complex query by calling the RawSql method, but the method only returns the Entity types. So I have to define extension methods for EFCore use Dapper.

[stephajn]

Would this basically be giving us back the Context.Database.SqlQuery method? that was incredibly useful in EF6 and made a lot of sense.

For example, I have a stored procedure that returns a single row of data with three columns. I would much rather be able to project that into a type that is not a DbSet like I could in EF6 using SqlQuery.

[ErikEJ]

@stephajn yes it will

[AraHaan]

I would love if this feature would also allow for raw queries that: do not return things from SELECT (like ones that set the actual outputs to an parameter (stored procedure)), this would actually remove a lot of my ADO.NET Code as then I would know that:

• my fromCode / fromData converters for specific types will run (with ADO.NET there is no guarantee of that happening).

An example pain point of mine is procedures like:

CREATE PROCEDURE [dbo].[RemovePatron]
    @patronUserId VARCHAR(MAX)
AS
DELETE FROM [dbo].[Patrons]
WHERE UserID = @patronUserId;
GO

CREATE PROCEDURE [dbo].[RemoveAllPatrons]
AS
BEGIN
    DELETE FROM [dbo].[Patrons];
END
GO

CREATE PROCEDURE [dbo].[RemoveExpiringVerification]
    @discordID VARCHAR(MAX)
AS
BEGIN
    DELETE FROM [dbo].[ExpiringVerifications]
    WHERE [DiscordID] = @discordID;
END
GO

CREATE PROCEDURE [dbo].[AddPatrons]
    @patrons Patron READONLY
AS
INSERT INTO [dbo].Patrons
SELECT * FROM @patrons
GO

CREATE PROCEDURE [dbo].[FollowerRolesContains]
    @roleId VARCHAR(MAX)
AS
BEGIN
    SELECT * FROM [dbo].[Roles]
    WHERE [Follower] = 1 AND [RoleId] = @roleId;
END
GO

CREATE PROCEDURE [dbo].[AddPatronsFromCsv]
    @filePath VARCHAR(MAX)
AS
BEGIN
    -- Remove all Patrons from the table.
    EXEC [dbo].[RemoveAllPatrons];
    -- Bulk insert new ones into the table from csv file.
    DECLARE @bulk_cmd VARCHAR(MAX);
    SET @bulk_cmd = 'BULK INSERT [dbo].[Patrons] FROM ''' + @filePath + ''';
WITH (FORMAT = ''CSV'', FIELDTERMINATOR = '','', FIRSTROW = 2);';
    EXEC(@bulk_cmd);
    EXEC master.sys.xp_delete_files @filePath;
END
GO

CREATE PROCEDURE [dbo].[AddExpiringVerification]
    @expiringVerification ExpiringVerification READONLY
AS
INSERT INTO [dbo].[ExpiringVerifications]
SELECT * FROM @expiringVerification
GO

CREATE PROCEDURE [dbo].[AddRole]
    @role Role READONLY
AS
INSERT INTO [dbo].[Roles]
SELECT * FROM @role;
GO

If I could use FromSqlRaw with calling these like my other stored procedures it would have been great. Currently I use DbContext.Database.ExecuteSqlRawAsync() / DbContext.Database.ExecuteSqlRaw() for these.

[ajcvickers]

Unfortunately, this feature is dependent on #13947, which is being cut from the 7.0 release. We know people are waiting on this, and it will be a high priority for EF8.

[jenergm]

Unfortunately, this feature is dependent on #13947, which is being cut from the 7.0 release. We know people are waiting on this, and it will be a high priority for EF8.

Thanks for reporting us. Do you have any forecast date about that EF8 release?

[ErikEJ]

@jenergm That will be November 2023.