C# – How to write the “Where” clause in the following LINQ to SQL Query

clinqlinq-to-sql

I'm working on the following LINQ query:

public void GetAuditRuleAgencyRecords(IEnumerable<Entities.AuditRule> rules)
{
    using (LinqModelDataContext db = new LinqModelDataContext())
    {
        var auditAgencyRecords = (from ag in db.Agencies
                        join ara in db.AuditRuleAccounts on ag.Agency_Id equals ara.AgencyID
                        join arr in db.AuditRuleResults on ara.AuditRuleAccountID equals arr.AuditRuleAccountID
                        join are in db.AuditRuleEnterprises on arr.AuditRuleEnterpriseID equals are.AuditRuleEnterpriseID
                        select new
                        {

                            AgencyID = ag.Agency_Id,
                            AgencyName = ag.Agency_Name,
                            AuditRuleEnterpriseID = arr.AuditRuleEnterpriseID,
                            CorrectedDate = arr.CorrectedDate,
                            NbrDaysToCorrect = arr.NbrDaysToCorrect,      

                        }).ToList();
    }
}

You can see that I'm passing in an IEnumerable rules. Each AuditRule object that I pass in has a property called "ID".

What would my where clause look like for this query if I want to say, only return the records where the table column AuditRuleEnterprise.AuditID matches any one of the ID's in my rules "ID" property (the objects I've passed into the method)?

Best Answer

Try:

.Where(rules.Select(r => r.ID).Contains(arr.AuditRuleEnterpriseID.AuditID))

or, in query syntax

where rules.Select(r => r.ID).Contains(arr.AuditRuleEnterpriseID.AuditID)
Related Topic