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

[ErikEJ]

Amazing. So this will be in daily build now?

[jenergm]

Thank you @ajcvickers!

I think it is this:
public static IQueryable SqlQuery(
this DatabaseFacade databaseFacade,
[NotParameterized] FormattableString sql)
Where calls that:
=> SqlQueryRaw(databaseFacade, sql.Format, sql.GetArguments()!);

Very good! Did it already have released?

[ajcvickers]

@ErikEJ Should be.

@jenergm As the milestone indicates, it will be released in EF Core 8. However, it should be available now in the daily builds and will be included in preview 1 of EF8.

[goenning]

@ajcvickers just tried this feature today and I love it! This can easily replace Dapper for many and allow us to incrementally adopt EF Core.

One question though: will it support scalar values? This doesn't seem to work (only tested with npgsql).

_db.Database.SqlQuery<string>($"SELECT id FROM environments WHERE key = {appKey}").FirstOrDefault();

[Misiu]

will it support scalar values?

That would be awesome! Waiting for more info.

[ajcvickers]

@goenning @Misiu Scalar values were already supported in EF7, and should still work. If this isn't the case, then 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.

[Misiu]

@ajcvickers I've created a new issue showing this error: #30447 In my case I'm using SqlServer.

The issue is that EF generates this code:

exec sp_executesql N'SELECT TOP(1) [t].[Value]
FROM (
    SELECT name FROM dbo.test_table WHERE entity_id=@p0
) AS [t]',N'@p0 nvarchar(4000)',@p0=N'5A83F3C3-A88F-4A56-934C-FFB8D0E682C1'

instead of:

exec sp_executesql N'SELECT TOP(1) [p].[Name]
FROM (
    SELECT name FROM dbo.test_table WHERE entity_id=@p0
) AS [p]',N'@p0 nvarchar(4000)',@p0=N'5A83F3C3-A88F-4A56-934C-FFB8D0E682C1'

[goenning]

Thanks @Misiu thsts the same error message I get using Postgres

[borisdj]

@ajcvickers I have posted related Q on SO:
https://stackoverflow.com/questions/76331657/dynamic-sub-select-using-expression-with-fromsqlraw-in-net-core-v7-with-cast
Can it be linked here, and can anyone take a look, or maybe to open separate issue.

[ite-klass]

@borisdj The linked question is confusing overall, but it adds a class definition to the DB context - so no, it's not directly relevant to this ticket - which is specifically about non-entity result types

[pcapozzi]

would it be possible to show an example of how to call a stored procedure with this new functionality?

[ajcvickers]

@pcapozzi See https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#raw-sql-queries-for-unmapped-types

[pcapozzi]

Thanks for that information. It seems that EF8 will give some exciting new potential. My only comment is that when you mention in the docs ' The code shown here comes from RawSqlSample.cs.' Although it does seem to be part of NewInEFCore8 project, I have never been able to download a specific project. They all seem to be part of the 'EntityFramework.Docs' solution which is now up to what seems like 100 terabytes. It would be very useful to be able to 'clone' individual projects. Thanks for your help Regards, PatC

…

On Tue, Oct 3, 2023 at 11:28 AM Arthur Vickers ***@***.***> wrote: @pcapozzi <https://github.com/pcapozzi> See https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#raw-sql-queries-for-unmapped-types — Reply to this email directly, view it on GitHub <#10753 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABE7UL5F3Q5EEKPPBQNZBLX5PLDHAVCNFSM4ENJ43T2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TCNZUGQ2TQMZVGE4A> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[DavidThielen]

This is added, so great. I want to add that this is essential for calls that use FreeTextTable() and ContainsTable() in SQL Server.