I have read: Entity Framework Core - IN clause equivalent
I am trying to produce a query similar to the above post, but instead of matching a single ID, I have an array of objects. I am trying to select all matching entities from the context that match those in my list of objects.
I have an IEnumerable<PlayerRank> rankings and I am trying to select all PlayerRanks from the table that match the Date, Player, and World of those in my list.
This is the best I could come up with:
_context.PlayerRankings.Where(p => rankings.Any(k =>
k.Date == p.Date &&
k.Player == p.Player &&
k.World == p.World));
This ends up performing what is equivalent to a SELECT * FROM query, and pulls the entire table into memory, then filters it based on my rankings list. The table is a few million rows long, and this is pretty crippling performance wise.
An example of what the SQL might look like:
SELECT * FROM db.player_rankings
WHERE (date, player, world) IN (
('2017-01-18', 'Player1', 6),
('2017-01-18', 'Player2', 6),
('2017-01-18', 'Player3', 6),
('2017-01-18', 'Player4', 6),
('2017-01-18', 'Player5', 6)
)
How can I use the Fluent API to generate an WHERE IN statement to match my list of PlayerRanks?
I am using MySQL and the Pomelo connector. The Date, Player, & World are a unique index on the table.
