Clever Geek Handbook

How to write Linq.Expression for IEnumerable.Any

I am writing queries in my code Linq to Sql

. I'm not very familiar with expressions, I want to write an expression that checks if IEnumerable

any element satisfies a condition

public class TestClass1
{
    // can be any number of properties, with diff names and types
    public string TestProperty1 { get; set; }
    public string TestProperty2 { get; set; }
    public List<TestClass1ExtraFieldValue> TestClass1ExtraFieldValueList { get; set; }
}

public class TestClass1ExtraFieldValue
{
    public TestClass1ExtraField TestClass1ExtraField { get; set; }

    // property names below are same in all classes which name ends with ExtraFieldValue
    public int IntValue { get; set; }
    public bool BoolValue { get; set; }
    public DateTime DateTimeValue { get; set; }
    public string StringValue { get; set; }
}

public class TestClass1ExtraField
{
    // property names are same in all classes which name ends with ExtraField
    public string Property1 { get; set; }
    public string Property2 { get; set; }
}

public class TestClass2
{
    // can be any number of properties, with diff names and types
    public string TestProperty1 { get; set; }
    public string TestProperty2 { get; set; }
    public List<TestClass2ExtraFieldValue> TestClass2ExtraFieldValueList { get; set; }
}

public class TestClass2ExtraFieldValue
{
    public TestClass2ExtraField TestClass2ExtraField { get; set; }

    // property names below are same in all classes which name ends with ExtraFieldValue
    public int IntValue { get; set; }
    public bool BoolValue { get; set; }
    public DateTime DateTimeValue { get; set; }
    public string StringValue { get; set; }
}

public class TestClass2ExtraField
{
    // property names are same in all classes  which name ends with ExtraField
    public string Property1 { get; set; }
    public string Property2 { get; set; }
}

public class TestClass3
{
    // can be any number of properties, with diff names and types
    public string TestProperty1 { get; set; }
    public string TestProperty2 { get; set; }
    public List<TestClass3ExtraFieldValue> TestClass3ExtraFieldValueList { get; set; }
}

public class TestClass3ExtraFieldValue
{
    public TestClass3ExtraField TestClass3ExtraField { get; set; }

    // property names below are same in all classes which name ends with ExtraFieldValue
    public int IntValue { get; set; }
    public bool BoolValue { get; set; }
    public DateTime DateTimeValue { get; set; }
    public string StringValue { get; set; }
}

public class TestClass3ExtraField
{
    // property names are same in all classes which name ends with ExtraField
    public string Property1 { get; set; }
    public string Property2 { get; set; }
}

// NOT a DB class
public class ExtraFieldClass
{
    public string Property1 { get; set; }
    public string Property2 { get; set; }
    public int IntValue { get; set; }
    public bool BoolValue { get; set; }
    public DateTime DateTimeValue { get; set; }
    public string StringValue { get; set; }
}

I have query classes as shown below:

public class TestClass1Query
{
    public string TestValue1;
    public string TestValue2;

    List<ExtraFieldClass> ExtraFieldsList { get; set; }

    public IQueryable GetQuery()
    {
        var query = GetTestClass1Values().Where(c=> c.TestProperty1==TestValue1 && c.TestProperty2 == TestValue2);
        // some condition checks
        foreach (var extraField in ExtraFieldsList)
        {
            switch (extraField.Type)
            {
                    case FieldType.Boolean:
                    {
                        var boolValue = Convert.ToBoolean(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass1ExtraFieldValueList.Any(t => t.TestClass1ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass1ExtraField.Property2 == extraField.Property2
                                                                                      && t.BoolValue == boolValue));
                        break;
                    }

                    case FieldType.DateTime:
                    {
                        var dateTimeValue = Convert.ToDateTime(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass1ExtraFieldValueList.Any(t => t.TestClass1ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass1ExtraField.Property2 == extraField.Property2
                                                                                      && t.DateTimeValue == dateTimeValue));
                        break;
                    }

                    case FieldType.Text:
                    {
                        var stringValue = Convert.ToString(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass1ExtraFieldValueList.Any(t => t.TestClass1ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass1ExtraField.Property2 == extraField.Property2
                                                                                      && t.StringValue == stringValue));
                        break;
                    }

                    case FieldType.Integer:
                    {
                        var integerValue = Convert.ToInt32(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass1ExtraFieldValueList.Any(t => t.TestClass1ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass1ExtraField.Property2 == extraField.Property2
                                                                                      && t.IntValue == integerValue));
                        break;
                    }
            }
        }
        return query;
    }

    private IQueryable<TestClass1> GetTestClass1Values()
    {
        return null;
    }
}

public class TestClass2Query
{
    public string TestValue1;
    public string TestValue2;

    List<ExtraFieldClass> ExtraFieldsList { get; set; }

    public IQueryable GetQuery()
    {
        var query = GetTestClass2Values().Where(c => c.TestProperty1 == TestValue1 && c.TestProperty2 == TestValue2);
        // some condition checks
        foreach (var extraField in ExtraFieldsList)
        {
            switch (extraField.Type)
            {
                case FieldType.Boolean:
                    {
                        var boolValue = Convert.ToBoolean(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass2ExtraFieldValueList.Any(t => t.TestClass2ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass2ExtraField.Property2 == extraField.Property2
                                                                                      && t.BoolValue == boolValue));
                        break;
                    }

                case FieldType.DateTime:
                    {
                        var dateTimeValue = Convert.ToDateTime(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass2ExtraFieldValueList.Any(t => t.TestClass2ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass2ExtraField.Property2 == extraField.Property2
                                                                                      && t.DateTimeValue == dateTimeValue));
                        break;
                    }

                case FieldType.Text:
                    {
                        var stringValue = Convert.ToString(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass2ExtraFieldValueList.Any(t => t.TestClass2ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass2ExtraField.Property2 == extraField.Property2
                                                                                      && t.StringValue == stringValue));
                        break;
                    }

                case FieldType.Integer:
                    {
                        var integerValue = Convert.ToInt32(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass2ExtraFieldValueList.Any(t => t.TestClass2ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass2ExtraField.Property2 == extraField.Property2
                                                                                      && t.IntValue == integerValue));
                        break;
                    }
            }
        }
        return query;
    }

    private IQueryable<TestClass2> GetTestClass2Values()
    {
        return null;
    }
}

public class TestClass3Query
{
    public string TestValue1;
    public string TestValue2;

    List<ExtraFieldClass> ExtraFieldsList { get; set; }

    public IQueryable GetQuery()
    {
        var query = GetTestClass3Values().Where(c => c.TestProperty1 == TestValue1 && c.TestProperty2 == TestValue2);
        // some condition checks
        foreach (var extraField in ExtraFieldsList)
        {
            switch (extraField.Type)
            {
                case FieldType.Boolean:
                    {
                        var boolValue = Convert.ToBoolean(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass3ExtraFieldValueList.Any(t => t.TestClass3ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass3ExtraField.Property2 == extraField.Property2
                                                                                      && t.BoolValue == boolValue));
                        break;
                    }

                case FieldType.DateTime:
                    {
                        var dateTimeValue = Convert.ToDateTime(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass3ExtraFieldValueList.Any(t => t.TestClass3ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass3ExtraField.Property2 == extraField.Property2
                                                                                      && t.DateTimeValue == dateTimeValue));
                        break;
                    }

                case FieldType.Text:
                    {
                        var stringValue = Convert.ToString(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass3ExtraFieldValueList.Any(t => t.TestClass3ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass3ExtraField.Property2 == extraField.Property2
                                                                                      && t.StringValue == stringValue));
                        break;
                    }

                case FieldType.Integer:
                    {
                        var integerValue = Convert.ToInt32(extraField.Fieldvalue);
                        query = query.Where(c => c.TestClass3ExtraFieldValueList.Any(t => t.TestClass3ExtraField.Property1 == extraField.Property1
                                                                                      && t.TestClass3ExtraField.Property2 == extraField.Property2
                                                                                      && t.IntValue == integerValue));
                        break;
                    }
            }
        }
        return query;
    }

    private IQueryable<TestClass3> GetTestClass3Values()
    {
        return null;
    }
}

Now, if you see my query classes, each has its own condition checks, and for additional fields, the logic is the same, only the names change. And I have a 20 class like this. And I don't want to have to repeat the same code every time. I searched and found out what we can use linq-expressions

to have a common code for this.

I have no knowledge of writing expressions, any suggestions or ways to achieve this?

+3
c # linq linq-expressions


source to share


1 answer


The Linq you write is actually converted to SQL commands. In your case, you've created a rather complex function Match

that is too complex to automatically translate to SQL.

To get around this, you need to extract the statement switch

from the method Match

and create a query in line:

var input = new Parameter();
// ...init input
query = query.Where(a => a.Property1 == "sometext");

switch(input.Type)
{
    Case bool:
        var boolValue = Convert.ToBoolean(input.ObjectValue);
        query = query.Where(a => a.Collection.Any(b => b.TestValue.C1 == input.Parameter1 && b.TestValue.C2 == input.Parameter && b.BoolValue == boolValue);
        break;
    Case dateTime :
        // convert to datetime and check
        var dateTimeValue = Convert.ToDateTime(input.ObjectValue);
        query = query.Where(a=> a.Collection.Any(b => b.TestValue.C1 == input.Parameter1 && b.TestValue.C2 == input.Parameter && b.DateTimeValue == dateTimeValue );
        break;
    Case int:
        // convert to int and check
        // ...
        break;
}

Not the best code, but it should work.

EDIT



You can improve it by moving the ugly part into a separate function that can be reused:

public IQueryable<ITest2> ApplyFilterOnQuery(IQueryable<ITest2> query, Parameter input)
{
    switch(input.Type)
    {
        Case bool:
            var boolValue = Convert.ToBoolean(input.ObjectValue);
            query = query.Where(a => a.Collection.Any(b => b.TestValue.C1 == input.Parameter1 && b.TestValue.C2 == input.Parameter && b.BoolValue == boolValue);
            break;
        Case dateTime :
            // convert to datetime and check
            var dateTimeValue = Convert.ToDateTime(input.ObjectValue);
            query = query.Where(a => a.Collection.Any(b => b.TestValue.C1 == input.Parameter1 && b.TestValue.C2 == input.Parameter && b.DateTimeValue == dateTimeValue );
            break;
        Case int:
            // convert to int and check
            // ...
            break;
    }
    return query;
}

Then you will use it like this:

var input = new Parameter();
// ...init input
query = query.Where(a => a.Property1 == "sometext");
query = ApplyFilterOnQuery(query, input);
0


source to share






More articles:


All Articles
Loading...
X
Show
Funny
Dev
Pics