Dynamic Linq Query EF c#

Question

I want to create dynamic linq query c#

I do googling lot but not get exact solution

I have candidate with following field(listed few)
CandidateId
JobTitleId
CityId
DepartmentId

I want to create linq query with dynamic data like

var idArray=[1,2,3,4]
var fieldName='CityId' (may be any other of candidate Table)

I need dynamic query like candidateCityId(or other) Contains in idArray

I have more than 50 field in candidate table so it's not possible to write for every field

This could help http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library — Davor Zlotrg, Feb 24 '15 at 11:43

score 0 · Answer 1 · answered Feb 24 '15 at 12:22

I was facing the same issue during developing a search window for inventory. I also had searched a lot on web but no success. I had tackled this problem as below.

Following is the window for my search:

enter image description here

Here you can see that there are 6 comboboxes and each have four options like:

  <ComboBoxItem IsSelected="True">Contains</ComboBoxItem>
                                <ComboBoxItem>Does Not Contain</ComboBoxItem>
                                <ComboBoxItem>Begins With</ComboBoxItem>
                                <ComboBoxItem>Ends With</ComboBoxItem>

I had solved this problem by storing the selection and value in list as:

public class FilterList
{
    public string combobox { get; set; }
    public string value { get; set; }
}

On search button click:

 List<FilterList> filter = new List<FilterList>();
            filter.Add(new FilterList { combobox = cmbPart.Text, value = txtPart.Text });
            filter.Add(new FilterList { combobox = cmbDescription.Text, value = txtDescription.Text });
            filter.Add(new FilterList { combobox = cmbVendor.Text, value = txtVendor.Text });
            filter.Add(new FilterList { combobox = cmbVendorPart.Text, value = txtVendorPart.Text });
            filter.Add(new FilterList { combobox = cmbManufacture.Text, value = txtManufacture.Text });
            filter.Add(new FilterList { combobox = cmbManuPartNumber.Text, value = txtManuPartNumber.Text });

Now in list i am having all the searched criteria with value. Now i have to filter record from database. For this first i am selecting all records from database, then using switch according to list items like:

        if (!string.IsNullOrEmpty(filter[0].value))
                {
                    switch (filter[0].combobox)
                    {
                        case "Contains":
                        break;
                        case "Does Not Contain":
                        break;
                 }}
                if (!string.IsNullOrEmpty(filter[1].value))
                {
                    switch (filter[1].combobox)
                    {
                        case "Contains":
                        //code
                 }}

in cases you can use different queries on the list which had got from database.

Overall you can say it is impossible to create runtime query in linq as we can do in sql.

Hope this will help you.

But have can I write linq query using where with more than one field dynamically.. — Lalji Kanjareeya, Feb 25 '15 at 09:08
No,you have to write where condition on the basis of cases. This is the original post which i had post regarding this http://stackoverflow.com/questions/23557509/use-dynamic-linq-library-with-join — Sunny, Feb 26 '15 at 04:26
Great can you post your ans here. So that in future we will refer your solution. — Sunny, Mar 02 '15 at 04:20

score 0 · Accepted Answer · answered Mar 02 '15 at 11:37

var answerList = new List(){1,2,3,4};//from DB 

var fieldName= FieldDBPath; //from db //eg. CandidateId 

var queryableData = _dbEntities.Candidates.Where("@0.Contains(outerIt."+fieldName + ")", answerList).ToList();

This means that it fetch the all candidate with CandidateId Contains in answerList.

Dynamic Linq Query EF c#

2 Answers2