I'm trying to build a where clause dynamically in a LINQ query which references tables via EF, but I'm getting the following error:
'ClaimantLastName' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near member access expression, line 6, column 2.
Here is what I'm trying:
string whereClause = string.Format("ClaimantLastName = '{0}' and ClaimantSSN = '{1}'", lastName, ssn);
I've also tried it without the single quotes to no avail.
Here is the actual query:
return db.Claims.Where(whereClause).Select(
u => new AdvancedSearchResult
{
ClaimNumber = u.ClaimNumber,
.
.
.
Is what I'm trying to do possible? It seems really basic. Where am I going wrong?
UPDATE: Here is the Claim entity.
public static Claim CreateClaim(global::System.Int32 id, global::System.String claimantFirstName, global::System.String claimantLastName, global::System.String claimantSSN, global::System.DateTime dateOfInjury, global::System.String claimNumber, global::System.String claimantMiddleName, global::System.String claimantAddress1, global::System.String claimantAddress2, global::System.String claimantCity, global::System.String claimantState, global::System.String claimantZip, global::System.DateTime claimantDateOfBirth, global::System.String compensability, global::System.Boolean injuryType, global::System.String jurisdictionState, global::System.String status, global::System.String condition, global::System.String managingBranch, global::System.String bodyPart, global::System.String acceptedBodyPart, global::System.Boolean pGCase, global::System.String employersDefenseAttorney, global::System.String accidentDescription, global::System.String claimExaminerFirstName, global::System.String claimExaminerLastName, global::System.String claimExaminerEmail, global::System.String claimantAttorney, global::System.String workerId, global::System.String workerType)
{
Claim claim = new Claim();
claim.Id = id;
claim.ClaimantFirstName = claimantFirstName;
claim.ClaimantLastName = claimantLastName;
claim.ClaimantSSN = claimantSSN;
claim.DateOfInjury = dateOfInjury;
claim.ClaimNumber = claimNumber;
claim.ClaimantMiddleName = claimantMiddleName;
claim.ClaimantAddress1 = claimantAddress1;
claim.ClaimantAddress2 = claimantAddress2;
claim.ClaimantCity = claimantCity;
claim.ClaimantState = claimantState;
claim.ClaimantZip = claimantZip;
claim.ClaimantDateOfBirth = claimantDateOfBirth;
claim.Compensability = compensability;
claim.InjuryType = injuryType;
claim.JurisdictionState = jurisdictionState;
claim.Status = status;
claim.Condition = condition;
claim.ManagingBranch = managingBranch;
claim.BodyPart = bodyPart;
claim.AcceptedBodyPart = acceptedBodyPart;
claim.PGCase = pGCase;
claim.EmployersDefenseAttorney = employersDefenseAttorney;
claim.AccidentDescription = accidentDescription;
claim.ClaimExaminerFirstName = claimExaminerFirstName;
claim.ClaimExaminerLastName = claimExaminerLastName;
claim.ClaimExaminerEmail = claimExaminerEmail;
claim.ClaimantAttorney = claimantAttorney;
claim.WorkerId = workerId;
claim.WorkerType = workerType;
return claim;
}
UPDATE: Added Paul's suggested code as a trial. This actually works.
whereClause = string.Format("ClaimantLastName = \"{0}\" and ClaimantSSN = \"{1}\"", lastName, ssn);
List<URIntake.Claim> claims = new List<Claim>();
URIntake.Claim claim = new Claim();
claim.ClaimantFirstName = "Jay";
claim.ClaimantLastName = "Williams";
claim.ClaimantSSN = "654219870";
claim.ClaimantDateOfBirth = new DateTime(1993, 1, 2);
claims.Add(claim);
claim = new Claim();
claim.ClaimantFirstName = "Santa";
claim.ClaimantLastName = "Claus";
claim.ClaimantSSN = "012345678";
claim.ClaimantDateOfBirth = new DateTime(1893, 1, 2);
claims.Add(claim);
List<AdvancedSearchResult> selectedClaims = claims.AsQueryable().Where(whereClause).Select(
u => new AdvancedSearchResult
{
ClaimNumber = u.ClaimNumber,
DateOfBirth = u.ClaimantDateOfBirth,
DateOfInjury = u.DateOfInjury,
Denied = u.Compensability == "Denied"
}).ToList();
Here's an example using System.Linq.Expressions. Although the example here is specific to your Claim
class you can make functions like this generic and then use them to build predicates dynamically for all your entities. I've been using recently to provide users with a flexible search for entities on any entity property (or groups of properties) function without having to hard code all the queries.
public Expression<Func<Claim, Boolean>> GetClaimWherePredicate(String name, String ssn)
{
//the 'IN' parameter for expression ie claim=> condition
ParameterExpression pe = Expression.Parameter(typeof(Claim), "Claim");
//Expression for accessing last name property
Expression eLastName = Expression.Property(pe, "ClaimantLastName");
//Expression for accessing ssn property
Expression eSsn = Expression.Property(pe, "ClaimantSSN");
//the name constant to match
Expression cName = Expression.Constant(name);
//the ssn constant to match
Expression cSsn = Expression.Constant(ssn);
//the first expression: ClaimantLastName = ?
Expression e1 = Expression.Equal(eLastName, cName);
//the second expression: ClaimantSSN = ?
Expression e2 = Expression.Equal(eSsn, cSsn);
//combine them with and
Expression combined = Expression.And(e1, e2);
//create and return the predicate
return Expression.Lambda<Func<Claim, Boolean>>(combined, new ParameterExpression[] { pe });
}