c# - 在 linq to entity 查询中加载实体对象子对象的更有效方法

Question

我有一个相当复杂的 linq to entity 查询，我正在执行，最后，我有一个结果集。我遍历该结果集，构建业务对象并返回该业务对象列表。很快，问题是 2 个子属性是具有自己子对象的复杂对象。对于循环中的每个业务对象，我必须进行 2 次数据库调用来填充其子对象。这 2 个调用减慢了整个过程，有没有更好的方法来做到这一点？新手到 EF。 (EF 4,SQL Server 2008,c#)

获取结果集:

var newresult = from r in result // result is another complex query
            join subedit in
                (from sa in context.Security_Access
                 join g in context.Security_UserGroup on sa.EntityID equals g.GroupID
                 where (sa.PrivledgeID == xx) && g.UserID == userId
                 select new { user = g.UserID, linkid = sa.LinkID }).Distinct() on new { aid = r.AssetId } equals new { aid = subedit.linkid } into theSubEdit
            from subEditAccess in theSubEdit.DefaultIfEmpty()
            join subdownload in
                (from sa in context.Security_Access
                 join g in context.Security_UserGroup on sa.EntityID equals g.GroupID
                 where (sa.PrivledgeID == xx|| sa.PrivledgeID == yy) && g.UserID == userId
                 select new { user = g.UserID, linkid = sa.LinkID }).Distinct() on new { aid = r.AssetId } equals new { aid = subdownload.linkid } into theSubDownload
            from subDownloadAccess in theSubDownload.DefaultIfEmpty()
            join subView in
                (from sa in context.Security_Access
                 join g in context.Security_UserGroup on sa.EntityID equals g.GroupID
                 where (sa.PrivledgeID == xx|| sa.PrivledgeID == yy|| sa.PrivledgeID == 101) && g.UserID == userId
                 select new { user = g.UserID, linkid = sa.LinkID }).Distinct() on new { aid = r.AssetId } equals new { aid = subView.linkid } into theSubView
            from subViewAccess in theSubView.DefaultIfEmpty()
            select new { r, EditAccess = (int?)subEditAccess.user, DownloadAccess = (int?)subDownloadAccess.user, ViewAccess = (int?)subViewAccess.user };

然后我遍历该结果集:

foreach (var asset in newresult)
{
    // and build a new business object, set its properties
    BoAsset boAsset = new BoAsset();
    boAsset.HasEditRights = (asset.EditAccess > 0);
    boAsset.HasDownloadRights = (asset.DownloadAccess > 0);
    boAsset.HasViewRights = (asset.ViewAccess > 0);
    boAsset.Description = asset.r.Description;
    boAsset.DetailedDescription = asset.r.DetailedDescription;
    boAsset.Keywords = asset.r.Keywords;
    boAsset.Notes = asset.r.Notes;
    boAsset.Photographer = asset.r.Photographer;
    boAsset.PhotographerEmail = asset.r.PhotographerEmail;
    boAsset.Notes = asset.r.Notes;
    boAsset.Author = asset.r.Author;

    // these 2 properties i've commented out are 
    // complex objects/entities, setting them the way I am 
    // requires me to call 2 separate methods which make 2 DB trips
    // per business object.

    //boAsset.Domains = GetAssetDomains(asset.r.AssetId);
    //boAsset.DomainEntries = GetAssetCustomDomains(asset.r.AssetId);

    myListofObjects.Add(boAsset);
}
 return myListofObjects;

有没有更好的方法？

Answer 1

只需将此 .Include("Domains").Include("DomainEntries") 添加到您的 Linq in in context.Security_Access 这应该从所有这些表中获取行一气呵成。

所以你的“内部”查询看起来像:

from sa in context.Security_Access.Include("Domains").Include("DomainEntries")
join g in context.Security_UserGroup on sa.EntityID equals g.GroupID
where (sa.PrivledgeID == xx) && g.UserID == userId
select new { ...

这是 MS 的文档:http://msdn.microsoft.com/en-us/library/bb738708.aspx