CODEWITHSUNDEEP
c#entity-framework
user616076
user616076

Reputation: 4001

How do I use a variable with my WHERE clause in my Entity Framework query

I'm running a query in my project with multiple joins. I want to provide the WHERE clause with a variable instead of hard coded as it is now but cannot seem to get the correct syntax.

var data = (from a in db.StudentData
            join b in db.Contacts on a.SID equals b.SID
            join c in db.Addresses on a.SID equals c.SID
            join d in db.EntryQuals.DefaultIfEmpty() on a.SID equals d.SID                            
            where a.SID == searchTxt
                  select new
                    {
                        ID = a.SID,
                        Birthdate = a.BIRTHDTE,
                        FirstName = a.FNAMES,
                        PreviousName = a.PREVSURNAME,                                
                        EntryQualAwardID = d.ENTRYQUALAWARDID,
                        AwardDate = d.AWARDDATE
       }).ToList();

How can I get my WHERE clause to work with a variable (ie: a.[ fieldVar ] ) where fieldVar could be "SID" as it is in the code currently.

Upvotes: 1

Views: 1883

Answers (2)

Steve Py
Steve Py

Reputation: 34968

When dealing with user select-able search criteria you will need to code for the possible selections. When dealing with building searches I recommend using the Fluent syntax over the Linq QL syntax as it builds an expression that is easy to conditionally modify as you go. From there you can use a Predicate & PredicateBuilder to dynamically compose your WHERE condition.

Jacques solution will work, but the downside of this approach is that you are building a rather large & complex SQL statement which conditionally applies criteria. My preference is to conditionally add the WHERE clauses in the code to ensure the SQL is only as complex as it needs to be.

If you want to do something like a smart search (think Google with one text entry to search across several possible fields)

var whereClause = PredicateBuilder.False<StudentData>();
int id;
DateTime date;
if(int.TryParse(searchTxt, out id))
    whereClause = whereClause.Or(x => x.SID == id);
else if(DateTime.TryParse(searchTxt, out date))
    whereClause = whereClause.Or(x => x.BirthDate == date);
else
   whereClause = whereClause.Or(x => x.FirstName.Contains(searchTxt));

var data = db.StudentData
    .Where(whereClause)
    .Select(a => new
    {
        ID = a.SID,
        Birthdate = a.BIRTHDTE,
        FirstName = a.FNAMES,
        PreviousName = a.PREVSURNAME,                                
        EntryQualAwardID = a.EntryQuals.ENTRYQUALAWARDID,
        AwardDate = a.EntryQuals.AWARDDATE
    }).ToList();

This does some basic evaluations of the search criteria to see if it fits the purpose of the search. I.e. if they can search by name, date, or ID and IDs are numeric, we only search on an ID if the criteria was numeric. If it looked like a date, we search by date, otherwise we search by name. (and potentially other searchable strings)

If they can search for ID, FirstName, and BirthDate and enter one or more of those as separate entry fields (Search criteria page) then based on which entries they fill in you can either pass separate nullable parameters and do the above based on what parameters are passed, or pass a list of search values with something like an Enum for which value was searched for:

I.e. by parameters:

private void ByParameters(int? id = null, DateTime? birthDate = null, string name = null)
{
    var whereClause = PredicateBuilder.False<StudentData>();
    if(id.HasValue)
        whereClause = whereClause.Or(x => x.SID == id.Value);
    if(date.HasValue)
    {
        DateTime dateValue = date.Value.Date;   
        whereClause = whereClause.Or(x => x.BirthDate == dateValue);
    }
    if (!string.IsNullOrEmpty(name))
       whereClause = whereClause.Or(x => x.FirstName.Contains(name));

    // ....
}

If the number of parameters starts to get big, then a custom type can be created to encapsulate the individual null-able values. I.e.:

[Serializable]
public class SearchCriteria
{
    public int? Id { get; set; }
    public DateTime? BirthDate { get; set; }
    public string Name { get; set; }
}
private void ByParameters(SearchCriteria criteria)
{
    // ....
}

Or you can compose a more dynamic parameter list object with a criteria type and value but it starts getting more complex than it's probably worth.

Upvotes: 1

JacquesB
JacquesB

Reputation: 42689

You can't really do that in Linq, sine linq needs to know the the type of the field at compile time. A workaround would be something like

where (fieldVar=="SID" && a.SID == searchTxt) || 
      (fieldVar=="FNAMES" && a.FNAMES== searchTxt) || ...

This will also alert you at compile time if you are doing an illegal comparison, eg. comparing a date to a string.

Upvotes: 0

Related Questions