CODEWITHSUNDEEP
c#entity-framework-core.net-6.0
AnthonyWJones
AnthonyWJones

Reputation: 189505

Efficiently select a set of objects that have multi value keys

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?

Upvotes: 0

Views: 42

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27416

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;
}

Upvotes: 1

Related Questions