Entity Framework Core IN equivalent from list

Question

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 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.

Entity Framework Core IN equivalent from list

0 Answers