How to build a dynamic string from a List of Objects?

Question

Lets say that I have a list of class called Filter

 public class Filter
    {
        [JsonProperty("field")]
        public string ColumnName { get; set; }
        [JsonProperty("value")]
        public string ColumnValue { get; set; }
        [JsonProperty("operator")]
        public object Operator {get; set;}
        public override string ToString()
        {
            return String.Format("{0} in ('{1}')", this.ColumnName, this.ColumnValue);
        }
     }

I need I could have a list of Filters where the column name could be the same.

So I could have an instance of ColumnName "LoanNumber" multiple times one for each instance of the class in the List. Each LoanNumber will have a completely different value. So I need to gather all of the values and put them under one single LoanNumber for an In clause string.

How can I loop through the list of filters and build a string that looks like this

string where = "LoanNum in (1234,456, 55676) and Dates in (01/01/2019, 01/02/2019)";

So far I am having trouble getting all of the data to look like above

 private string CreateWhereClause(List<Filter> filter)
        {
            StringBuilder sb = new StringBuilder();            
            foreach(var f in filter)
            {
                if (!sb.ToString().Contains(f.ColumnName))
                {
                    sb.Append(f.ToString() + " AND ");
                }else
                {
                    sb.Append(f.ToString() + " AND ");
                }
            }
            sb.Remove(sb.Length - 4, 4);
            return sb.ToString();
        }

The problem with above is I get a string that looks like this LoanNum in (1234) and LoanNum in (3456) and Dates in (...) and Dates in (...),

Answer 1

I hope I understood you correctly. You could do

var list = new List<Filter>
{
    new Filter{ColumnName="LoanNum",ColumnValue="1234"},
    new Filter{ColumnName="Dates",ColumnValue="01/01/2019"},
    new Filter{ColumnName="LoanNum",ColumnValue="456"},
    new Filter{ColumnName="Dates",ColumnValue="01/02/2019"},
    new Filter{ColumnName="LoanNum",ColumnValue="55676"},
};


var subResult = list
    .GroupBy(filter => filter.ColumnName)
    .Select(group => string.Format("{0} in ({1})", group.Key,
        string.Join(",", group.Select(filter => filter.ColumnValue))));

var where = string.Join(" and ", subResult);

Output

LoanNum in (1234,456,55676) and Dates in (01/01/2019,01/02/2019)