[Feature Request] Directly type-mapping for raw SQL query statement

[sgjsakura]

Problem Description

The EF Core 2.1 provides the new query type to mapping query-based data set into CLR types. Combined with the FromSql method, it is now possible to extract query reuslts of raw SQL statement directly such as:

var result = myContext.Query<TopStudentInfo>.FromSql("EXEC [GetTopStudents]").ToArray();

However, a strong restriction for the above code is that you must first include the query type into the DbContext using either DbQuery<T> property or IModelBuilder.Query<T> method. It is quite a boring work if you want to executing serveral diffierent stored procedures and under such circumstance you must include each query type individually.

Hoped Enhancement

Actually I want that we can executing a raw SQL statement with query type mapping directly with an optimized syntax like:

var result = myContext.FromSql<TopStudentInfo>("EXEC [GetTopStudents]").ToArray();

Some Effort I Made with Limitations

Currently I tried to provide a package Sakura.EntityFrameworkCore.FromSqlExtensions with the extension FromSql method. Internally it uses the Model.AddQueryType to register a new query type in-place when this method is called.

I must reigister the query type first because the the EntityQueryableExpressionVisitor always uses the FindEntityType to retrieve the entity type, and if the type is not registered yet, exception will be thrown. Althought the RemoveEntityType method is also provided, it is difficult to determine the lifetime of a query instance, and thus I cannot remove the new registered type properly. To remove the side effect of globally registering a new type in model, I think either non-registered type discovery should be implemented, or the query life-time related management manner may be provided so that the newly registred type can be removed after the query resource is disposed.

I will also be glad to create a pull request if you think the current limited implementation is accepatable for the EF Core. Best wishes and hope this feature can be provided as soon as possible. If there's already a way to meet such a requirement, please kindly show me an example and many thanks.

[Vasim-DigitalNexus]

The feature for the Query (dynamic introduction of types) is tracked here: #10753, although this feature would be great, I still think we need another simple raw mapping function for read-only none-tracked queries that are not rooted in the DbSet/DbQuery

[ajcvickers]

Closing as duplicate of #10753