Ignoring Empty strings when searching with LINQ-to-Entities

Question

When I run this code, I am checking that before I add a new record, there isn't already an existing one that matches the Bank account number, the VAT number or the Paypal email address...

But, if the Text boxes are empty, it is matching the first record that has an empty field in the database. eg. If txtBankAcctNum.text = "" it matches the first record in the db where there is no bank account number.

What I need it to do is only try matching the textbox.text to the db field if there is text present !

I'd be able to do this in a stored procedure, no problems, but I'm still learning this LINQ stuff. Can anyone help ?

billingDetail = db.BillingDetails.FirstOrDefault(bd => bd.BankAcctNum == txtBankAcctNum.Text.Trim() 
                || bd.PayPalEmailAddress == txtPayPalEmailAddress.Text.Trim()
                || bd.VATnum == txtVATnum.Text.Trim());

Answer 1

This may do the trick

bool bankAcctNumIsValid = !string.IsNullOrWhiteSpace(txtBankAcctNum.Text.Trim());
bool payPalEmailAddressIsValid = !string.IsNullOrWhiteSpace(txtPayPalEmailAddress.Text.Trim());
bool vatNumIsValid = !string.IsNullOrWhiteSpace(txtVATnum.Text.Trim());

billingDetail = db.BillingDetails
 .FirstOrDefault(bd => 
    (bankAcctNumIsValid && bd.BankAcctNum == txtBankAcctNum.Text.Trim()) || 
    (payPalEmailAddressIsValid && bd.PayPalEmailAddress == txtPayPalEmailAddress.Text.Trim()) ||
    (vatNumIsValid && bd.VATnum == txtVATnum.Text.Trim())
 );

Answer 2

You can build up the expression tree conditionally, prior to materialization, like so:

var billingDetailQuery = db.BillingDetails.Where(bd => true);

if (!String.IsNullOrEmpty(txtBankAcctNum.Text))
{
  billingDetailQuery = billingDetailQuery
           .Where(bd => bd.BankAcctNum == txtBankAcctNum.Text.Trim());
}
// Same for Email and VAT

var result = billingDetailQuery.FirstOrDefault();

This approach typically results in more focused SQL execution plans and avoids parameter sniffing issues i.e. where @x is NULL or @x = table.field filters, and also avoids mapping issues in the predicate lambdas like this.