Dynamic LINQ expression where field name is not known

Question

I am working on an ASP.NET Web Api service where one of the controller actions accepts a JSON string with 0 or more Key:Value pairs to search against a collection of objects. Because of this I do not know which field names will be in the request to filter the collection on.

Now, I have code that will build dynamic query by chaining WHERE expressions based on the data provided. The problem in this case is that not only do I not know the field name which needs to be filtered on, the list of fields and their values are stored in a collection within each object - and the objects in that collection only have two properties: Name and Value.

The data is being deserialized from a bunch of XML files that I do not have control over (so cannot change the formatting), and the list of fields contained within each file could be different. (see class definition below).

[System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
[System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = false)]
public class Bug
{
    [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
    public int ID { get; set; }

    [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
    public DateTime ChangedDate { get; set; }

    [System.Xml.Serialization.XmlArrayAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
    [System.Xml.Serialization.XmlArrayItemAttribute("Field", typeof(BugField), Form = System.Xml.Schema.XmlSchemaForm.Unqualified, IsNullable = false)]
    public List<BugField> Fields {get; set;}
}

If I run the following query everything works fine - I get the result I am looking for based on the JSON request - however that request is only searching on one field and one value, and the query has the index of the correct field hardcoded ;) (FYI - itemList is a collection created earlier with no filtering)

itemList = (List<Bug>)itemList.Where(x => x.Fields[15].Value.ToString() == field.Value.ToString()).ToList();

I do have code in place to create a dynamic LINQ query (chaining WHERE expressions) based on the search fields provided from a JSON request (I am not including the code for this here as it is too long - and not sure it is totally relevant...YET). However, the way the expressions are being parsed you need to be able to reference the name of the property to search against - which of course is not known since it is the value of the Name property.

So - How to modify the query to take into account that field names that determine the query parameters are unknown beforehand?

EDIT: The following code block shows what I want to use (or rather that would work with my dynamic query builder). The first line is the code that works great if the field name in the class is defined the same as the field name provided in the JSON string. The second is one of the attempts I made at trying to get to the inner collection field name property.

foreach (KeyValuePair<string, object> field in queryFields)
{
    itemList = itemList.Where<Bug>(field.Key, field.Value, (FilterOperation)StringEnum.Parse(typeof(FilterOperation), "eq"));
    itemList = itemList.Where<Bug>(x => x.Fields.Any(y => y.Name == field.Key && y.Value == field.Value)); 
}

Is there a reason your are not adding `using System.Xml.Serialization` to the top of this cs file? It makes your code difficult to read. If you add this `using` statement, you'd be able to remove `System.Xml.Serialization` from the attribute declaration. Eg: `[XmlArrayAttribute(..)` instead of `[System.Xml.Serialization.XmlArrayAttribute(..)` — Odys, Mar 25 '13 at 16:50
@odyodyodys: Agreed. For attributes, you can also drop the trailing "Attribute", as it's implied in C#. — recursive, Mar 25 '13 at 16:55
Actually - no, there is no reason. Normally I do exactly that to make things cleaner. :) — Justin Greywolf, Mar 25 '13 at 16:59

score 1 · Answer 1 · answered Jun 06 '14 at 16:10

I know this is old, but here is how I did it without having to type in every possible name:

1) Anonymous IEnumerable coming in, cast as IEnumerable or IList as required
2) On the first element,

var property = element.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public)

3) Foreach object in property, add columns to your datatable:

table.Columns.Add(property.Name, typeof(property.GetValue(element, null));

4) Iterate your collection from the beginning: foreach:

var prop = item.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

dynamic i = new ExpandoObject();
IDictionary<string, object> d = (IDictionary<string, object>)i;
foreach (var p in prop)
{
    d.Add(p.Name, p.GetValue(item));
}
list.Rows.Add(((IDictionary<string, object>)d).Values.ToArray());

This gets the anonymous types, extracts them into an ExpandoObject (which is completely interchangeable with a IDictionary), then adds the row by casting the IDictionary dynamic object as an array.

Scales with any type you can throw at it and allows you to manipulate the data as it is constructed.

score 0 · Answer 2 · answered Mar 25 '13 at 17:02

I see a couple of approaches to solve that:

1) Have a list of all properties that are available for filtering, match the properties and add them dynamically to your linq query.

The way that would work is that you will accept your json object and iterate through the properties passed with their values. So, say that you get in your controller:

{
Prop[0].Name = "Name" 
Prop[0].Value = "Justin"
Prop[1].Name = "Email"
Prop[1].Value = "j@g.com"
}

In your controller, for example, you will iterate through each key value and dynamicall chain it. Some pseudocode:

foreach(var keyValue in Filters){
  var prop = keyValue.Name;
  var val = keyValue.Value;
  myLinqQuery.AddWhere(c => c.GetType().GetProperty().Name == prop && c.GetValue() == Value);
}

This apprach will work good, but one big disadvantage is that it will try and filter each property in the request with reflection. You would loose some control in that case.

2) Allow only a number of filters and have a filter condition for each of those. In that option you would allow only a list of filters and have some sort of an where action for each of them. Here is a simple example: Say that your controller takes in:

public ActionResult Filter(string name, string email){
 //name and email are the filter values
 var query = ( from c in (List<Bug>)itemList select c);
if(!string.IsNullOrEmpty(name))
  query.AddWhere(c => c.Name == name);

if(!string.IsNullOrEmpty(email))
  query.AddWhere(c => c.Email == email);


}

You can further design that to be more OOP and SOLID by creating a separate class for each filter allowed and exposing its filter action.

Let me know if it helps!

