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
1 |
var query131 = ctx.Clients.SqlQuery("SELECT * FROM Clients WHERE CountryCode = 'US';"); |
Query from generic type
1 |
var query132 = ctx.Database.SqlQuery<Client>("SELECT * FROM Clients WHERE CountryCode = 'US';"); |
Query arbitrary fields
At first, I need to create a custom class that will contain data from my query.
1 2 3 4 5 |
class ClientInfo { public string Name { get; set; } public string Number { get; set; } } |
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.
1 2 |
string queryText = "SELECT p.Number, c.Name FROM Clients c JOIN Phones p ON c.Id = p.ClientId"; var query133 = ctx.Database.SqlQuery<ClientInfo>(queryText); |
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
1 |
var sqlResults = ctx.Database.SqlQuery<long>("dbo.GetBigintValue"); |
To get this value you need to iterate the collection.
1 2 3 4 5 |
foreach (long result in sqlResults) { // do action, i.e. assign result to variable break; } |