Entity Framework 4生成的查询正在联接完整表 [英] Entity Framework 4 generated queries are joining full tables

问题描述

我有两个实体：Master和Details。
当我查询它们时，对数据库的查询结果是：

I have two entities: Master and Details. When I query them, the resulting query to database is:

SELECT [Extent2]."needed columns listed here", [Extent1]."needed columns listed here"
FROM (SELECT * [Details]."all columns listed here"...
     FROM [dbo].[Details] AS [Details]) AS [Extent1]
LEFT OUTER JOIN [dbo].[Master] AS [Extent2] ON [Extent1].[key] = [Extent2].[key]
WHERE [Extent1].[filterColumn] = @p__linq__0

我的问题是：为什么过滤器不在内部查询中？如何获得此查询？我已经尝试了很多EF和Linq表达式。

My question is: why not the filter is in the inner query? How can I get this query? I've tried a lot of EF and Linq expressions.

我需要的是：

SELECT <anything needed>
  FROM Master LEFT JOIN Details ON Master.key = Details.Key
 WHERE filterColumn = @param

我在两个表中都有完整的顺序扫描，在生产环境中，每个表中都有数百万行。

I'm having a full sequential scan in both tables, and in my production environment, I have milions of rows in each table.

非常感谢!!

推荐答案

有时，实体框架无法产生最佳查询。您可以执行以下一些操作进行优化。

Sometimes The entity Framework does not produce the best query. You can do a few of the following to optimize.

1. 修改linq语句（使用
   LINQPad ）


2. 创建存储的proc并映射存储的proc proc以返回实体


3. 创建一个处理联接的视图并将该视图映射到新的
   实体

这篇关于Entity Framework 4生成的查询正在联接完整表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！