Building dynamic where clauses in LINQ to EF queries

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();

Please add image of the `db.Claims` entity – Paul Sullivan Feb 15 '13 at 19:32 — Paul Sullivan, Feb 15 '13 at 19:32

score 8 · Answer 1 · edited Dec 20 '22 at 09:24

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 });
}

Charles380 · Answer 2 · 2013-03-04T20:44:00.747

I personally really like to use PredicateBuilder because it still has the LINQ look and feel, and not some magic string. You can have all sorts of conditions then add the clause to there predicate and it will compile all nice and clean for you.

http://www.albahari.com/nutshell/predicatebuilder.aspx

Here is a cut up sample from my own code:

var predicate = PredicateBuilder.True<MarketingCabinetItem>();

//add vendor filter
if (vendorComboBox.SelectedValue != null && !String.IsNullOrEmpty(vendorComboBox.SelectedValue.ToString()))
{
    var vend = vendorComboBox.SelectedValue.ToString();
    predicate = predicate.And(m => m.Vendor == vend);
    vendPredicate.And(v => v.VendorName == vend);
}


//get all mkt item types in category
if (categoryComboBox.SelectedValue != null)
{
    var mktCatId = Guid.Parse(categoryComboBox.SelectedValue.ToString());
    predicate = predicate.And(p => p.CategoryCategoryId == mktCatId);
}

// get the marketing items using the inner and outer
var mktItems = (from mi in ctx.MarketingItem.AsExpandable()
                join mType in ctx.ItemType.AsExpandable() on mi.MarketingItemTypeId equals mType.Id
                join mktCat in ctx.Category.AsExpandable() on mType.MarketingItemCategoryId equals mktCat.Id
                join att in ctx.Attachment.AsExpandable() on mi.Id equals att.MarketingItemId
                join pri in ctx.Priority.AsExpandable() on mi.PriorityId equals pri.Id


                select new MarketingCabinetItem
                {
                   Id = mi.Id,
                   Title = mi.Title,
                   ItemTypeDescription = mType.Description,
                   PriorityLevel = pri.Level,
                   StartDate = mi.StartDate,
                   ExpirationDate = mi.ExpirationDate,
                   HasAttachments = att != null,
                   CategoryDescription = mktCat.Description
               }).Where(predicate).ToList();

Huh. I've seen this kind of thing before in other ORMs, but it didn't occur to me to try it in this instance. I'll look into it. Thanks! — birdus, Feb 15 '13 at 20:55
Looks like this won't work with LINQ to Entities. I get this error message: "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities." — birdus, Feb 15 '13 at 22:10
did you add .AsExpandable() because LINQKit does support LINQ to Entities, since the code snippet I posted was LINQ to Entities — Charles380, Feb 16 '13 at 01:01

Paul Sullivan · Answer 3 · 2013-02-15T19:30:25.383

4

You could/need to use dynamic linq

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

db.Where("Something = @0 And SomethingElse = @1", "Blah", 42)

Seems to be how it is handled (note the lack of need for string.Format)

edited Feb 15 '13 at 19:30

answered Feb 15 '13 at 19:07

Paul Sullivan

2,865
2
19
25

