Querying Entity Model. Part 13 – Raw SQL

Entity Framework allows to execute queries written in Transact-SQL. EF has a SqlQuery method. You can read about it in MSDN article Raw SQL Queries
But it has a very important limitation – query must return concrete types (like int or string), entity instances or any other class instances.

Query from Entity

Query from generic type

Query arbitrary fields

At first, I need to create a custom class that will contain data from my query.

Now I can execute any SQL query that would return the same fields as declared in the class. The order of properties in the class and the order of fields in the query are not relevant.

It’s worth to mention that class ClientInfo is not included in the DbContext class as DbSet<ClientInfo>. It’s just a class in your code.

LINQ queries allows to create anonymous types, SqlQuery does not. Nevertheless, it’s a very good feature to run your own T-SQL code. Also you can create stored procedures in SQL Server database and run them from Entity Framework. I wrote about it in my post Code First. Executing Stored Procedures

[UPDATE 2016-08-24]
SqlQuery returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery. For example, you have a stored procedure that will return a single bigint value.

To get this value you need to iterate the collection.

Leave a Reply

Your email address will not be published. Required fields are marked *