在实体框架中通过原始 SQL 查询获取复杂对象 [英] Fetching complex objects by raw SQL query in Entity Framework
问题描述
我想使用单个查询从数据库复杂对象中获取.我们来看下面的例子:
I would like to fetch from database complex object using single query. Let's look at the following example:
SELECT TableA.*, TableB.*
FROM TableA
INNER JOIN TableA.B_Id = TableB.Id
和相应的类:
public class QueryResult
{
public TableA A { get; set; }
public TableB B { get; set; }
}
public class TableA
{
public int Id { get; set; }
public string SomeContentA { get; set; }
public int B_Id { get; set; }
}
public class TableB
{
public int Id { get; set; }
public int SomeContentB { get; set; }
}
我想从上面对数据库执行原始 SQL 查询,并获取正确设置 A 和 B 属性的 QueryResult 对象集合.到目前为止,我尝试使用 SqlQuery 方法,但我只设法在 A 和 B 属性中获取具有空值的 QueryResult 对象集合(显然返回的结果集未正确绑定到属性):
I would like to execute the raw SQL query from above against the database and get collection of QueryResult objects with correctly set A and B properties. So far I tried using SqlQuery method, but I only managed to get collection of QueryResult objects with nulls in A and B properties (apparently returned result set was not correctly binded to properties):
var results = ctx.Database.SqlQuery<QueryResult>(\\example_query).ToList();
注意:
- 我不应该在 SELECT 语句中手动列出列.TableA 和 TableB 类以及 SQL 表可能会随着时间的推移而发生变化,但这些变化将保持一致.
- 三个查询(一个从 TableA 和 TableB 中获取 ID,第二个从 TableA 中获取对象,第三个从 TableB 中获取对象)会影响性能,我应该尽可能避免它.
我使用的是实体框架 4.3 和 SQL Server 2012.
I am using Entity Framework 4.3 and SQL Server 2012.
谢谢,艺术
推荐答案
您仍然可以使用常规的 EF 结构,只需将您的类映射到相应的表并强制在 LINQ-To-Entities 中加入:
You can still use regular EF constructions by just mapping your classes to their corresponding tables and forcing the join in LINQ-To-Entities:
using(var ctx = new MyDbContext())
{
return ctx.TableA
.Join(ctx.TableB, a=>a.B_Id, b=>b.Id, (a,b)=>
new QueryResult{TableA=a, TableB=b});
}
我认为这是唯一的方法,至少到 EF6.
I think that's the only way, at least up to EF6.
这篇关于在实体框架中通过原始 SQL 查询获取复杂对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!