Im working with Entity Framework version 6.1.3 and I want to execute a SQL-query which gathers information from multiple tables like this:
var result = context.Database.SqlQuery<SomeType>("SELECT SUM(d.PurchaseValue) AS 'Total', div.Name, l.Name " +
"FROM Device AS d " +
"RIGHT JOIN Location AS l " +
"ON d.LOCATION_ID = l.ID " +
"RIGHT JOIN Division AS div " +
"ON d.DIVISION_ID = div.ID " +
"GROUP BY div.Name, l.Name " +
"ORDER BY l.Name");
My question is, what should be the the type in SqlQuery<>? Or what is the proper way to execute a query like this and get a result out of it?
Here SomeType
can be any type that has properties which match the names of the columns returned from the query.
For example, your query returns columns: Total | Name
Therefore, your return type (class) can be similar to below...
public class SomeType
{
public string Name { get; set; }
public decimal Total { get; set; }
}
Your query will be
var result = context.Database.SqlQuery<List<SomeType>>(...);
Have you seen checked the Data Development Center for ways to do this?
Note 'Entity Framework allows you to query using LINQ with your entity classes. However, there may be times that you want to run queries using raw SQL directly against the database.'