How to SELECT WHERE NOT EXIST using LINQ?

/*
#Where EXISTS
=====================
*/
from p in tblProjectInfo
where tblProjectFundingCommitment.Any(pfc=>(pfc.ProjectId==p.Id)&&(pfc.FundSourceId==44))
select p

tblProjectInfo
   .Where (
      p => 
         (tblProjectFundingCommitment
               .Any (pfc => ((pfc.ProjectId == p.Id) && (pfc.FundSourceId == 44)))
            )
   )

//--With Optional Parameters
//---------------------------
int intIsUnderADP = 0/1/2; //we will skip comaping if value is 2(not a boolean)
bool isUnderADP = Convert.ToBoolean(intIsUnderADP);
int managingDPId = [any integer value starts from 0]; //we will skip comaping if 0

tblProjectInfo
 .Where(
                p => ((intIsUnderADP == 0 || intIsUnderADP == 1) ? p.IsUnderADP == isUnderADP : true)
                    && (context.tblProjectFundingCommitment.Any(pfc => ((pfc.ProjectId == p.Id) && ((managingDPId > 0) ? 

pfc.FundSourceId == managingDPId : true))))
                    && (!string.IsNullOrEmpty(p.DPProjectNo))
 )

/*
#Where NOT EXISTS
=====================
*/
from p in tblProjectInfo
where !tblProjectFundingCommitment.Any(pfc=>(pfc.ProjectId==p.Id)&&(pfc.FundSourceId==44))
select p

tblProjectInfo
   .Where (
      p => 
         !(tblProjectFundingCommitment
               .Any (pfc => ((pfc.ProjectId == p.Id) && (pfc.FundSourceId == 44)))
            )
   )

//--With Optional Parameters
//---------------------------
int intIsUnderADP = 0/1/2; //we will skip comaping if value is 2(not a boolean)
bool isUnderADP = Convert.ToBoolean(intIsUnderADP);
int managingDPId = [any integer value starts from 0]; //we will skip comaping if 0

tblProjectInfo
 .Where(
                p => ((intIsUnderADP == 0 || intIsUnderADP == 1) ? p.IsUnderADP == isUnderADP : true)
                    && !(context.tblProjectFundingCommitment.Any(pfc => ((pfc.ProjectId == p.Id) && ((managingDPId > 0) ? 

pfc.FundSourceId == managingDPId : true))))
                    && (!string.IsNullOrEmpty(p.DPProjectNo))
 )

Comments

Popular posts from this blog

Crystal Report FAQ Error and Fixes

Export DataTable or DataSet to CSV or XML

Linux Command ref