LambdicSql で共通な select from を使った union

GitHub - Codeer-Software/LambdicSql
GitHub - Codeer-Software/LambdicSql.SqlServer

を使ったサンプル。

元ネタ

SELECT
  foo, 
  bar
FROM
  table
WHERE 
  foo = 1
  AND bar = 2
UNION ALL
SELECT
  foo, 
  bar
FROM
  table
WHERE
  foo = 3
  AND baz = 4

select - from は共通してて、union all する時の where を変更したいみたいな感じ。

install-package LambdicSql.SqlServer
install-package System.Data.SqlClient

して、

using LambdicSql;
using System;
using System.Data.SqlClient;
using static LambdicSql.SqlServer.Symbol;

public class table
{
  public int foo { get; set; }
  public int bar { get; set; }
}
public class DB
{
  public table table { get; set; }
}

class Program
{

  static void Main(string[] args)
  {
    var selectfrom = Db<DB>.Sql(db => Select(new table
    {
      foo = db.table.foo,
      bar = db.table.bar
    }).From(db.table));

    var query = selectfrom +
      Db<DB>.Sql(db => Where(db.table.foo == 1 && db.table.bar == 2)) +
      Db<DB>.Sql(_ => Union(All())) +
      selectfrom +
      Db<DB>.Sql(db => Where(db.table.foo == 3 && db.table.bar == 4));

    var qs = query.Build(typeof(SqlConnection)).Text;

    Console.WriteLine(qs);
/*
SELECT
  table.foo AS foo,
  table.bar AS bar
FROM table
WHERE table.foo = @p_0 AND table.bar = @p_1
UNION ALL
SELECT
  table.foo AS foo,
  table.bar AS bar
FROM table
WHERE table.foo = @p_2 AND table.bar = @p_3
*/
  }
}