C# Using Linq Query with where clause as variable on dataTable

Question

I have seen different examples and questions but I cannot figure how to write this query successfully. The where clause condition is in filter.condition

  private object[] GetValueFromLookup(MultipleKeyConditionBuilder filter, string lookupValueField, DataTable datatableLookup)
    {

        Func<int, string> whereClause = test => filter.Condition;

        IEnumerable<object> query =
        from rows in datatableLookup.AsEnumerable().Where(whereClause)
        select rows.Field<object>(lookupValueField);
        return query.ToArray();

    }

I am getting this error. I have tried different things but unfortunately I cannot understand how to solve it.

cannot convert from 'System.func (int,string)' to 'System.func(system.Data.DataRow,int,bool)'

MultipleKeyConditionBuilder.This function gives the filter condition. It is defined as.

public MultipleKeyConditionBuilder(List<string> sourceKeyFieldsList, List<string> referenceKeyFieldsList, DataRow sourceRow) {}

filter.Condition gives a string for example "Project_id = 255454"

Edit your question and throw some light on `MultipleKeyConditionBuilder` — Sarvesh Mishra, Sep 05 '16 at 11:59
Can there be more than one condition? What will be value of filter.Condition in case of multiple filters? — Sarvesh Mishra, Sep 05 '16 at 12:17
yes it has more than 1 condition,. depending on the situation. ' Condition += referenceKeyFieldsList[i].ToValidSQLName() + " = " + filterChar + sourceRow[sourceKeyFieldsList[i]] + filterChar; ' — مسعود, Sep 05 '16 at 12:20
You should give a look at http://stackoverflow.com/questions/3501709/dynamic-linq-multiple-where-clause — Sarvesh Mishra, Sep 05 '16 at 12:21
And also http://stackoverflow.com/questions/848415/dynamic-where-clause-in-linq — Sarvesh Mishra, Sep 05 '16 at 12:23
hmm in my case its just a single string which has all conditions. I think i need to change my approach. this filter will not work for Linq — مسعود, Sep 05 '16 at 12:23
If you can change the filter creation then it is possible easily. — Sarvesh Mishra, Sep 05 '16 at 12:26

Ralf Bönning · Answer 1 · 2016-09-05T11:37:27.320

1

You can use a declaration like this

 Func<DataRow, bool> whereClause = test => filter.Condition;

I assume that filter.Condition returns a boolean value. I have tested this simplification:

 Func<DataRow, bool> whereClause = test => true;

Update:

filter.Conditionreturns a string with statements like property = 'value'. This has to evaluated with some function that takes this statement, inserts the values from the current DataRow and returns a boolean, like

    ...
    Func<DataRow, bool> whereClause = row => SomeClass.Evaluate(filter.Condition, row);
    ...

public static class SomeClass
{
     public static bool Evaluate(string expression, DataRow data)
     {
          ... do some sophisticated stuff ...
          return true /  false;
     }
}

But this solution is only fitting to give a direct answer to the question.

I suggest the redesign the MultipleKeyConditionBuilder that MultipleKeyConditionBuilder.Condition does not return a string but a Predicate<DataRow>. After that you can write

Func<DataRow, bool> whereClause = test => filter.Condition(test);

edited Sep 05 '16 at 11:37

answered Sep 05 '16 at 10:27

Ralf Bönning

14,515
5
49
67

filter.Condition returns a string – مسعود Sep 05 '16 at 10:30
now only the last part is different. how to change it to bool or the other to string? – مسعود Sep 05 '16 at 10:32
How is the MultipleKeyConditionBuilder defined? – Ralf Bönning Sep 05 '16 at 10:52
public MultipleKeyConditionBuilder(List sourceKeyFieldsList, List referenceKeyFieldsList, DataRow sourceRow) {} – مسعود Sep 05 '16 at 11:17
And `MultpileKeyConditionBuilder.Condition` ? – Ralf Bönning Sep 05 '16 at 11:17
its gives filter condition. for example the current value is "Project_Id = 254411" – مسعود Sep 05 '16 at 11:19
Is this a string ? – Ralf Bönning Sep 05 '16 at 11:20
Func whereClause = test => Convert.ToBoolean(filter.Condition); I tried this it gives String was not recognized as a valid Boolean. – مسعود Sep 05 '16 at 11:46
Thank you for your help. It looks complex but I will try something new – مسعود Sep 05 '16 at 12:34

score 0 · Answer 2 · answered Sep 05 '16 at 11:52

0

I don't know why you have complicated the things there.

This should do the job.

private object[] GetValueFromLookup(MultipleKeyConditionBuilder filter, string lookupValueField, DataTable datatableLookup)
{
    DataRow[] rows = datatableLookup.Select(filter.Condition);
    return rows.Select(r => r.Field<object>(lookupValueField)).ToArray();
}

answered Sep 05 '16 at 11:52

Sarvesh Mishra

2,014
15
30

The datatableLookup.Select is slow. I want to improve the performance. That's why I am using Linq. I tried dataview it was also not beneficial – مسعود Sep 05 '16 at 11:57
@Masood Edit your question and throw some light on `MultipleKeyConditionBuilder`. How you are preparing the filter? – Sarvesh Mishra Sep 05 '16 at 12:08
This function is adding strings to condition variable to make a condition: it is perfect for data table select statement but for linq its not working – مسعود Sep 05 '16 at 12:17

score 0 · Answer 3 · answered Oct 10 '16 at 17:03

System.Linq.Enumerable.Where expects a Func<T, bool> (i.e. a function that takes T as input and returns bool) meaning for your line whereClause = test => filter.Condition; to work filter.Condition should be C# code. In your case it seems to be a string.

If I understand what you are trying to do here then you should have your filter.Condition return an Expression<Func<DataRow, bool>>.

Following is a code snippet to help you understand how Expressions work.

        Expression<Func<string, bool>> conditionExpression = x => "MyString".Equals(x);

        // Following if block will not compile
        //if (conditionExpression("MyString")) 
        //    Console.WriteLine("True");
        //else
        //    Console.WriteLine("False");

        var condition = conditionExpression.Compile();

        // Following if block will compile.
        if(condition("MyString")) //this compiles.
            Console.WriteLine("True");
        else
            Console.WriteLine("False");

Hope this helps.

C# Using Linq Query with where clause as variable on dataTable

3 Answers3