CODEWITHSUNDEEP
c#linqlambda
JohnnyCage
JohnnyCage

Reputation: 175

Lambda Expression for Not In a Collection

I have 3 tables

eTrip

Country | eProfile

eProfile

Documents (collection of Document)

Documents

Type | ExpiryDate | Country

I am trying to get a collection of eTrips in a search api. There are several conditions that need to be met for an eTrip.

Each employee can hold multiple documents (visas, passports, etc). For an eTrip to be valid we need to make sure the eTrip.Country != the country of a valid passport (future expiry date) document.

How can we write a lambda expression to accomplish this?

The code that I have so far is something like this

    var query = context.eTrip.AsQueryable();
    query = query.Where(e => e.validTrip == true);
    var docs = query.Select(e => e.eProfile.Documents);
        foreach (Documents d in docs)
        {
            if (d.DocumentTypeCode == "Passport" && d.ExpiryDate != null && d.ExpiryDate > DateTime.Now)
            {
               query = query.Where(e => e.Country != d.Country);
            }
        }

I need to write the filter for the country now and I am not sure how we can do it for a collection.

Upvotes: 0

Views: 159

Answers (2)

myroman
myroman

Reputation: 562

Try that:

        // your part
        var query = context.eTrip.AsQueryable();
        query = query.Where(e => e.validTrip == true);
        var docs = query.Select(e => e.eProfile.Documents);

        // get countries for which there are no documents with future date
        var myCountryQuery = query.Where(x => !docs
            .Where(d => d.DocumentTypeCode == "Passport" && d.ExpiryDate != null && d.ExpiryDate > DateTime.Now)
            .Any(d => d.Country != x.Country)
            );

Upvotes: 0

Xaver
Xaver

Reputation: 393

you can extend your Where clause with an Any subquery on Documents

query = query.Where(e => e.validTrip == true && e.eProfile.Documents.Any(a=>a.DocumentTypeCode == "Passport" && a.ExpiryDate.HasValue && a.ExpiryDate.Value > DateTime.Now && e.Country!=d.Country));

Upvotes: 1

Related Questions