Building dynamic where clauses in LINQ to EF queries

entity-framework linq

Question

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();
1
2
2/15/2013 8:54:19 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

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 });
}
8
2/15/2013 11:06:13 PM

Related Questions

LINQ to entities - Building where clauses to test collections within a many to many relationship

Summing it up......contentQuery.Where( content => content.Tags.Any(tag => tags.Any(t => t.Name == tag.Name)) ); ...So is that what you're expecting?...I'm a little confused.
c# entity-framework lambda linq many-to-many
asked by chakrit

Dynamic where clause in Linq to Entities

Linq's DeferredExecution to rescue. Linq query is not executed unless the data is requested from it....var prod = from p in ctxt.products.expand("items\details") select p; if (p1 != null) { prod = prod.Where(p => p.x == p1); } if (p2 != null) { prod = prod.Where(p => p.xx == p2); } // Execute the query var prodResult = prod.ToLi...
dynamic entity-framework linq where-clause
asked by amit_g

Dynamic LINQ DateTime Comparison String Building - Linq To Entities

It seems what I was trying to do is not possible with the current DynamicLINQ library. The reason it didn't work was well outlined below by Tilak....My solution was to modify the DynamicLINQ library to allow the query to be written as a string and passed to the where clause for Date/Time datatypes. The modification was found here by Paul Hatcher: ....
c# dynamic-linq entity-framework linq string
asked by Ricketts

Dynamic where clause (OR) in Linq to Entities

In the post ...here... I learned how to build a dynamic query using the deferred execution of Linq. But the query is actually using an ...AND... concatenation of the WHERE condition. ...How can I achieve the same query but with an OR logic?...Due to the Flags enum, the query should search for ...Username..., ...WindowsUsername... or ...both...:...
c# entity-framework linq linq-to-entities where-clause
asked by Thomas Zweifel

How to detect IsNull / NotNull when building dynamic LINQ expressions?

I'm building dynamic LINQ expression that is later evaluated. So for example if I want to know if certain property is equal to some value I do:...// MemberExpression property; // int? val; Expression.Equal(property, Expression.Constant(val)) ...However, I can't seem to find a way to detect if val Is Null or NOT Null. Can somebody recommend to me ho...
asp.net-mvc c# entity-framework linq
asked by nikib3ro

linq to entities dynamic where build from lambdas

Close. Unfortunately if you take a look inside each of your property lambdas, for instance.....t => t.FirstName t => t.LastName ...You will find they are each of ...Expression.Property.... However each of them has a different ...Expression.Parameter.... You want to use a ...ExpressionVisitor... to replace the ...PropertyExpression.Parameter... wit...
c# entity-framework expression-trees lambda linq
asked by Aron

Call custom function in EF LINQ query Where clause

Env: EF6 + Code First...I want to be able to call a custom function in the Where clause of a LINQ query...So this line:...var activeStaff = Repo.Staff.Where(s => s.EndDate == null || s.EndDate.Value > DateTime.Today); ...becomes:...var activeStaff = Repo.Staff.Where(s => MyEdmFunctions.IsCurrentStaff(s)); ...This is what I have tried, ...public cla...
code-first ef-code-first entity-framework-6 linq
asked by ozstudent

Building dynamic query in a loop using Expression trees

I have a system that allows different criteria pertaining to Sales to be stored in the database. When the criteria are loaded, they are used to build a query and return all applicable Sales. The criteria objects look like this:...ReferenceColumn (The column in the Sale table they apply to)...MinValue (Minimum value the reference column must be)...M...
c# entity-framework expression-trees linq
asked by Valuator

Why does this linq query fail when I change the select clause to select new Model

Because you are using ...LINQ To SQL... here which means, that query is translated to ...SQL... that has no idea about your custom types.... var rooms = (from m in db.Rooms where m.FK_HotelID == id select m).ToList(); var roomsVm = from m in rooms select new Room ...
ado.net-entity-data-model asp.net-mvc-5 c# entity-framework-6 linq
asked by madoxdev

C# convert sql query into EF dbcontext linq to entities query

I have an sql server database and I use EF 6.0 to access it in my app....I have the following SQL Query I need to convert to dbcontext linq to entities query and have some damn hard time figure it out....This is the query:...select PA.Number, PA.Name, PR.* from MYDBNAME.dbo.Product PR join MYDBNAME.dbo.Order OD on PR.Id = OD.Id join MYDBNAME...
c# entity-framework-6 linq linq-to-entities sql
asked by user2818430

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow