More efficient way of loading children of entity objects in linq to entity query

Question

I have a rather complex linq to entity query that I'm performing, in the end, I have a result set. I loop through that result set, build business objects and return that list of business objects. it's pretty quick, the problem is that 2 of the child properties are complex objects with their own child objects. for every business object in my loop, I then have to make 2 DB calls to fill its child object. Those 2 calls slow down the overall process, is there a better way to do this? noob to EF here. (EF 4,SQL Server 2008,c#)

Get a result set:

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 };

I then loop through that result set:

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;

Is there a better way?

Floremin · Accepted Answer · 2013-04-10 18:22:16Z

2

Just add this .Include("Domains").Include("DomainEntries") to your Linq in in context.Security_Access That should get rows from those tables all in one go.

So your "inner" queries would look like:

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 { ...

Here is the documentation from MS: http://msdn.microsoft.com/en-us/library/bb738708.aspx

answered Apr 10, 2013 at 18:22

Floremin

4,08918 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheRedDwarf Over a year ago

I don't understand how I'm supposed to use this... I have a method called GetDomains(int assetid) that returns a list of objects, how do I incorporate that into this?

Floremin Over a year ago

My assumption is that Domains is a table in your DB and Security_Access table has a foreign key to that table. Using Include("Domains") wouls simply add related rows from that table into your results, so you don't need to have GetDomains method. If this is not the case and your DB structure is different, you probably won't be able to use .Include()

Gareth · Accepted Answer · 2015-11-20 12:53:14Z

0

If you want to improve your performance use compile queries !

You can check the example here.

 static readonly Func<AdventureWorksEntities, Decimal, 
 IQueryable<SalesOrderHeader>> s_compiledQuery2 =
 CompiledQuery.Compile<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>>((ctx, total) => 
from order in ctx.SalesOrderHeaders.Include("Orders") where order.TotalDue >= total select order);

MSDN

AND

You can Introduce Include suppose to select all the employees along with their departments . If you have a navigational property, you won't need a join at all. You can use Include like this:

List<Employee> employeesWithDepartments = CreateObjectSet<Employee>().
                                      Include(e => e.Department).
                                      ToList();

edited Nov 20, 2015 at 12:53

Gareth

5,2636 gold badges47 silver badges73 bronze badges

answered Sep 25, 2013 at 16:33

pixelbyaj

1,1781 gold badge9 silver badges24 bronze badges

Collectives™ on Stack Overflow

More efficient way of loading children of entity objects in linq to entity query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related