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.
Limitations
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?
Thanks for writing up this example of the feature in action, Dave!
Much appreciated.
Looking forward to using this feature once EF Core 8.0 becomes stable for some exception scenarios in our codebase — MUCH better than having to drop out / include a whole additional data access stack and/or other libraries (such as ADO.net / Dapper).
Thanks!
In a few scenarios, we want to pull only sub-set of columns using Raw sql Query to calling stored procedure use SqlQueryRaw method. but still want to use the same model. that throw this below exception
“The required column ‘colname’ was not present in the results of a ‘FromSql’ operation”.
How to achieve those scenarios using Raw sql
Here My method to called stored procedure using raw sql
public IEnumerable ExecStoredProc(string procName, IDictionary parameters)
{
try
{
var inputParameter = new List(parameters.Count);
foreach (var parameter in parameters)
{
inputParameter.Add(new SqlParameter
{
ParameterName = parameter.Key,
Value = parameter.Value ?? DBNull.Value
});
}
string procWithParameter = string.Format(“exec {0} {1}”, procName, string.Join(“, “, inputParameter.Select(p => p.ParameterName)));
var result = _dbContext.Database.SqlQueryRaw (procWithParameter, inputParameter.ToArray());
return result.ToList();
//return result.ToList();
}
catch (System.Exception ex)
{
throw ex;
}
}