EFCore query to get amount of products for each vendor by product search term

Question

I'm stuck on a product search query for a price comparison website.

There are 3 Tables:

Product: ProductId, Title

Vendor: VendorId

ProductPrices: ProductId, VendorId, Price

ProductPrices is a mapping table for all vendor prices for a certain product.

Now I want to be able to search for a product like "sweater blue". This should get all vendors that selling products containing the word "sweater blue" and the number of products found for each vendor.

The output should be:

{[

{VendorA,Found:23},
{VendorB,Found:2},

}}

By now I only have this query for getting all vendors by a products search term:

            var query = Context.Products
                        .Join(Context.ProductPrices,
                            product => product.ProductId,
                            pprice => pprice.ProductId,
                            (product, pprice) => new { product, pprice })
                        .Join(Context.Vendors,
                            pprice2 => pprice2.pprice.VendorId,
                            vendor => vendor.VendorId,
                            (pprice2, vendor) => new { pprice2, vendor })
                        .Where(x=>x.pprice2.product.Title.Contains("sweater blue"))
                        .Distinct()
                        .Select(x=>new
                        {
                            x.vendor
                        });

I don't know how to get the counts found for each vendor in ProductPrices.

Thank you!

Answer 1

You could use just Product and productPrices tables, Like the following code:

Dictionary<string, string> result = (from product in Context.Products
    join productPrice in Context.ProductPrices on product.ProductId equals productPrice.ProductId
    where product.Title == "sweater blue"
    select new { VendorId = productPrice.VendorId, Tilte = product.Title }
    )
    .GroupBy(v => v.VendorId)
    .ToDictionary(k => k.Key, v => $"Found : {v.ToList().Count}");