SQLite Query: Translate string.Join and Concat

[bricelam]

These can be translated into the group_concat aggregate function.

.NET	SQL
string.Concat(values)	group_concat($values, '')
string.Join(separator, values)	group_concat($values, $separator)

[roji]

I've already added some custom aggregate translations for Npgsql (npgsql/efcore.pg#2383), I can probably do the above along with #2981.

[roji]

Note slight discrepancy between string.Join and group_concat, as group_concat filters out nulls but the string.Join doesn't - you get an empty string. So to get a faithful translation, we'd need to coalesce to empty string. Same thing happens on SQL Server and PG string_agg.

[roji]

Poaching (in case it wasn't abundantly clear 😅)

[roji]

Same as SQL Server (#2981 (comment)):

Null semantics of group_concat

DROP TABLE IF EXISTS data;
CREATE TABLE data
(
    id INT PRIMARY KEY,
    name TEXT
);

-- Simple sample
INSERT INTO data (name) VALUES ('foo'), ('bar');
SELECT group_concat(name, ', ') FROM data; -- foo, bar

-- NULLs are ignored and the corresponding separator is not added
DELETE FROM data WHERE 1=1;
INSERT INTO data (name) VALUES ('foo'), (NULL), ('bar');
SELECT group_concat(name, ', ') FROM data; -- foo, bar

-- NULLs only results in NULL
DELETE FROM data WHERE 1=1;
INSERT INTO data (name) VALUES (NULL);
SELECT group_concat(name, ', ') FROM data; -- NULL

-- Empty table results in NULL
DELETE FROM data WHERE 1=1;
SELECT group_concat(name, ', ') FROM data; -- NULL

[roji]

Duplicate of #2981

[roji]

Doing this as part of #2981