Entity Framework query

Question

Hi all,

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!

Answer 1

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.

Answer 2

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.