Efficiently select a set of objects that have multi value keys

Question

I have a ProductVersions model which has a multi part key, a int ProductId and a int VersionNum field.

The function below takes a list of simple Dto classes that are just these 2 fields with goal of returning a set full ProductVersion objects from the database that match.

Below is a less than efficient solution. I am expecting the incoming list to only between 2 to 4 items so its not too bad but I'd like to do better.

    private async Task<List<ProductVersion>?> GetProductVersionsFromDto(IList<ProductVersionDto>? productVersionDtos)
    {
        List<ProductVersion>? productVersions = null;

        if (productVersionDtos != null)
        {
            foreach (ProductVersionDto dto in productVersionDtos)
            {
                ProductVersion? productVersion = await myPortalDBContext.ProductVersions
                    .Where(pv => pv.ProductId == dto.ProductId && pv.VersionNum == dto.VersionNum)
                    .FirstOrDefaultAsync();

                if (productVersion != null)
                {
                    if (productVersions == null) productVersions = new List<ProductVersion>();

                    productVersions.Add(productVersion);
                }
            }
        }

        return productVersions;
    }

I had consider something like this:

    private async Task<List<ProductVersion>?> GetProductVersionsFromDto(IList<ProductVersionDto>? productVersionDtos)
    {
        List<ProductVersion>? productVersions = null;

        if (productVersionDtos != null)
        {
            productVersions = await myPortalDBContext.ProductVersions
                .Join(productVersionDtos,
                    pv => new { pv.ProductId, pv.VersionNum },
                    pvd => new { pvd.ProductId, pvd.VersionNum },
                    (pv, pvd) => pv)
                .ToListAsync();
        }

        return productVersions;
    }

but at runtime fails because the Join doesn't make sense. Anyone know of way to do this more efficiently with just a single round-trip into the dbContext?

Does this answer your question? [Lambda/Linq with Contains criteria for multiple keywords](https://stackoverflow.com/questions/67666649/lambda-linq-with-contains-criteria-for-multiple-keywords) — JHBonarius, Apr 07 '22 at 14:23

score 1 · Answer 1 · answered Apr 07 '22 at 13:55

1

Use FilterByItems extension and then you can generate desired query:

private async Task<List<ProductVersion>?> GetProductVersionsFromDto(IList<ProductVersionDto>? productVersionDtos)
{
    if (productVersionDtos == null)
        return null;

    var productVersions = await myPortalDBContext.ProductVersions
        .FilterByItems(productVersionDtos, 
           (pv, dto) => pv.ProductId == dto.ProductId && pv.VersionNum == dto.VersionNum, true)
        .ToListAsync();

    return productVersions;
}

answered Apr 07 '22 at 13:55

Svyatoslav Danyliv

21,911
3
16
32

Thanks I'll take look but it really shouldn't be this hard. EF is been around a long time and multi value keys have existed since the dawn of the database. – AnthonyWJones Apr 07 '22 at 15:04
Actually not, you can try `Any` operator with local collection and get not translatable exception. – Svyatoslav Danyliv Apr 07 '22 at 15:56

Efficiently select a set of objects that have multi value keys

1 Answers1