The second approach, while it would work, is not one that I would like to do - there are quite a lot of potential fields to be searched against and would rather not right a separate action for different combinations - or one action with multiple conditionals for each item :) I am investigating the first approach though. Thanks... — Justin Greywolf, Mar 25 '13 at 17:21
Where is the method .AddWhere() located? I was not able to make your first proposal work either due to not knowing where that specific method was contained - also the c.GetType().GetProperty() functions were not resolving... — Justin Greywolf, Mar 26 '13 at 21:06
Hey, the AddWhere method is pseudocode. You mentioned that you have a way of dynamically chaning where statements so that is what I was refering to here. Also, the reflection methods are pseudocode as well. You can have a look for those at this question for those: http://stackoverflow.com/questions/1196991/get-property-value-from-string-using-reflection-in-c-sharp — sTodorov, Mar 27 '13 at 07:03
Got it. I just wanted to make sure there wasn't something I was missing :) I was not able to get the first option to work even translating out of pseudo code due to the issue of the fieldnames in the collection being unknown - as well as the field names in the query... — Justin Greywolf, Mar 27 '13 at 16:36

score 0 · Accepted Answer · answered Mar 26 '13 at 21:04

After spending almost a full 2 days on trying to get this solution to work without making big changes elsewhere I finally decided to make a slight design change to the model to make this easier.

How I resolved this was to add each field to the model directly, instead of using a List of custom objects. Due to this I removed the XML Deserialization, and instead looped through all of the elements within the Node using XDocument - then comparing the value of the "Name" attribute to determine which field it was, and assigning the value of the "Value" attribute to the corresponding Property in the model.

A little extra code, and a nice little switch statement with 22 cases to handle the assignments...

Not how I wanted to do it, but at least I can still deliver the service on time and then try to see if there is a better way to do this later...

For the sake of completeness here is what I changed:

public class Bug
{
    public int ID { get; set; }

    public DateTime ChangedDate { get; set; }

    public string State { get; set; }
    public string AreaPath { get; set; }
    public string Title { get; set; }
    public string Status { get; set; }
    public string SubStatus { get; set; }
    public string OpenedBy { get; set; }
    public string ChangedBy { get; set; }
    public DateTime OpenedDate { get; set; }
    public DateTime ResolvedDate { get; set; }
    public string AssignedTo { get; set; }
    public string IssueType { get; set; }
    public string IssueSubtype { get; set; }
    public string Priority { get; set; }
    public string Severity { get; set; }
    public string Keywords { get; set; }
    public string ScreenID { get; set; }
    public string ResolvedBy { get; set; }
    public string Resolution { get; set; }
    public string ReproSteps { get; set; }
    public string HowFound { get; set; }
    public string FullHistory { get; set; }
    public string EverChangedBy { get; set; }


}

and what used to be the deserialization method:

internal static Bug Deserialize(string filePath)
    {
        XDocument doc = XDocument.Load(filePath);
        Bug bug = new Bug();

        bug.ID = int.Parse(doc.Root.Element("ID").Value);
        bug.ChangedDate = DateTime.Parse(doc.Root.Element("ChangedDate").Value);

        foreach (var el in doc.Root.Element("Fields").Elements())
        {
            XAttribute fieldName = el.Attributes("Name").Single();
            XAttribute fieldValue = el.Attributes("Value").Single();

            switch (fieldName.Value.ToString())
            {
                case "State":
                    bug.State = fieldValue.Value.ToString();
                    break;
                case "Area Path":
                    bug.AreaPath = fieldValue.Value.ToString();
                    break;
                case "Title":
                    bug.Title = fieldValue.Value.ToString();
                    break;
                case "Status":
                    bug.Status = fieldValue.Value.ToString();
                    break;
                case "SubStatus":
                    bug.SubStatus = fieldValue.Value.ToString();
                    break;
                case "Opened By":
                    bug.OpenedBy = fieldValue.Value.ToString();
                    break;
                case "ChangedBy":
                    bug.ChangedBy = fieldValue.Value.ToString();
                    break;
                case "Opened Date":
                    bug.OpenedDate = DateTime.Parse(fieldValue.Value.ToString());
                    break;
                case "Resolved Date":
                    bug.ResolvedDate = DateTime.Parse(fieldValue.Value.ToString());
                    break;
                case "Assigned To":
                    bug.AssignedTo = fieldValue.Value.ToString();
                    break;
                case "Issue Type":
                    bug.IssueType = fieldValue.Value.ToString();
                    break;
                case "Issue Subtype":
                    bug.IssueSubtype = fieldValue.Value.ToString();
                    break;
                case "Priority":
                    bug.Priority = fieldValue.Value.ToString();
                    break;
                case "Severity":
                    bug.Severity = fieldValue.Value.ToString();
                    break;
                case "Keywords":
                    bug.Keywords = fieldValue.Value.ToString();
                    break;
                case "ScreenID":
                    bug.ScreenID = fieldValue.Value.ToString();
                    break;
                case "ResolvedBy":
                    bug.ResolvedBy = fieldValue.Value.ToString();
                    break;
                case "Resolution":
                    bug.Resolution = fieldValue.Value.ToString();
                    break;
                case "ReproSteps":
                    bug.State = fieldValue.Value.ToString();
                    break;
                case "HowFound":
                    bug.State = fieldValue.Value.ToString();
                    break;
                case "FullHistory":
                    bug.State = fieldValue.Value.ToString();
                    break;
                case "EverChangedBy":
                    bug.State = fieldValue.Value.ToString();
                    break;
            }
        }

        return bug;
    }

Which allowed me to use the following call to build out the LINQ query:

foreach (KeyValuePair<string, object> field in queryFields)
        {
            itemList = itemList.Where<Bug>(field.Key, field.Value, (FilterOperation)StringEnum.Parse(typeof(FilterOperation), "eq"));                
        }

Cheers!

Dynamic LINQ expression where field name is not known

3 Answers3