Delete Old Items from Product List Using LINQ

Question

We have a Suppliers class and a Products class. We wish to use Entity Framework Core to update our database when a supplier issues a new products catalog. Simplified classes:

public class Supplier
{
    public Guid SupplierId { get; set; }
    public List<Product> Products { get; } = new List<Product>();
}
public class Product
{
    public string ItemCode { get; set; }
    public decimal ItemCost { get; set; }
    public Guid SupplierId { get; set; }
    public Supplier Supplier { get; set; }
}

We wish to first delete items in the new catalog which are not in our old catalog. We try to find those items with this LINQ query

List<Product> discontinued = db.Products
.Where(e => !newCatalog
.Any(nc => (e.ItemCode == nc.ItemCode && e.SupplierId == nc.SupplierId))
.ToList();

We then delete using

db.Products.RemoveRange(discontinued);

However the query returns all products where Products.SupplierId != newCatalog.SupplierId. This deletes all products for other suppliers.

How do we formulate the LINQ query so that we only delete items that are discontinued in newCatalog?

Answer 1

The correct condition is

Where(e => e.SupplierId == supplierId && !newCatalog.Any(nc => nc.ItemCode == e.ItemCode))

which requires knowing the SupplierId of the passed products. It could be extracted in advance with something like this:

var supplierId = newCatalog.Select(e => e.SupplierId).Distinct().Single();

Here Select + Distinct + Single is simply to ensure all passed products have one and the same SupplierId. If you don't need such enforcement, you could simply take it from the first product:

var supplierId = newCatalog[0].SupplierId;

In both cases it's good to extract it and put it into variable outside of the query.

Another improvement could be to replace newCatalog.Any(nc => nc.ItemCode == e.ItemCode) condition which most likely is causing client evaluation with Contains based condition, hopefully converted to SQL IN (...) and evaluated on the server. To do that, again you could extract the new item codes into a new variable outside the query:

var newItemCodes = newCatalog.Select(nc => nc.ItemCode);

Then the final criteria inside the query would be:

Where(e => e.SupplierId == supplierId && !newItemCodes.Contains(e.ItemCode))