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
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