EF:如何执行具有多个联接的SQL查询? [英] EF: How to execute a SQL-query with multiple joins?

问题描述

我正在使用Entity Framework 6.1.3版，并且我想执行一个SQL查询 这样可以从多个表中收集信息:

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");

我的问题是，SqlQuery<>中的类型应该是什么?还是执行这样的查询并从中获取结果的正确方法是什么?

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?

推荐答案

此处SomeType可以是具有与查询返回的列名称相匹配的属性的任何类型.

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; }
}

您的查询将是

var result = context.Database.SqlQuery<List<SomeType>>(...);

这篇关于EF:如何执行具有多个联接的SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！