Build a string changing a variable in each loop iteration and joining all of them

Question

I need to build a my sql query which is basically at his core is similar to the following:

select something from {myCurrentTable} where some condition

This query must be repeated N times where N is unknown and put in a union.

I need to change the value of myCurrentTable every iteration so i would do something like this:

foreach(string table in myTables)
{
    queryTot += $"SELECT something from {table} where some condition";
    if(!lastIteration)
       queryTot += " union ";
}

Is there any function i can use to get rid of the explicit loop?

Is there a reason you need to get rid of the loop? Have you thought about string.Join(" UNION ", ...) ? — BurnsBA
– BurnsBA, Commented Sep 20, 2021 at 14:04
For what it's worth you may want UNION ALL instead of UNION: the latter deduplicates the results. — O. Jones
– O. Jones, Commented Sep 20, 2021 at 14:09
@BurnsBA mainly cause i want to make the part of the function i'm working on less "verbose" — Daniele Sartori
– Daniele Sartori, Commented Sep 20, 2021 at 14:11
Also, what is wrong with using a loop in the first place? Any code you do use (like the string.Join answer below) is internally still using a loop. — DavidG
– DavidG, Commented Sep 20, 2021 at 14:16
The thing that's nice about using string.Join is that you don't need to be concerned with checking for the last iteration, and it also works with a single table :). With the loop approach, both these need to be accounted for. — Johnathan Barclay
– Johnathan Barclay, Commented Sep 20, 2021 at 14:21

Johnathan Barclay · Accepted Answer · 2021-09-20 14:28:45Z

3

You could use LINQ Select to project the tables into SQL select statements, then string.Join to add a union between each one:

string query = string.Join(
    " union ",
    myTables.Select(table => $"SELECT something from {table} where some condition"));

Using string.Join rather than a manual loop means that the unions are correctly inserted without having to check for the last iteration, or check whether the myTables collection only contains a single item.

edited Sep 20, 2021 at 14:28

answered Sep 20, 2021 at 14:06

Johnathan Barclay

20.5k2 gold badges27 silver badges40 bronze badges

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

2 Comments

Daniele Sartori Over a year ago

Thank you very much. I have used string.Join in the past but i didn't thought i could have combined with LINQ Select.

Johnathan Barclay Over a year ago

@DanieleSartori Select returns an IEnumerable<string> here, and string.Join has an overload that accepts IEnumerable<string> as the second argument.

Ran Turner · Accepted Answer · 2021-09-20 14:15:37Z

2

If you concatenate strings in a loop in an undeterministic count, you should consider using StringBuilder instead of regular String.

Since a string is an immutable data type in C#, when you combine two or more strings, a new string is produced. If you append a series of characters to a string (like in your example), you will recreate the same string in memory multiple times..

An alternative approach is modifying a StringBuilder instance which does not result in the creation of a new instance in memory, hence it's recommended in the case you describe.

answered Sep 20, 2021 at 14:15

Ran Turner

18.7k9 gold badges60 silver badges60 bronze badges

Collectives™ on Stack Overflow

Build a string changing a variable in each loop iteration and joining all of them

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related