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

How are you initializing `products`? You probably want to [Include](https://learn.microsoft.com/en-us/ef/ef6/querying/related-data) `ProductOwner` and `ProductPrice`. — 41686d6564 stands w. Palestine, May 13 '22 at 07:58
@41686d6564 stands w. Palestine I don't want to use Include. The products come from an external service and I have to supplement them with these 2 properties (Owner and Price) from my DB — EmilOS, May 13 '22 at 08:04

score 1 · Accepted Answer · edited May 13 '22 at 08:32

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?

Select all the target id
Get all products from DB
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?

edited May 13 '22 at 08:32

Orace

7,822
30
45

answered May 13 '22 at 08:06

user3682728

467
3
7

Bad formatting. Useless call to `ToList`. Inefficient join. `targetProduct` can be null, which leads to a crash. – Orace May 13 '22 at 08:10
@Orace Which call to `ToList()` is useless? – Johnathan Barclay May 13 '22 at 08:12
@Orace Thank ,could you please guide me about Bad formatting , Useless call to ToList and Inefficient join. – user3682728 May 13 '22 at 08:13
Using `Where` on a List is inefficient. You could create a `Dictionary` with `.ToDictionary(x=>x.Id,x =>new { x => x.Id ,x.ProductOwner , x.Price})` and use that in the loop to look up products by ID – Panagiotis Kanavos May 13 '22 at 08:15
@user3682728 You shouldn't have edited out the `ToList()`; it's now executing the query each iteration. – Johnathan Barclay May 13 '22 at 08:18
@PanagiotisKanavos Thank you , I didn't even know this in my career. – user3682728 May 13 '22 at 08:18
Instead of `Where(condition).FirstOrDefault()` you can use just `.FirstOrDefault(condition)`. That's just as slow though because it will have to iterate over the entire list to find a matching object – Panagiotis Kanavos May 13 '22 at 08:20
p.s. After many edits and opinions , I wish OP found his solution. – user3682728 May 13 '22 at 08:35
code looks more optimized but throws an exception when run: `The LINQ expression 'DbSet()\r\n .Where(p => __Ids_0.Contains(Convert.ToString(p.Id)))\r\n .Select(x => new { \r\n Id = x.Id, \r\n ProductOwner = x.ProductOwner, \r\n Price = x.Price\r\n })\r\n .DistinctBy(x => x.Id)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.` – EmilOS May 13 '22 at 08:59
@EmilOS It seem on one here remember issue about using Contain in Linq and entity framework ,sorry that I'm not fluent with the solution but please refer to this https://stackoverflow.com/questions/68737681/the-linq-expression-could-not-be-translated-either-rewrite-the-query-in-a-form and https://stackoverflow.com/questions/374267/contains-workaround-using-linq-to-entities – user3682728 May 13 '22 at 09:35
Why AsEnumerable and ToDictionary? – Caius Jard May 13 '22 at 11:09

score 0 · Answer 2 · answered May 13 '22 at 09:21

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();

score -1 · Answer 3 · answered May 13 '22 at 08:21

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

This still performs 2n queries. – Johnathan Barclay May 13 '22 at 08:22 — Johnathan Barclay, May 13 '22 at 08:22

C# LINQ single query to complete an incomplete model

3 Answers3