Entity Framework Usage Note: The associated entity used in the where query condition does not require an include

Http://www.cnblogs.com/dudu/archive/2012/04/13/entity_framework_include_where.html

In the Entity Framework, if entity A is associated with entity B, you want to load entity B while loading entity a. It is common practice to use include () in LINQ queries. However, if you use entity B,ef in the query criteria to automatically load entity B, the include is not only redundant, but also adds additional left OUTER join queries that affect performance.

For Q.cnblogs.compublic class Questionservice {    private irepository<questionitem> _questionrepository;    Public Questionservice (Iunitofwork unitofwork)        : Base (unitofwork)    {        _questionrepository = new Repository <QuestionItem> (unitofwork);    }    Public list<questionitem> getunsolvedquestions (int pageIndex, int pageSize)    {        return _ Questionrepository.entities            . Include (q = q.user)            . Where (q = q.isactive && q.user.isactive)            . Skip ((pageIndex-1) * pageSize)            . Take (pageSize)            . ToList ();                }} public class questionitem{Public    int Id {get;set;}    public string Title {get; set;}    public bool IsActive {get; set;}    public int UserId {get; set;}    Public user user {get; set;}} public class user{Public    int UserId {get; set;}    public bool IsActive {get;set;}}

In the above code, we want to include the user information in the Questionitem when Getactivequestions () returns list<questionitem>, so the LINQ query uses the. Include (q = Q.user).

(Special note: here is the Q.) User. IsActive as one of the query criteria)

Then we use SQL Server Profiler to discover that the Entity framework generates the following SQL statement:

SELECT TOP [Filter1]. [ID] as [id], [Filter1]. [Title] As [Title],[filter1]. [UserId] As [UserId], [Filter1]. [USERID1] As [UserId1], [Filter1]. [IsActive1] As [IsActive], [Filter1]. [USERID2] As [Userid1],[filter1]. [IsActive2] As [Isactive1]from (SELECT [extent1].[ ID] as [id], [Extent1]. [Title] As [Title], [Extent1]. [UserId] As [Userid1],[extent1]. [IsActive] As [Isactive1],[extent3]. [UserID] As [UserID2], [Extent3]. [IsActive] As [IsActive2], Row_number () over (ORDER by [extent1].[ QID] DESC) as [Row_number]    from   [dbo].[ Question_item] as [Extent1]    INNER JOIN [dbo].[ Users] as [Extent2] on [Extent1]. [UserID] = [Extent2]. [UserID]    Left OUTER JOIN [dbo]. [Users] As [Extent3] on [Extent1]. [UserID] = [Extent3]. [UserID]    WHERE ([extent1].[ IsActive] = 1) and ([extent2].[ IsActive] = 1)) as  [Filter1]where [filter1].[ Row_number] > 0ORDER by [Filter1]. [Id] DESC

[dbo]. The [Users] table corresponds to the user entity class, which is in SQL [dbo]. [Users] Two joins, with two joins affecting the execution plan and increased query time due to the large amount of data on the [Users] table. This is clearly to be avoided.

After a fierce battle with this problem, we finally found a solution--to remove the include, it is so simple!

From this point of view, the Entity framework is still very clever, but because it does not know the wisdom of it, but it brings problems.

So, code like people, to get along well with her, we must know everything about her!

Entity Framework Usage Note: The associated entity used in the where query condition does not require an include