Entity Framework query

Question

I have a piece of code that I don't know how to improve it.

I have two entities: EntityP and EntityC. EntityP is the parent of EntityC. It is 1 to many relationship. EntityP has a property depending on a property of all its attached EntityC.

I need to load a list of EntityP with the property set correctly. So I wrote a piece of code to get the EntityP List first.It's called entityP_List. Then as I wrote below, I loop through the entityP_List and for each of them, I query the database with a "any" function which will eventually be translated to "NOT EXIST" sql query. The reason I use this is that I don't want to load all the attached entityC from database to memory, because I only need the aggregation value of their property. But the problem here is, the looping will query the databae many times, for each EntityP!

So I am wondering if anybody can help me improve the code to query the database only once to get all the EntityP.IsAll_C_Complete set, without load EntityC to memory.

foreach(EntityP p in entityP_List)
                {

                        isAnyNotComoplete = entities.entityC.Any(c => c.IsComplete==false && c.parent.ID == p.ID);
                    p.IsAll_C_Complete = !isAnyNotComoplete;
                }

Thank you very much!

Craig Stuntz · Accepted Answer · 2010-04-23 20:57:41Z

1

In EF 4, you can do:

var ids = entityP_List.Select(p => p.ID);
var q = (from p in entities.entityP
         where ids.Contains(p => p.ID)
         select new 
         {
             ID = p.ID,
             IsAll_C_Complete = !p.entityCs.Any(c => !c.IsComplete)
         }).ToList();
foreach (var p in entityP_List)
{
    p.IsAll_C_Complete = q.Where(e.ID == p.Id).Single().IsAll_C_Complete;
}

...which will do the whole thing in one DB query. For EF 1, Google BuildContainsExpression for a replacement for the .Contains( part of the above.

answered Apr 23, 2010 at 20:57

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

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

Comments

tgolisch · Accepted Answer · 2010-04-23 19:56:44Z

0

I would base EntityP on a SQL View instead of a table. Then I would define the relationship, and aggregate the value for child table within the view.

answered Apr 23, 2010 at 19:56

tgolisch

6,7743 gold badges27 silver badges46 bronze badges

Collectives™ on Stack Overflow

Entity Framework query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related