In Entity Framework currently, SQL queries must return a type in the model (known to EF) or a scalar type. In the cases where an app needs to execute an SQL query which returns an unknown or unmapped type developers usually drop to ADO.NET or use something like Dapper.
In order to not have to ‘leave’ EF the ability to issue a raw query against the DB and have the results put into an unmapped (unknown to EF) entity has been a much requested feature by developers.
Thankfully this feature is now complete and merged so will be available in Preview 1 or in the Entity Framework daily builds now if you want a really early look at it.
Raw SQL queries for unmapped types example
I’ve pulled down EF version 8.0.0-alpha.1.23069.1 from the daily builds for this example.
Our sample unmapped type is OrdersVM below. Entity Framework knows nothing about this class, it is not configured in the DbContext or tracked by EF in anyway.
Given the above unmapped type both of the queries below will populate a List<OrdersVM> collection.
The two queries are very similar except the 2nd one filters on the OrderDate column in the DB. Entity Framework will pass in the OrdersAfterDate value as a parameter to be safe against SQL Injection.
Looking back at the OrdersVM class above we can see a number of interesting things highlighted in red …
- Casing doesn’t matter.
- Properties which have public sets must be in the query or an exception will throw.
- Constructors work fine, but the names and types must match. If there’s a value in the query but not in the constructor params the constructor will still be called but the missing value will never be set.
What do you think?
Will you use this functionality?
Will you drop Dapper now that this is soon to be supported in EF8?