C# LINQ single query to complete an incomplete model

Question

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

Answer 1

// 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?

Answer 2

Since "products" is coming from external service and "context.Products" is from your DB. Why don't you join "context.Products" with "products" and return properties of "products" by applying value for "owner" and "price".

Example

               var result = (from p in products 
                          join dbP in context.Products on dbP.Id == p.Id into gj 
                          from subDbP in gj.DefaultIfEmpty() 
                          select new { 
                              Owner = subDbP?.ProductOwner ?? string.Empty, 
                              Price = subDbP?.ProductPrice ?? string.Empty, 
                              Id = p.Id
                          }).ToList(); 

Answer 3

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 :-)