How to fetch a list from the database based on another list of a different object type?

c# entity-framework-core linq

Question

I have these two models:

public class Product
{
    public int Id { get; set; }
    public int ProductGroupId { get; set; }
    public int ProductGroupSortOrder { get; set; }
    // ... some more properties
    public ICollection<ProductInCategory> InCategories { get; set; }
}

public class ProductInCategory
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int ProductCategoryId { get; set; }
    public int SortOrder { get; set; }

    // Nav.props.:
    public Product Product { get; set; }
    public ProductCategory ProductCategory { get; set; }
}

Some of the Products are grouped together via the property ProductGroupId, and I want to be able to remove whole groups of Products from ProductInCategory in a single Db-query.

The controller method receives a product_id and a category_id, not a ProductGroupId.

For a single Product I have been using this query to remove it from the category:

ProductInCategory unCategorize = await _context.ProductsInCategories
    .Where(pic => pic.ProductId == product_id && pic.ProductCategoryId == category_id)
    .FirstOrDefaultAsync();

and then:

_context.Remove(unCategorize);
await _context.SaveChangesAsync();

Now, if I have a List<Product> that I want to remove from ProductsInCategories, what would the query look like?

I have tried this, but it fails on the .Any()-bit:

Product product = await _context.Products
    .Where(p => p.Id == product_id)
    .FirstOrDefaultAsync();

List<Product> products = await _context.Products
    .Where(g => g.ProductGroupId == product.ProductGroupId)
    .ToListAsync();

List<ProductInCategory> unCategorize = await _context.ProductsInCategories
    .Where(pic => pic.ProductId == products.Any(p => p.Id)
        && pic.ProductCategoryId == category_id)
    .ToListAsync();
1
1
7/4/2018 1:30:34 PM

Accepted Answer

The controller method receives a product_id and a category_id, not a ProductGroupId

The first question is why the method receives product_id while it needs to do something with ProductGroupId.

This smells to a bad design, but anyway, let first translate the product_id to the desired ProductGroupId (this will cost us additional db query):

int? productGroupId = await _context.Products
    .Where(p => p.Id == product_id)
    .Select(p => (int?)p.ProductGroupId)
    .FirstOrDefaultAsync();

if (productGroupId == null)
{
    // Handle non existing product_id 
}

The rest is a simply matter of accessing the navigation property inside the LINQ to Entities query, which will be translated by EF Core to the appropriate join inside the generated SQL query. No intermediate Product list is needed.

List<ProductInCategory> unCategorize = await _context.ProductsInCategories
    .Where(pic => pic.Product.ProductGroupId == productGroupId)
    .ToListAsync();
2
7/4/2018 11:25:24 AM

Popular Answer

I could suggest a code fix for what you want, but there's a better solution: not loading the data to begin with.

In your code example, you are loading the data from the database, before then telling EF to delete the loaded items. That's not efficient. There should be no reason to load the data, you should be able to simply execute the query without needing to load data.

As far as I'm aware, Entity Framework is not capable of "conditional deletion" (for lack of a better name), for example:

DELETE FROM People WHERE Name = 'Bob' 

If you want to delete items based on a particular column value (other than the entity's ID), you can't rely on Entity Framework unless you want to load the data (which eats performance).

There are two better options here:

1. Execute the SQL query yourself

context.Database.ExecuteSqlCommand(
        "DELETE FROM Products WHERE ProductGroupId = " + product.ProductGroupId 
      ); 

This is how I've always done it.

Sidenote: I'm expecting comments about SQL injection. To be clear: there is no danger of SQL injection here as product.ProductGroupId is not a string, and its value controlled by the developer, not the end user.
Nonetheless, I do agree that using SQL parameters is good practice. But in this answer, I wanted to provide a simple example to showcase how to execute a string containing SQL.

2. Find a library that enables you to delete without loading.

I only stumbled on this when googling just now. Entity Framework Extensions seems to have implemented the conditional delete feature:

context.Customers.Where(x => x.ID == userId).DeleteFromQuery();

In your case, that would be:

_context.Products.Where(g => g.ProductGroupId == product.ProductGroupId).DeleteFromQuery();

Sidenote:
I've always used Code First, and EF has always generated cascaded deletes for me automatically. Therefore, when you delete the parent, its children get deleted as well. I'm not sure if your database has cascaded deletes, but I am assuming default EF behavior (according to my experience).



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow