LINQ equivalent of WHERE IN clause-CodePudding

I have 3 tables Purchase, PurchaseBill, and PurchasePayment.

Purchase has one-to-many relationship with PurchaseBill
PurchaseBill has one-to-many relationship with PurchasePayment

I'm looking for a Linq (fluent) equivalent of this SQL Query

SELECT COUNT('') FROM PurchasePayment
WHERE PurchaseBillId IN (
   SELECT PurchaseBillId FROM PurchaseBill WHERE PurchaseId = @PurchaseId
)

Basically I want to know if any PurchasePayment record exist for certain @PurchaseId

Currently I'm using

                var bills = _context.PurchaseBill.Where(a => a.PurchaseId == obj.Id);

                foreach (var bill in bills)
                {
                    var hasPayment = _context.PurchasePayment.Any(a => a.PurchaseBillId == bill.Id);

                    if (hasPayment)
                        ctx.AddFailure("Cannot modify paid Purchase.");
                }

I tried
var hasPayment = _context.PurchasePayment.Any(w => bills.Contains(w.PurchaseBillId));
but it's not working. Is it possible to do it without looping in Linq?

CodePudding user response：

You can implement something like this:

var PurchaseId=1;
var result = _context.PurchasePayment.Where(x=>_context.PurchaseBill.Where(y=>y.PurchaseId==PurchaseId).Select(y=>y.PurchaseBillId).ToList().Contains(x.PurchaseBillId)).Count();

CodePudding user response：

Have you tried this:

var hasPayment = _context.PurchasePayment.Where(x => _context.PurchaseBill.Where(a => a.PurchaseId == obj.Id).Any(y => y.PurchaseBillId == x.PurchaseBillId)).Any();

I tried an example here: https://dotnetfiddle.net/uTs6pd

CodePudding user response：

I agree with Gert; you ought to have a PurchaseBill class with an ICollection<PurchasePayment> PurchasePayments property (linking onto the multiple payments) and an int PurchaseId property (linking back to a single purchase) and be able to do something like:

var hasPayment = context.PurchaseBills.Any(
  pb => pb.PurchaseId == obj.Id && pb.PurchasePayments.Any()
);

"Is there any purchasebill with PurchaseId blahblah and any PurchasePayment?"

Or, if you plan to do something with each bill, and its payments, something like:

var bills = context.PurchaseBills.Include(pb => pb.PurchasePayments);

foreach(var pb in bills){

  if(pb.PurchasePayments.Any()){
    //do something with the bill and its purchase here
  }
}

But don't do this if all you're going to do is add a message to an error list; it's a lot of data to download for the sake of looking through and finding one bill record that might have a payment record - that is better done by the first recommendation that asks the DB for a simple boolean - "is there any payment anywhere for this purchaseId?"