How to generate tuple matching query based on multiple column?

[md-redwan-hossain]

Is there any way to generate queries as given below?? I did't find anything that can achieve as the followings.

SELECT * 
FROM "ProductStocks" ps
where (ps."DepotId", ps."ProductId", ps."ProductBatchId") IN ((1, 1, 1),(1, 1, 2));

SELECT *
FROM person
WHERE (firstname, lastname) = ( ('Arthur', 'Dent') );

SELECT *
FROM person
WHERE (firstname, lastname) > ('Arthur', 'Dent');

SELECT *
FROM person
WHERE (firstname, lastname) IN ( ('Arthur', 'Dent'), ('Ford','Prefect') )

[keparlak]

First you can use raw query like;

var query = new Query("ProductStocks")
    .WhereRaw("(\"DepotId\", \"ProductId\", \"ProductBatchId\") IN ((1, 1, 1),(1, 1, 2))");

or you can use like the example below;

// Tuple listesi için
var tuples = new List<(int depotId, int productId, int batchId)> 
{
    (1, 1, 1),
    (1, 1, 2)
};

var query = new Query("ProductStocks");
query.Where(q =>
{
    foreach (var tuple in tuples)
    {
        q.OrWhere(sub => sub
            .Where("DepotId", tuple.depotId)
            .Where("ProductId", tuple.productId)
            .Where("ProductBatchId", tuple.batchId));
    }
});

Maybe you can create a custom extension method to do this like that;

public static class SqlKataExtensions
{
    public static Query WhereInTuple(this Query query, 
        string[] columns, 
        IEnumerable<object[]> values)
    {
        var conditions = string.Join(", ", columns.Select(c => $"\"{c}\""));
        var valuesList = string.Join(",", 
            values.Select(v => $"({string.Join(", ", v)})"));
        
        return query.WhereRaw($"({conditions}) IN ({valuesList})");
    }
}

// Usage:
var query = new Query("ProductStocks")
    .WhereInTuple(
        new[] { "DepotId", "ProductId", "ProductBatchId" },
        new[] 
        { 
            new object[] { 1, 1, 1 },
            new object[] { 1, 1, 2 }
        }
    );
    
    ```