I have a list of incomplete product models. Everyone is missing an owner and a price. Can these deficiencies be filled with a single query to context? Without this foreach loop?

foreach(var item in products)
{
    item.Owner = context.Products.Where(x => x.Id == item.Id).Select(x => x.ProductOwner).FirstOrDefault();
    item.Price = context.Products.Where(x => x.Id == item.Id).Select(x => x.ProductPrice).FirstOrDefault();
}

I would like one query to fill in the missing fields in IEnumerable products

CodePudding user response：

// build list of Id for which we need data
var idsToUpdate = products.Select(o => o.Id).ToList();

var dataById = Context.Products
                      // get matching entries (server side)
                      .Where(x => idsToUpdate.Contains(x.Id))
                      // get only relevant data
                      .Select(x => new { x.Id, x.ProductOwner, x.Price })
                      // ensure uniqueness (server side, free if Id is a PK)
                      .DistinctBy(x => x.Id)
                      // we will not update db
                      .AsNoTracking()
                      // now client side
                      .AsEnumerable()
                      // arrange the data
                      .ToDictionary(x => x.Id, x => new { x.ProductOwner, x.Price });

foreach (var item in products)
{
    if (!dataById.TryGetValue(item.Id, out var data))
        continue;

    item.ProductOwner = data.ProductOwner;
    item.Price = data.Price;
}

If data is not many then try query once, maybe?

1. Select all the target id
2. Get all products from DB
3. Do as you please with data(two lists) you have

ref : Using LINQ, is it possible to output a dynamic object from a Select statement? If so, how?

CodePudding user response：

This is highly depends on the DataType of products. If this is a List, there is an method available, called ForEach.

If you are using something different, you have to write an extension method somewhere within your code. This can be something like this:

public static class EnumerableExtensions
{
    public static void ForEach<T>(this IEnumerable<T> values, Action<T> predicate)
    {
        foreach(var value in values)
        {
            predicate(value);
        }
    }       
}

Afterwards, you can use this extension method like LINQ:

products.ForEach(item => 
{
    var product = context.Products.Where(x => x.Id == item.Id);

    item.Owner = product.Select(x => x.ProductOwner).FirstOrDefault();
    item.Price = product.Select(x => x.ProductPrice).FirstOrDefault();
});

Hope this helps :-)