CODEWITHSUNDEEP
entity-frameworklinqfilter
robin
robin

Reputation: 497

Filter Linq Child Collection by another List/Array

When I try to filter a child collection based on a simple value I can do it easily by doing something like this:

db.Table.Where(a => a.SubTable.Any(b => b.SubTableId == 1));

But when I try to filter this same child collection according a list of values that corresponds of it, I'm getting errors all the time.

List<long> listOfIDs = new List<long> { 1, 2, 3 };
db.Table.Where(a => listOfIDs.Any(a.SubTable.Select(b => b.SubTableId));

I searched a lot how to do this and I'm still starting EF. The error I'm getting is Cannot convert from System.Collections.Generic to Func.

Anybody cold please help me telling me what I'm doing wrong? I just want to filter a collection using an array, list, anything...

Regards, Rubens

Upvotes: 3

Views: 2482

Answers (4)

Tom Deloford
Tom Deloford

Reputation: 2175

Try starting from the SubTable:

var results = from s in SubTable
              join id in listOfIDs on s.SubTableID equals id.ID
              select s.Table;

or if you prefer

var results = subTables.Where(a => listOfIDs.Contains(a.SubTableID))
                       .Select(s => s.Table);

Finally if you do not have access to the SubTable table

 var results = tables.Select(t => t.SubTable)
                     .Where(a => listOfIDs.Contains(a.SubTableID))
                     .Select(s => s.Table);

Upvotes: 0

Sampath
Sampath

Reputation: 65978

You can try as shown below.

db.Table.Where(a => a.SubTable.Any(x=> listOfIDs.Contains(x.SubTableId)));

Upvotes: 5

djangojazz
djangojazz

Reputation: 13170

You almost had it. You need to kind of reverse the logic in the nested predicate to do the collection you are searching for and then expose the lambda continuation variable to that. I gave a little bit more complex example were say you have a collection of other complex objects and you want to find properties of those and their parents as well:

public class POC
{
  public int Id { get; set; }
  public string Desc { get; set; }
  public List<Order> Orders { get; set; }
}

public class Order
{
  public int Id { get; set; }
  public string Desc { get; set; }
}

static List<Order> GetOrders(int numberOfOrders)
{
  var orders = new List<Order>();

  for (int i = 1; i <= numberOfOrders; i++)
  {
    orders.Add(new Order { Id = i, Desc = $"{i} Order" });
  }

  return orders;
}

static List<POC> GetPOCOsAndOrders()
{
  return new List<POC>
  {
      new POC { Id = 1, Desc = "John", Orders = GetOrders(1)},
      new POC { Id = 2, Desc = "Jane", Orders = GetOrders(2) },
      new POC { Id = 3, Desc = "Joey" , Orders = GetOrders(3)}
  };
}


static void Main(string[] args)
{
  var orders = new List<int> { 2, 3 };
  var items = GetPOCOsAndOrders();

  var peopleAndOrdersWhereOrderNumberIsGreaterThanTwo = items.Where(x => x.Orders.Any(y => orders.Contains(y.Id)));

  //I should only get the last two people out of three and their orders
  peopleAndOrdersWhereOrderNumberIsGreaterThanTwo.ToList().ForEach(x => Console.WriteLine($"{x.Id} {x.Desc} {x.Orders.Count}"));

  Console.ReadLine();
}

Upvotes: 2

Esperento57
Esperento57

Reputation: 17492

try this:

    var ResultList=(
      from elemID in listOfIDs 
      from elemA in db.Table.Where(elemA => elemA.SubTable.Contains(elemID)).DefaultIfEmpty() 
     where elemA !=null
     select elemA).ToList();

Upvotes: 0

Related Questions