Select from a list all the skus that match id and colour LINQ selector

Question

I have a database that I'm connecting to using EF Core.

Table:

Id    Sku    Colour
--------------------
 1    123    blue
 1    124    black
 1    125    green
 2    126    yellow
 2    127    red
 3    128    white
 4    129    pink

Now I have the input list that is for example 1,blue format and I need this whole list to check in the db and return all the skus that are matching to that.

What I have so far but only works one at a time because I can’t get it to work from a list of items to return a list of corresponding skus.

public async Task<List<ProdBarcode>> FindSku(string id, string colour)
{
    return await ProdBarcode.Where(x => x.ID == id 
                                        && x.Colour == colour)
                            .ToListAsync();
}

How ca I return a list with multiple Skus per item+colour combinations? I have tried instead of the 2 param id and colour to have a list of strings as an input but then all the modifications I did on the return are not working also not too sure what to use maybe contains? Current method will cause a webpage to throw a connection time out as it takes some time to go through 2000 items at a one by one basis.

I guess the SQL will be something like:

SELECT sku 
FROM ProdBarcode 
WHERE id + ‘,’ + colour IN (inputList)

Answer 1

Not sure if a Tuple can be translated via Linq-to-Sql, but its worth a test. Tested this with a local collection, so at the very least it should work on a set that is not IQueryable.

var searchCriteria = new List<Tuple<string, string>>()
{
    new Tuple<string, string>("1", "blue"),
    new Tuple<string, string>("2", "blue"),
    new Tuple<string, string>("3", "red"),
    new Tuple<string, string>("3", "green")
};

var results = dataTable
    .Where(item => searchCriteria.Contains(new Tuple<string, string>(item.Id, item.Color)))
    .ToList();

Edit2:

Perhaps something along these lines would work.

var results = dataTable
    .Where(item => searchCriteria
        .Any(criteria =>
            criteria.Item1 == item.Id &&
            criteria.Item2 == item.Color
        )
    )
    .ToList();