score:1

Accepted answer
[httppost]
public jsonresult checkforexistingreferral(referralviewmodel viewmodel)
{
    bool haspreviousrequest = false;
    var candidateid = user.identity.getuserid();
    // do an outer join between the tables on referralid and select only a new anonymous type that has referrerid
    // and status. if no record found in referralinstances then set status to empty.
    var result = (from r in  _context.referrals
                 join ri in _context.referralinstances on r.referralid equals ri.referralid into refsinst
                 where ((ri.candidateid == candidateid) && 
                        (ri.companyid == viewmodel.companyid) && 
                        (ri.skillid == viewmodel.skillid))
                 from rs in refsinst.defaultifempty() 
                 select new {referenceequals = rs.referrerid,  status = rs == null ? "":rs.referralstatus})
                .tolist(); 
    // this covers third condition            
    if(result.any(p => p.referrerid != null && p.status == "accepted"))  
    {
        haspreviousrequest = false;
    }
    // this covers first and second conditions. if nothing found in referralinstances, the status will be empty
    if(result.any() && result.all(p => p.status != "accepted")) 
    {
        haspreviousrequest = true;
    }  

    return json(new { haspreviousrequest = haspreviousrequest });
}

Related Query

More Query from same tag