Insert query with WhereNotExist clause (insert or ignore)

[Swahhillie]

I am trying to generate the following query:

INSERT INTO "tree_prototype" ("name", "height")
SELECT 'Accacia 13.8m', 13.78331
WHERE NOT EXISTS(SELECT 1 FROM "tree_prototype" WHERE "name" = 'Accacia 13.8m')

But all I am able to get sqlkata to make is this:

INSERT INTO "tree_prototype" ("name", "height")
SELECT 'Accacia 13.8m', 13.78331
FROM "tree_prototype"
WHERE NOT EXISTS(SELECT 1 FROM "tree_prototype" WHERE "name" = 'Accacia 13.8m')

The second query will throw because of a unique constraint failure on the name column.

Using the following code:

var notExists = new Query("tree_prototype")
    .WhereNotExists(q =>
    q.From("tree_prototype")
        .Where("name", data.name))
    .SelectRaw("?,?", data.name, data.height);

var insert = new Query("tree_prototype")
    .AsInsert(new[] { "name", "height" }, notExists);

db.Execute(insert);

How would I write the code to generate the desired SQL code?

[xPudda]

You could check if the value exists in separate queries.

But the query could be also like this:

var notExists = new Query()
    .WhereNotExists(q => q.From("tree_prototype")
                          .Where("name", data.name))
    .FromRaw("( VALUES ( ?, ? )) AS t ( name, height )", data.name, data.height)
    .Select("t.name", "t.height");

var insert = new Query("tree_prototype")
    .AsInsert(new[] { "name", "height" }, notExists);

db.Execute(insert);

Refer to this Microsoft documentation.