EF: How to execute a SQL-query with multiple joins?

asp.net-mvc c# entity-framework-6 sql

Question

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?

1
2
9/30/2016 3:37:59 PM

Accepted Answer

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>>(...);
2
2/24/2020 8:23:46 PM

Popular Answer

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.'

So if you can, use LINQ. Check here and here to start.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow