Using variables to build a LinQ query?

Question

I don't think is possible but wanted to ask to make sure. I am currently debugging some software someone else wrote and its a bit unfinished.

One part of the software is a search function which searches by different fields in the database and the person who wrote the software wrote a great big case statement with 21 cases in it 1 for each field the user may want to search by.

Is it possible to reduce this down using a case statement within the Linq or a variable I can set with a case statement before the Linq statement?

Example of 1 of the Linq queries: (Only the Where is changing in each query)

var list = (from data in dc.MemberDetails
    where data.JoinDate.ToString() == searchField
    select new
        {
            data.MemberID,
            data.FirstName,
            data.Surname,
            data.Street,
            data.City,
            data.County,
            data.Postcode,
            data.MembershipCategory,
            data.Paid,
            data.ToPay
        }
).ToList();

Update / Edit:

This is what comes before the case statement:

string searchField = txt1stSearchTerm.Text;
string searchColumn = cmbFirstColumn.Text;
switch (cmbFirstColumn.SelectedIndex + 1)
{

The cases are then done by the index of the combo box which holds the list of field names.

possible duplicate of [LINQ - dynamic WHERE clause?](http://stackoverflow.com/questions/848415/linq-dynamic-where-clause) — GSerg, May 28 '14 at 07:32
@GSerg I don't think this is a duplicate question since the criteria are known at compile time, just not which ones are applicable at a given situation. There is nothing to gain by doing it `.Where("data.JoinDate == " + searchField)` over `.Where(x => x.JoinDate == searchField)` — Maarten, May 28 '14 at 07:39
@Maarten The users selects which fields to search by (out of 21 available fields). That is exactly the problem described in the linked question. Doing `Where`s manually would require a 21-bullet `switch`, which is what the OP currently has, as I understand. — GSerg, May 28 '14 at 07:53
I will take a look at the linked answer, I am a bit out of my depth with this project, I am a web developer not a C# developer so I am learning as I go here. Thanks for the help guys! — Someone, May 28 '14 at 07:55

score 1 · Answer 1 · answered May 28 '14 at 07:28

Given that where takes a predicate, you can pass any method or function which takes MemberDetail as a parameter and returns a boolean, then migrate the switch statement inside.

private bool IsMatch(MemberDetail detail)
{
    // The comparison goes here.
}

var list = (from data in dc.MemberDetails
    where data => this.IsMatch(data)
    select new
    {
        data.MemberID,
        data.FirstName,
        data.Surname,
        data.Street,
        data.City,
        data.County,
        data.Postcode,
        data.MembershipCategory,
        data.Paid,
        data.ToPay
    }
).ToList();

Note that:

You may look for a more object-oriented way to do the comparison, rather than using a huge switch block.
An anonymous type with ten properties that you use in your select is kinda weird. Can't you return an instance of MemberDetail? Or an instance of its base class?

This won't work with Entity Framework.. although, not specified, the SQL tag suggests to me that is what he's using — Martin Booth, May 28 '14 at 07:33
@MartinBooth: good point, I've forgotten about this one. I'll remove my answer if it appears to be the author's case. — Arseni Mourzenko, May 28 '14 at 07:36

score 1 · Answer 2 · answered May 28 '14 at 07:32

How are the different where statements handled, are they mutually excluside or do they all limit the query somehow?

Here is how you can have one or more filters for a same query and materialized after all filters have been applied.

var query = (from data in dc.MemberDetails
             select ....);

if (!String.IsNullOrEmpty(searchField))
    query = query.Where(pr => pr.JoinDate.ToString() == searchField);

if (!String.IsNullOrEmpty(otherField))
    query = query.Where(....);

return query.ToList();

Using variables to build a LinQ query?

2 Answers2