Linq Subquery Where Clause

Question

I need some help with thsi linq query. It shoudl be fairly simple, but it is kicking my butt.

I need to use a subquery to filter out data from the main query, but every path I have tried to use results in failure.

The subquery by itself looks like this.

int pk = (from c in context.PtApprovedCertifications
         where c.FkosParticipant == 112118 &&
         (!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
         c.EffectiveDate <= DateTime.Now &&
         c.FkptApprovedCertificationVoidedBy == null
         orderby c.EffectiveDate descending,c.PK descending
         select c.PK).FirstOrDefault();

This works as expected but as you can see I plugged in the number 112118. This should be the primary key from main query.

The combined query I've been working on looks like this.

IQueryable<PtAMember> result = (from p in context.PtAMembers
    where (p.FkptACertification == (from c in context.PtApprovedCertifications
        where c.FkosParticipant == p.PtApprovedCertification.OsParticipant.PK &&
        (!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
        c.EffectiveDate <= DateTime.Now &&
        c.FkptApprovedCertificationVoidedBy == null
        orderby c.EffectiveDate descending, c.PK descending
        select c.PK).FirstOrDefault()) &&
            (p.LastName.ToLower().Contains(param.ToLower()) ||
            p.FirstName.ToLower().Contains(param.ToLower()) ||
            p.SocialSecurityNumber.Contains(param))
    select p).Distinct().OrderBy(PtAMembers => PtAMembers.LastName).ThenBy(PtAMember => PtAMember.FirstName);

This results in an error though. Any help in solving this conundrum would greatly appreciated.

Thanks!

Answer 1

How about turning your subquery into a lookup function:

Func<int, int> pkLookup = n => (from c in context.PtApprovedCertifications
                      where c.FkosParticipant == n &&
                      (!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
                      c.EffectiveDate <= DateTime.Now &&
                      c.FkptApprovedCertificationVoidedBy == null
                      orderby c.EffectiveDate descending,c.PK descending
                      select c.PK).FirstOrDefault();

then using that in your main query.