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 (...),

Can you post those strings you're getting? because to me "LoanNum in (1234) and LoanNum in (3456) and Dates in (...) and Dates in (...)" is exactly what should be created. IS your intention to pair a LoanNum with a Dates? — Christoph Wolf
– Christoph Wolf, Commented Jan 15, 2019 at 1:26
Why not have a collection of values per filter? So you only have one filter per column with a list of values to check. — Ben
– Ben, Commented Jan 15, 2019 at 1:35

littlerufe · Accepted Answer · 2019-01-15 01:57:04Z

2

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)

edited Jan 15, 2019 at 1:57

littlerufe

37.2k5 gold badges33 silver badges48 bronze badges

answered Jan 15, 2019 at 1:41

Anu Viswan

18.2k4 gold badges28 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

littlerufe · Accepted Answer · 2019-01-15 02:18:37Z

I believe GroupBy and Select is the best way to go, but in case you were wondering how to do it in a loop as you first started doing, one way would be to use a dictionary, where the Key is the ColumnName, and the Value is a comma-separated list of the associated ColumnValue strings:

private static string CreateWhereClause(List<Filter> filters)
{
    if (filters == null) return null;
    if (!filters.Any()) return string.Empty;

    var results = new Dictionary<string, string>();

    foreach (var filter in filters)
    {
        if (results.ContainsKey(filter.ColumnName))
        {
            results[filter.ColumnName] += $",'{filter.ColumnValue}'";
        }
        else
        {
            results.Add(filter.ColumnName, $"'{filter.ColumnValue}'");
        }
    }

    return string.Join(" AND ", results.Select(result => 
        $"{result.Key} IN ({result.Value})"));
}

Putting it to use might look like:

private static void Main()
{
    var filters = 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 query = $"SELECT LOANS WHERE {CreateWhereClause(filters)}";

    Console.WriteLine(query);

    Console.ReadKey();
}

Output

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related