IEnumerable属性的聚合/级联的物化和性能

问题在最后。

这是的情况

public class Head {
    public Int32 Id {get; set;}
    public virtual ICollection<Detail> Details {get; set;}
}
public class Detail {
    public Int32 Id {get; set;}
    public virtual Head Head {get; set;}
    public Int32 IType {get; set;}
    public String Code {get; set;}
}

我的需要是用至少两列来填充网格：

• Head.Id
• 所述标头的Code值与给定Detail.Type值的串联

我的第一次尝试是：

Int32 givenValue = 2;
var q = repo.Heads.
            Where(w.Expand()).
            Select(x => new {
                Id = x.IdFolder,
                Details = x.Details.Select(y => new { 
                    Id = y.Id,
                    IType = y.IType,
                    Code = y.Name
                })
            }).OrderBy(x => x.Id).Take(taked).
            ToList().                            // One hit to the database
            Select(y => new {
                Id = y.Id,
                Codes2AsString = String.Join(
                    ",", 
                    y.Details.Where(z => z.IType == givenValue).Select(z => z.Code))
            }).
        ToList();

它运行良好。（我知道我应该在数据库端过滤细节，但我需要整个集合来进行其他连接。）

但是：这段代码慢了8到10，相当于Linq到SQL（我正在迁移一个现有的应用程序）中2850个heads的速度。也就是说，填充网格需要4到5秒，而不是近0秒。

我的第二次尝试是在数据库端聚合/连接，就像在旧版本的应用程序中一样。

我创建了一个视图（具有tsql特性）

create view as v_Head2Codes
select
    h.Id,         
    (
        select ',' + id.Code as [text()]
        from
            Details id
        Where  
            id.Header_Id == h.Id and id.IType = 2 
        order by id.Code
        For XML PATH ('')
    ) Codes
from
    Headers h

然后我创建一个新的类

public class VHead2Codes {
    public Int32 Id {get; set;}
    public String Codes {get; set;}
}

我将这个新类映射到视图，并修改我的Head类

public class Head {
    public Int32 Id {get; set;}
    public virtual ICollection<Detail> Details {get; set;}
    public virtual VHead2Codes Codes2AsString {get; set;}
}

我设置了一对一关系，我的查询变成

var q = repo.Heads.
            Where(w.Expand()).
            Select(x => new {
                Id = x.IdFolder,
                Codes2AsString = x.Codes2AsString.Codes
            }).OrderBy(x => x.Id).Take(taked).
            ToList();                            // One hit to the database

在这里，我得到了和以前一样的结果和表现。

我的第一个猜测是，丢失的微处理器周期被EF物化过程所使用但这可能是错误的（请参阅第二条评论）。循环在串联中丢失：从头到尾的循环。

我的问题是：是否有其他方法可以通过保留perfs来避免视图？

=============================================

======根据您的请求，生成的SQL================

linq查询是：

Folders.Select(x => new {
    Id = x.IdFolder,
    Contribs = x.Contributors.Select(y => new {
        Name = y.Contributor.LastName
    })
})

sql:

SELECT 
[Project1].[idDossier] AS [idDossier], 
[Project1].[C1] AS [C1], 
[Project1].[ThirdParty_Id] AS [ThirdParty_Id], 
[Project1].[LastName] AS [LastName]
FROM ( SELECT 
    [Extent1].[idDossier] AS [idDossier], 
    [Join1].[ThirdParty_Id] AS [ThirdParty_Id], 
    [Join1].[LastName] AS [LastName], 
    CASE WHEN ([Join1].[ThirdParty_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[tableD] AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Extent2].[ThirdParty_Id] AS [ThirdParty_Id], [Extent2].[TableD_Id] AS [TableD_Id], [Extent3].[LastName] AS [LastName]
        FROM  [dbo].[FolderContributions] AS [Extent2]
        INNER JOIN [dbo].[v_ThirdParties] AS [Extent3] ON ([Extent2].[ThirdParty_Id] = [Extent3].[Id]) AND ([Extent2].[ThirdParty_Source] = [Extent3].[Source]) ) AS [Join1] ON [Extent1].[idDossier] = [Join1].[TableD_Id]
)  AS [Project1]
ORDER BY [Project1].[idDossier] ASC, [Project1].[C1] ASC

var givenValue = "t1";
var heads = new[] { // your repo.Heads query here: heads = repo.Heads.Where(w.Expand()).OrderBy(x => x.IdFolder).Take(taked);
    new {IdFolder = 1, Details = new[]{new {Code = "a", IType = "t1"}, new {Code = "b", IType = "t2"}}},
    new {IdFolder = 2, Details = new[]{new {Code = "c", IType = "t2"}, new {Code = "d", IType = "t1"}}},
};
// Db hit.
var q = heads; 
var details = q.SelectMany(
    h=>h.Details
        .Where(d=>d.IType == givenValue)
        .Select(d=>new{HeadId = h.IdFolder, d.Code})).ToList();
// O(N) in-memory.
var grid = details
    .ToLookup(d=>d.HeadId)
    .Select(g=>new{HeadId = g.Key, Codes = string.Join(",",g.Select(i=>i.Code))})
    .ToList();