LINQ Select Object based on a property within a list within the object

Question

I am trying to select a list of objects from a list of objects which has a list of objects of another type within itself based on a property within that list of objects.

For example

class TypeA{
  string Name {get; set;}
  List<TypeB> ListOfTypeB {get; set;}
}

class TypeB{
  int Age {get; set;}
  bool Active {get; set;}
}

I have a list of Type A which is populated by the database, I want to query the list of TypeA and Return a List of TypeA based on the Active property in TypeB which is a list within the TypeA object.

Any help would be great.

Thanks

Answer 1

You wrote:

I want to query the list of TypeA and Return a List of TypeA based on the Active property in TypeB.

This specification is a bit unclear. I assume you mean that you want all TypeA objects that have at least one TypeB object with a true value TypeB.Activity (or those with a false one).

The answer depends on whether you want to perform your query as Enumerable or as Queryable. In words: Do you first want to get the objects into local memory and then perform the query (AsEnumerable) or do you want to let the database perform the query (Asqueryable) and then only return the valid results to local memory.

AsEnumerable

This method is not very efficient, because it would mean that you get the complete TypeA and TypeB table to local memory before processing.

However your query would be simple:

var AwithActivity = allTypeA
    .Where(a => a.ListOfTypeB.Any(b => b.Activity));

In words:

From the complete sequence of typeA elements, take only those typeA elements that have at least one ListOfTypeB element with a true value for property Activity.

AsQueryable

If your database is set up correctly you will have two tables. One table with TypeA items and one table with TypeB items.

TypeA has a one-to-many relation with TypeB. Every TypeA has zero or more TypeB and every TypeB belongs to exactly one TypeA.

In your database both TypeA and TypeB will have a primary key. Every TypeB will have a foreign key to the TypeA to wich it belongs.

This is fairly standard database. It helps you with your query. Using these definitions your query will be translated into:

I want all TypeA objects of which there is at least one TypeB object that has a foreign key to this TypeA object AND a true value for Activity

The query will be:

var results = TypeATable            // groupjoin table TypeA with table TypeB
    .GroupJoin(TypeBTable,
      typeA => typeA.PrimaryKey,    // from every typeA use the primary key
      typeB => typeB.ForeignKey,    // from every typeB use the foreign key to the TypeA
      (a, allB) => new              // for every A with all matching typeB:
      {                             // create a new anonymous type
         A = a,                     // with the found A
         HasActivity = allB         // and a boolean that says if there was
            .Any(b => b.Activity),  // at least one B with a true Activity
      });

To get all TypeA with at least one activity:

var AWithActivity = results.Where(result => result.HasActivity)
   .Select(result => result.A);

To get all TypeA that have no activity at all:

var AwithoutActivity = result.Where(result => !result.HasActivity)
   .Select(result => result.A);

Addition If could be that the relation between a typeA and typeB is not one-to-many, but many-to-many. In that case, you take all active typeB objects and find all typeA objects with foreign keys to this active typeB object