CODEWITHSUNDEEP
c#sql-serverlinq
TridentTrue
TridentTrue

Reputation: 193

LINQ subquery with multiple columns

I'm trying to recreate this SQL query in LINQ:

SELECT * 
FROM   Policies 
WHERE  PolicyID IN(SELECT PolicyID 
                   FROM   PolicyRegister 
                   WHERE  PolicyRegister.StaffNumber = @CurrentUserStaffNo 
                          AND ( PolicyRegister.IsPolicyAccepted = 0 
                                 OR PolicyRegister.IsPolicyAccepted IS NULL ))

Relationship Diagram for the two tables:

Relationship Diagram

Here is my attempt so far:

var staffNumber = GetStaffNumber();

var policyRegisterIds = db.PolicyRegisters
                        .Where(pr => pr.StaffNumber == staffNumber && (pr.IsPolicyAccepted == false || pr.IsPolicyAccepted == null))
                        .Select(pr => pr.PolicyID)
                        .ToList();

var policies = db.Policies.Where(p => p.PolicyID.//Appears in PolicyRegisterIdsList)

I think I'm close, will probably make two lists and use Intersect() somehow but I looked at my code this morning and thought there has to be an easier way to do this,. LINQ is supposed to be a more readble database language right?

Any help provided is greatly appreciated.

Upvotes: 1

Views: 700

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

Just use Contains:

var policies = db.Policies.Where(p => policyRegisterIds.Contains(p.PolicyID));

Also better store policyRegisterIds as a HashSet<T> instead of a list for search in O(1) instead of O(n) of List<T>:

var policyRegisterIds = new HashSet<IdType>(db.PolicyRegisters......);

But better still is to remove the ToList() and let it all happen as one query in database:

var policyRegisterIds = db.PolicyRegisters.Where(pr => pr.StaffNumber == staffNumber && 
                            (pr.IsPolicyAccepted == false || pr.IsPolicyAccepted == null));

var policies = db.Policies.Where(p => policyRegisterIds.Any(pr => pr.PolicyID == p.PolicyID));

Upvotes: 2

Related Questions