Adding that library and "using System.Linq.Dynamic" doesn't seem to make a difference. Still getting my original exception, even though my where clause looks similar to the one in the article. – birdus Feb 15 '13 at 19:15
If I do it that way, how can I build the where clause dynamically? The way you're showing (which I read about before), I'll have to build a half dozen complete LINQ statements (since I have a half dozen totally different where clauses), obviating any need for dynamically creating the where clause. Maybe I'm missing something. – birdus Feb 15 '13 at 19:38
Yes if you want to have a copletely dynamic query then String.Format is the way forward (i.e. being able to query different properties of the entity). From your simple example it was unclear how dynamic you needed to be. – Paul Sullivan Feb 15 '13 at 19:42
@birdus Please add image of the db.Claims entity – Paul Sullivan Feb 15 '13 at 19:42
@birdus - can you just create a standard List of Claims i.e. `List claims = new List{new Claim{//blah},new Claim{//blah}};` and see if the dynamic query still fails. If it doesn't we can start to isolate if it is something to do with EF – Paul Sullivan Feb 15 '13 at 20:02
Paul, I added what you suggested (it's in the question now) and it actually works. – birdus Feb 15 '13 at 20:54
@birdus Okay so this indicates something funny in EF playing havoc with the visibility of the members of the entity to the reflection code of the dynamic Linq. A fix is to take all the entities and copy them to List and perform your dynamic query against that. I think this is going to need a bit of reverse engineering of the Dynaimc Linq dll or a post to the EF/dynamic linq authors unfortunately :( I don't have the time to plough through it sorry – Paul Sullivan Feb 15 '13 at 22:24
@birdus Just one final question... you know your `db` EF datacontext... You are using a new context atomically in the method right i.e. not declaring it as a static or anything? – Paul Sullivan Feb 15 '13 at 22:29
The code in my search method is simply wrapped in this: using (var db = new URIntakeEntities()) { ... }. Thanks for your help, Paul. – birdus Feb 15 '13 at 23:24

Eng-Mohamed Abdulwahab · Answer 4 · 2013-05-20T13:34:33.800

    public class SearchField
    {
        public string Name { get; set; }
        public string @Value { get; set; }
        //public string Operator { get; set; }

        public SearchField(string Name, string @Value)
        {
            this.Name = Name;
            this.@Value = @Value;
            //Operator = "=";
        }
    }

    public class FilterLinq<T>
    {
        public static Expression<Func<T, Boolean>> GetWherePredicate(params SearchField[] SearchFieldList)
        {

            //the 'IN' parameter for expression ie T=> condition
            ParameterExpression pe = Expression.Parameter(typeof(T), typeof(T).Name);

            //combine them with and 1=1 Like no expression
            Expression combined = null;

            if (SearchFieldList != null)
            {
                foreach (var fieldItem in SearchFieldList)
                {
                    //Expression for accessing Fields name property
                    Expression columnNameProperty = Expression.Property(pe, fieldItem.Name);


                    //the name constant to match 
                    Expression columnValue = Expression.Constant(fieldItem.Value);

                    //the first expression: PatientantLastName = ?
                    Expression e1 = Expression.Equal(columnNameProperty, columnValue);

                    if (combined == null)
                    {
                        combined = e1;
                    }
                    else
                    {
                        combined = Expression.And(combined, e1);
                    }
                }
            }

            //create and return the predicate
            return Expression.Lambda<Func<T, Boolean>>(combined, new ParameterExpression[] { pe });
        }

    }

And You Can call it for any Class

ClinicEntities x = new ClinicEntities();

  dataGridView1.DataSource = x.Patient
                          .Where(
FilterLinq<Patient>.GetWherePredicate(
new SearchField("PatientNameEnglish", "Mona Mohamed Ali"), 
new SearchField("PatientHusbandName", "Ahmed Sallam Kareem"))).ToList();

Finally Thanks for bmused

score 1 · Answer 5 · answered Mar 02 '14 at 21:37

the simple way is to use LINQExtension with LINQKIT

using (var context = new workEntities() )
{

    Dictionary<string, List<string>> dictionary = new Dictionary<string, List<string>>();
    dictionary["Title"] = new List<string> {  
                    "Network Engineer", 
                    "Security Specialist", 
                    "=Web Developer"
                };
    dictionary["Salary"] = new List<string> { ">=2000" };
    dictionary["VacationHours"] = new List<string> { ">21" };
    dictionary["SickLeaveHours"] = new List<string> { "<5" };                
    dictionary["HireDate"] = new List<string> { 
                    ">=01/01/2000",
                    "28/02/2014" 
                };
    dictionary["ModifiedDate"] = new List<string> { DateTime.Now.ToString() };

    var data = context.Employee.CollectionToQuery(dictionary).ToList();
}

Building dynamic where clauses in LINQ to EF queries

5 Answers5

Linked