ASP.NET Core 2.2 - Simple DB Query Question

Question

Trying to query the db for a specific record, this record in the model has an ICollection associated with it. So here's an example:

Let's say a have a bunch of stores:

class StoreLocation {
    public int StoreId
    public string LocationName
    public ICollection<SaleItems> SaleItems
}

class SaleItems {
    public int SaleItemId
    public string ItemName
    public string ItemCost
}

So using entity framework...

How can I search for SaleItems costing less than $5 at a specific store that gets searched for?

var SaleItemsAtStore = _context.StoreLocations
.Where(location => location.StoreId == SomethingUserInputs

var CheapSaleItems = SaleItems...

....not sure where to go with this, or maybe I'm going in the total wrong direction to begin with.

Chris Pratt · Accepted Answer · 2019-04-23 17:40:34Z

You can do it via StoreLocation, but it will be inefficient, as you'll have to query out all SaleItems and then filter them in memory:

var store = await _context.StoreLocations.Include(x => x.SaleItems)
    .SingleOrDefaultAsync(x => x.StoreId == storeId);
var saleItems = store.SaleItems.Where(x => x.ItemCost < 5);

Alternatively, and better, you can explicitly load only the sale items you want, but you'll still have to query the store first, which means one unnecessary query:

var store = await_context.StoreLocations.FindAsync(storeId);
var saleItems = await _context.Entry(store)
    .Collection(x => x.SaleItems).Query()
    .Where(x => x.ItemCost < 5).ToListAsync();

The best approach would be to have an explict foreign key property on your SaleItem entity:

[ForeignKey(nameof(StoreLocation))]
public int StoreLocationId { get; set; }
public StoreLocation StoreLocation { get; set; }

Then, you can simply do:

var saleItems = await _context.SaleItems
    .Where(x => x.ItemCost < 5 && x.StoreLocationId == storeId).ToListAsync();

Collectives™ on Stack Overflow

ASP.NET Core 2.2 - Simple DB Query Question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related