public class Foo {
    public long Id {get; set;}
    public string NotNullableText {get; set;}
    public string? NullableText {get; set;}

    public Foo(long id, string notNullableText, string? nullableText) {
        this.Id = id;
        this.NotNullableText = notNullableText;
        this.NullableText = nullableText;
    }

    public Foo() : this(default(long), "", default(string?)) {}

    public override string ToString() => @"foo";
} 

public class ChildOfFoo {
    public long Id {get; set;}
    public long FooId {get; set;}
    public long? Val {get; set;}
    
    public class ForeignKeysCollection {
        public SogePoco.Common.JoinInfo<ChildOfFoo,SogePocoIntroduction.Pocos.Foo> Foo_by_FooId                 
            => throw new System.Exception("This code should not be called in runtime, it only servers as DSL during query generation");
    }
    public ForeignKeysCollection ForeignKeys => throw new System.Exception("This code should not be called in runtime, it only servers as DSL during query generation");

    public ChildOfFoo(long id, long fooId, long? val) {
        this.Id = id;
        this.FooId = fooId;
        this.Val = val;
    }

    public ChildOfFoo() : this(default(long), default(long), default(long?)) {}

    public override string ToString() => @"child_of_foo";
} 

[GenerateQueries]
public class InvokeGenerator {
    public void GetFoo() => Query.Register((Foo x) => x.NotNullableText == "something");
}

public static class DatabaseExtensions {    
    public static async System.Collections.Generic.IAsyncEnumerable<SogePocoIntroduction.Pocos.Foo> GetFoo(
            this SogePocoIntroduction.Database self) {

        await using var cmd = self.CreateCommand();
        cmd.Parameters.Add(
            CreateParam(
                cmd,
                @"$0", 
                ((object?)@"something" ?? System.DBNull.Value)));                        
        cmd.CommandText = @"SELECT
""t0"".""id"", ""t0"".""not_nullable_text"", ""t0"".""nullable_text""
FROM ""foo"" as ""t0""
WHERE ""t0"".""not_nullable_text"" = $0;";
        self.LastSqlText = cmd.CommandText;
        self.LastSqlParams = cmd.Parameters.Cast<System.Data.Common.DbParameter>().ToArray();
        await using var rdr = await cmd.ExecuteReaderAsync();

        while (await rdr.ReadAsync()) {
            var iCol = 0;

            var itm0 = new SogePocoIntroduction.Pocos.Foo(
                id:(long)rdr.GetValue(iCol++), 
                notNullableText:(string)rdr.GetValue(iCol++), 
                nullableText:rdr.GetValue(iCol++) switch { System.DBNull => null, var x => (string?)x});
            
            yield return itm0;
        }            
    }
    
    private static System.Data.Common.DbParameter CreateParam(System.Data.Common.DbCommand cmd, string n, object? v) {
        var result = cmd.CreateParameter();
        result.ParameterName = n;
        result.Value = v;
        return result;
    }
}

use SogePoco.Common;

[GenerateQueries]
class SomeClassToHoldMyRequestsToGenerateQueries {
    public void SomeRequestedQuery() =>	
        Query.Register((Foo f) => f.NullableText == "abc");		
}

use SogePoco.Common;

[GenerateQueries]
class SomeClassToHoldMyRequestsToGenerateQueries {
    public void SomeRequestedQuery(string x) =>	
        Query.Register((Foo f) => f.NullableText == x);		
}

use SogePoco.Common;

[GenerateQueries]
class Irrelevant {    
    //to get equivalent of `select * from Foo where nullable_text is null or nullable_text = $0` use:
    public void GetMatchingFoo(string p) =>	
        Query.Register((Foo f) => f.NullableText == null || f.NullableText == p);
    
    //to get equivalent of `select * from Foo where nullable_text is not null and nullable_text = $0` use:
    public void GetMatchingFoo(string p) =>	
        Query.Register((Foo f) => f.NullableText != null && f.NullableText == p);	
}

use SogePoco.Common;

[GenerateQueries]
class Irrelevant {
    /* 
    to get equivalent of
        select * 
        from child_of_foo 
        join foo on foo.id = child_of_foo.foo_id 
        where child_of_foo.val = $0` 
    Notice: you don't need to specify how to join tables. This information is stored as foreign key
    */
    public void GetValueTupleOfChildOfFooAndFoo(int neededVal) =>	
        Query
            .From<ChildOfFoo>()
            .Join(cof => cof.ForeignKeys.Foo_by_FooId)		
            .Where( (cof,f) => cof.Val == neededVal); //no `.Select(...)` hence generated query will return Value tuple of all joined types/pocos. In this case: (ChildOfFoo, Foo)
    
    //you can initiate join from another type (one that doesn't have foreign key) like this:
    public void GetValueTupleOfChildOfFooAndFoo(int prm) =>
        Query
            .From<Foo>()
            .Join((ChildOfFoo cof) => cof.ForeignKeys.Foo_by_FooId, f => f) //second parameter is very important when you have joined at least one table already
            .Where( (cof,f) => f.Val == prm);
    
    public void GetValueTupleOfChildOfFooAndFoo(int v) =>	
        Query
            .From<ChildOfFoo>()
            .Join(cof => cof.ForeignKeys.Foo_by_FooId)		
            .Where( (cof,f) => cof.Val == v)
            .Select((cof,f) => cof); //same as first query but generated query will return `ChildOfFoo` instances instead of ValueTuple: (ChildOfFoo, Foo)

    //you can also use .LeftJoin() with same syntax as .Join() above to get `left join` instead of `inner join` 
}

use SogePoco.Common;

[GenerateQueries]
class Second {
public void GetFoo() =>	
    Query
        .From<Foo>()
        .OrderByAsc(f => f.NotNullableText);
        
        //for reverse order use: 
        //.OrderByDesc(f => f.NotNullableText)
}

use SogePoco.Common;

[GenerateQueries]
class Second {
    public void GetFoo() =>	
        Query
            .From<Foo>()
            .Take(1);            
}

use SogePoco.Common;

[GenerateQueries]
class Second {
    public void GetMatchingFoo(string[] mustBeIn) =>
        Query.Register((Foo f) => mustBeIn.Contains(f.NullableText));
}

use SogePoco.Common;

[GenerateQueries]
class Second {
    public void GetMatchingFoo() =>	
        PostgresQuery  //notice: different root here
            .From<Foo>()
            .ForUpdate();
            //or .ForShare()
            //or .ForNoKeyUpdate()
            //or: .WithForClause("update nowait or anything custom goes here") 
}

System.Action<string> OnRowsAffectedExpectedToBeExactlyOne {set;}