CODEWITHSUNDEEP
c#linqentity-frameworkentity-relationshipiqueryable
victor_c
victor_c

Reputation: 171

Select categories where no product has inventory

I am trying to wrap my head around a Linq query to return a parent object only if all child objects contain a specific property.

I.e., as an example, return categories where all products linked to that category have product.Inventory == 0

Other title for this Q:
Select ParentObject where all ChildObjects have specific ChildObject.Parameter value

EDIT:
In addition to the relationship, I also only want to get a category if one of it's date properties is not null.

EDIT:
Here is one of the samples I have previously attempted:

var selectQuery = 
   (from statementDetail in pcardDatabaseContext.PCardStatementDetails
    where statementDetail.ExportedDate != null
    && statementDetail.PCardTransactions.All(txn => txn.TransactionStatusID == txnStatusAccountingApproved)
    orderby statementDetail.ExportedDate
    select statementDetail) as IOrderedQueryable<PCardStatementDetail>;

EDIT:
Found a solution for my problem, but can't self-answer for another 7 hours.

I had partially been experiencing some issues on an earlier syntax, I assumed that when using x.All the values wouldn't return any match if the set was empty.

Here is what solved it for me:

var selectQuery =
   (from statementDetail in pcardDatabaseContext.PCardStatementDetails
    where statementDetail.ExportedDate == null
    && statementDetail.PCardTransactions.All(txn => txn.TransactionStatusID == txnStatusAccountingApproved)
    && statementDetail.PCardTransactions.Any()
    orderby statementDetail.ExportedDate
    select statementDetail) as IOrderedQueryable<PCardStatementDetail>;

Please note that I had modified the ExportDate to only retrieve ExportedDate == NULL. Also, I had to add a .Any, otherwise I was getting records that had no Transactions (where I thought the .All would filter out).

Upvotes: 1

Views: 216

Answers (4)

victor_c
victor_c

Reputation: 171

Found a solution for my problem, but can't self-answer for another 7 hours.

I had partially been experiencing some issues on an earlier syntax, I assumed that when using x.All the values wouldn't return any match if the set was empty.

Here is what solved it for me:

var selectQuery =
   (from statementDetail in pcardDatabaseContext.PCardStatementDetails
    where statementDetail.ExportedDate == null
    && statementDetail.PCardTransactions.All(txn => txn.TransactionStatusID == txnStatusAccountingApproved)
    && statementDetail.PCardTransactions.Any()
    orderby statementDetail.ExportedDate
    select statementDetail) as IOrderedQueryable<PCardStatementDetail>;

Please note that I had modified the ExportDate to only retrieve ExportedDate == NULL. Also, I had to add a .Any, otherwise I was getting records that had no Transactions (where I thought the .All would filter out).

Upvotes: 0

Mark
Mark

Reputation: 487

Assuming your classes look something like this

public class Category
{
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

public class Product
{
    public string Name { get; set; }
    public int Inventory { get; set; }
}

then this will work (AllCategories() returns IEnumerable<Category>)

var categories = AllCategories().Where(c => c.Products.All(p => p.Inventory == 0));

Upvotes: 1

Thomas Levesque
Thomas Levesque

Reputation: 292455

var categoriesWithNoInventory =
    Categories.Where(c => c.Products.All(p => p.Inventory == 0));

Upvotes: 2

dreadlocks1221
dreadlocks1221

Reputation: 80

I think you would have to do 2 queries? the first to grab the child products and then to check the inventory, or I suppose you could do it with a join, here's a join example: http://www.dotnetperls.com/join

Upvotes: 0

Related Questions