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

Model: Dynamic introduction of types. #10753

Open
anpete opened this Issue Jan 23, 2018 · 29 comments

Comments

Projects
None yet
9 participants
@anpete
Copy link

anpete commented Jan 23, 2018

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

This comment has been minimized.

Copy link

mrlife commented Apr 9, 2018

@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

This comment has been minimized.

Copy link

anpete commented Apr 9, 2018

@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

This comment has been minimized.

Copy link

mrlife commented Apr 9, 2018

@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

This comment has been minimized.

Copy link

anpete commented Apr 9, 2018

@mrlife What would be the type of the result?

@mrlife

This comment has been minimized.

Copy link

mrlife commented Apr 9, 2018

@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

This comment has been minimized.

Copy link

anpete commented Apr 9, 2018

@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

This comment has been minimized.

Copy link

mrlife commented Apr 9, 2018

@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

This comment has been minimized.

Copy link

anpete commented Apr 9, 2018

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

@Vasimovic

This comment has been minimized.

Copy link

Vasimovic commented May 15, 2018

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

This comment has been minimized.

Copy link

mrlife commented Jun 12, 2018

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

@smitpatel

This comment has been minimized.

Copy link
Contributor

smitpatel commented Jun 14, 2018

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

@Vasimovic

This comment has been minimized.

Copy link

Vasimovic commented Jun 28, 2018

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

This comment has been minimized.

Copy link
Member

ajcvickers commented Jun 28, 2018

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

@Vasimovic

This comment has been minimized.

Copy link

Vasimovic commented Jun 29, 2018

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

This comment has been minimized.

Copy link
Member

ajcvickers commented Jun 29, 2018

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

@Vasimovic

This comment has been minimized.

Copy link

Vasimovic commented Jun 29, 2018

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

This comment has been minimized.

Copy link

jenergm commented Aug 9, 2018

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

This comment has been minimized.

Copy link
Member

ajcvickers commented Aug 9, 2018

@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

This comment has been minimized.

Copy link

jenergm commented Aug 9, 2018

Hi @ajcvickers. Thanks for sharing the roadmap.

@jenergm

This comment has been minimized.

Copy link

jenergm commented Oct 30, 2018

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

This comment has been minimized.

Copy link
Member

ajcvickers commented Oct 30, 2018

@jenergm Not as currently planned.

@jenergm

This comment has been minimized.

Copy link

jenergm commented Oct 31, 2018

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

This comment has been minimized.

Copy link

pantonis commented Nov 1, 2018

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

@ajcvickers

This comment has been minimized.

Copy link
Member

ajcvickers commented Nov 1, 2018

@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

This comment has been minimized.

Copy link
Member

ajcvickers commented Dec 12, 2018

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

This comment has been minimized.

Copy link

ericnewton76 commented Jan 1, 2019

@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

This comment has been minimized.

Copy link
Member

divega commented Jan 5, 2019

In #1862 (comment), @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

This comment has been minimized.

Copy link
Contributor

smitpatel commented Jan 7, 2019

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

This comment has been minimized.

Copy link

ericnewton76 commented Jan 7, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment