Filter a list of objects by comparing with another list of different objects

Question

I have the following 3 classes in my dbml file:

public class Player {
    public int PlayerID {get; set;}
    public string Name {get; set;}
 }

public class PlayerItem {
    public int PlayerItemID {get; set;}
    public int PlayerID {get; set;}
    public int ItemID {get; set;}
}

There is an association created between Player.ID and PlayerItem.PlayerID

Public Class CustomItem {
    public int ItemID {get; set;}
    public string ItemName {get; set;}
}

Here's the setup:

1. I have a list of Players - List<Player>
2. Each player has a Entityset child of type PlayerItem
3. I have a list of Items - List<Item>

How can I select only those players that have at least one custom item in their list of PlayerItems? This is basically matching ItemID in each Player's PlayerItems with the Item ID in CustomItem.

Ultimately, I'd like to have a simple list of players - List<Player> - to work with.

Answer 1

LINQ make this sort of thing easy:

players.Where( p => p.PlayerItemList.Any( 
              pi => customItems.Any( ci => ci.ItemID == pi.ItemID ) );

This can also be written in query form:

var result = from p in players
             from pi in p.PlayerItemList
             where customItems.Any( ci => ci.ItemID == pi.ItemID ) );

This will perform best if you create a lookup table or dictionary from your custom item set:

var customItemDict = customItems.ToDictionary( ci => ci.ItemID );
var result = from p in players
             from pi in p.PlayerItemList
             where customItemDict.ContainsKey( pi.ItemID ) );