Izayoi.Data.Query
1.3.0
.NET 8.0
This package targets .NET 8.0. The package is compatible with this framework or higher.
.NET Standard 2.1
This package targets .NET Standard 2.1. The package is compatible with this framework or higher.
dotnet add package Izayoi.Data.Query --version 1.3.0
NuGet\Install-Package Izayoi.Data.Query -Version 1.3.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Izayoi.Data.Query" Version="1.3.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="Izayoi.Data.Query" Version="1.3.0" />
<PackageReference Include="Izayoi.Data.Query" />
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add Izayoi.Data.Query --version 1.3.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: Izayoi.Data.Query, 1.3.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package Izayoi.Data.Query@1.3.0
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=Izayoi.Data.Query&version=1.3.0
#tool nuget:?package=Izayoi.Data.Query&version=1.3.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
Izayoi.Data.Query
This is a library that supports building query (SQL) to manipulate a database.
Applies to
| Product | Versions |
|---|---|
| .NET | 8, 9, 10 |
| .NET Standard | 2.1 |
| Unity | 2021, 2022, 6000 |
Wiki
Examples
Query Option
using Izayoi.Data.Query;
static void Main()
{
QueryOption queryOption;
queryOption = new QueryOption(RdbKind.Sqlite, 3);
queryOption = new QueryOption(RdbKind.SqlServer, 2022)
{
QuotationMarks = new QuotationMarkSet('[', ']')
};
queryOption = new QueryOption(RdbKind.Mysql, 8)
{
QuotationMarks = new QuotationMarkSet('`', '`')
};
queryOption = new QueryOption(RdbKind.Pgsql, 16)
{
QuotationMarks = new QuotationMarkSet('"', '"')
};
queryOption = new QueryOption()
{
EnableFormat = true,
IndentSpace = 4,
BeforeComma = true,
}
}
Basic
using Izayoi.Data.Query;
static void Main()
{
var queryOption = new QueryOption();
var queryBuilder = new QueryBuilder(queryOption);
var select = new Select()
.SetFrom("users")
.AddField("*");
queryBuilder.Build(select);
string query = queryBuilder.GetQuery();
var parameters = queryBuilder.GetParameters();
// query:
// SELECT *
// FROM users
// parameters:
// (Empty)
}
Select
// SELECT
{
var select1 = new Select()
.SetFrom("users")
.AddField("id")
.AddField("name")
.AddField("age");
// query:
// SELECT id, name, age
// FROM users
}
// SELECT DISTINCT
{
var select2 = new Select()
.SetType(SType.DISTINCT)
.SetFrom("users")
.AddField("*");
// query:
// SELECT DISTINCT *
// FROM users
}
// WHERE: AND
{
var select3 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", ">=", 13)
.AddWhere("age", "<=", 19);
// query:
// SELECT *
// FROM users
// WHERE age >= @w_0
// AND age <= @w_1
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 13
// [1]:
// ParameterName: @w_1
// DbType: DbType.Int32
// Value: 19
}
// WHERE: BETWEEN
{
var select4 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", OpType.BETWEEN, new int[] { 13, 19 });
// query:
// SELECT *
// FROM users
// WHERE BETWEEN age @w_0_0 AND @w_0_1
// parameters:
// [0]:
// ParameterName: @w_0_0
// DbType: DbType.Int32
// Value: 13
// [1]:
// ParameterName: @w_0_1
// DbType: DbType.Int32
// Value: 19
}
// WHERE: IN
{
var select5 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", OpType.IN, new int[] { 20, 30, 40 });
// query:
// SELECT *
// FROM users
// WHERE age IN (@w_0_0, @w_0_1, @w_0_2)
// parameters:
// [0]:
// ParameterName: @w_0_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_0_1
// DbType: DbType.Int32
// Value: 30
// [2]:
// ParameterName: @w_0_2
// DbType: DbType.Int32
// Value: 40
}
// WHERE: IN
{
var select6 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", OpType.IN, new List<int> { 20, 30, 40 });
// query:
// SELECT *
// FROM users
// WHERE age IN (@w_0_0, @w_0_1, @w_0_2)
// parameters:
// [0]:
// ParameterName: @w_0_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_0_1
// DbType: DbType.Int32
// Value: 30
// [2]:
// ParameterName: @w_0_2
// DbType: DbType.Int32
// Value: 40
}
// WHERE: OR
{
var select7 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere('(', "age", "=", 20)
.AddWhere(CType.OR, "age", "=", 30)
.AddWhere(CType.OR, "age", "=", 40)
.AddWhere(CType.OR, "age", "=", 50, ')');
// query:
// SELECT *
// FROM users
// WHERE (age = @w_0
// OR age = @w_1
// OR age = @w_2
// OR age = @w_3)
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_1
// DbType: DbType.Int32
// Value: 30
// [2]:
// ParameterName: @w_2
// DbType: DbType.Int32
// Value: 40
// [3]:
// ParameterName: @w_3
// DbType: DbType.Int32
// Value: 50
}
// WHERE: IS NULL / IS NOT NULL
{
var select8 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", OpType.IS_NULL);
// query:
// SELECT *
// FROM users
// WHERE name IS NULL
// parameters:
// (Empty)
}
// WHERE: IS NULL
{
var select9 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", "is", null);
// query:
// SELECT *
// FROM users
// WHERE name IS NULL
// parameters:
// (Empty)
}
// WHERE: IS NOT NULL
{
var select10 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", "is not", null);
// query:
// SELECT *
// FROM users
// WHERE name IS NOT NULL
// parameters:
// (Empty)
}
// WHERE: LIKE / NOT LIKE
{
var select11 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", OpType.LIKE, "J%");
// query:
// SELECT *
// FROM users
// WHERE name LIKE @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.String
// Value: "J%"
}
// WHERE
{
var select12 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", ">=", 20)
.AddWhere(Type.AND, "name", OpType.LIKE, "J%")
.AddWhere(Type.AND, "enabled", "=", true);
// query:
// SELECT *
// FROM users
// WHERE age >= @w_0
// AND name LIKE @w_1
// AND enabled = @w_2
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_1
// DbType: DbType.String
// Value: "J%"
// [2]:
// ParameterName: @w_2
// DbType: DbType.Boolean
// Value: true
}
// JOIN
{
var select13 = new Select()
.SetFrom("posts")
.AddJoin(JType.LEFT_JOIN, "users", "users.id = posts.user_id")
.AddField("posts.id")
.AddField("posts.comment")
.AddField("posts.user_id")
.AddField("users.name", "user_name")
.AddWhere("users.age", "<", 18);
// query:
// SELECT posts.id, posts.comment, posts.user_id, users.name AS user_name
// FROM posts
// LEFT JOIN users ON (users.id = posts.user_id)
// WHERE users.age < @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 18
}
// JOIN: Table Alias
{
var select14 = new Select()
.SetFrom("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddField("p.id")
.AddField("p.comment")
.AddField("p.user_id")
.AddField("u.name", "user_name")
.AddWhere("u.age", "<", 18);
// query:
// SELECT p.id, p.comment, p.user_id, u.name AS user_name
// FROM posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// WHERE u.age < @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 18
}
// JOIN: Schema
{
var select15 = new Select()
.SetFrom("dbo", "posts", "p")
.AddJoin(JType.LEFT_JOIN, "dbo", "users", "u", "u.id = p.user_id")
.AddField("p.id")
.AddField("p.comment")
.AddField("p.user_id")
.AddField("u.name", "user_name")
.AddWhere("u.age", "<", 18);
// query:
// SELECT p.id, p.comment, p.user_id, u.name AS user_name
// FROM dbo.posts AS p
// LEFT JOIN dbo.users AS u ON (u.id = p.user_id)
// WHERE u.age < @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 18
}
// GROUP BY
{
var select16 = new Select()
.SetFrom("posts")
.AddJoin(JType.LEFT_JOIN, "users", "users.id = posts.user_id")
.AddField("user_id")
.AddField("users.name", "user_name")
.AddField("COUNT(comment)", "post_count")
.AddGroup("user_id")
.AddGroup("user_name");
// query:
// SELECT user_id, users.name AS user_name, COUNT(comment) AS post_count
// FROM posts
// LEFT JOIN users ON (users.id = posts.user_id)
// GROUP BY user_id, user_name
}
// HAVING
{
var select17 = new Select()
.SetFrom("posts")
.AddField("user_id")
.AddField("COUNT(comment)", "post_count")
.AddGroup("user_id")
.AddHaving("post_count", ">=", 2)
.AddHaving("post_count", "<=", 4);
// query:
// SELECT user_id, COUNT(comment) AS post_count
// FROM posts
// LEFT JOIN users ON (users.id = posts.user_id)
// GROUP BY user_id
// HAVING post_count >= @h_0 AND post_count <= @h_1
// parameters:
// [0]:
// ParameterName: @h_0
// DbType: DbType.Int32
// Value: 2
// [1]:
// ParameterName: @h_1
// DbType: DbType.Int32
// Value: 4
}
// LIMIT and OFFET
{
var select18 = new Select()
.SetFrom("users")
.AddField("*")
.AddOrder("id", OType.ASC)
.SetLimit(5)
.SetOffset(10);
// query:
// SELECT *
// FROM users
// ORDER BY id ASC
// LIMIT 5
// OFFSET 10
// queryOption
// RdbKind.SqlServer
// query:
// SELECT *
// FROM users
// ORDER BY id ASC
// OFFSET 10 ROWS
// FETCH NEXT 5 ROWS ONLY
}
// WITH
{
var select19 = new Select()
.With
.Add(new CommonTableExpression("cte_users1")
.AddColumn("id")
.AddColumn("name")
.AddSelect(new Select()
.SetFrom("users")
.AddField("id")
.AddField("name"))
)
.Add(new CommonTableExpression("cte_users2")
.AddColumn("id")
.AddColumn("name")
.AddSelect(new Select()
.SetFrom("cte_users1")
.AddField("id")
.AddField("name"))
);
select19
.SetFrom("cte_users")
.AddField("*");
// query:
// WITH cte_users1(id, name) AS (
// SELECT id, name
// FROM users
// ),
// WITH cte_users2(id, name) AS (
// SELECT id, name
// FROM cte_users1
// )
// SELECT *
// FROM cte_users2
}
// WITH Recursive
{
var select20 = new Select()
.With
.SetRecursive(true)
.Add(new CommonTableExpression("cte")
.AddColumn("n")
.AddSelect(new Select()
.AddField("1"))
.AddSelect("UNION ALL", new Select()
.SetFrom("cte")
.AddField("n + 1")
.AddWhere("n", "<", 5))
);
select20
.SetFrom("cte_users")
.AddField("*");
// query:
// WITH RECURSIVE cte(n) AS (
// SELECT 1
// UNION ALL
// SELECT n + 1
// FROM cte
// WHERE n < @c_0_1_w_0
// )
// SELECT *
// FROM cte
// parameters:
// [0]:
// ParameterName: @c_0_1_w_0
// DbType: DbType.Int32
// Value: 5
}
Insert
{
var insert1 = new Insert();
insert1.SetInto("users")
.Values
.Add("id", 1)
.Add("name", "name1")
.Add("age", 20)
.Add("created_at", DateTimeOffset.UtcNow)
.Add("updated_at", DateTimeOffset.UtcNow);
// query:
// INSERT INTO users
// (id, name, age, created_at, updated_at)
// VALUES
// (@v_0, @v_1, @v_2, @v_3, @v_4)
// parameters:
// [0]:
// ParameterName: @v_0
// DbType: DbType.Int32
// Value: 1
// [1]:
// ParameterName: @v_1
// DbType: DbType.String
// Value: "name1"
// [2]:
// ParameterName: @v_2
// DbType: DbType.Int32
// Value: 20
// [3]:
// ParameterName: @v_3
// DbType: DbType.DateTime
// Value: (2024-08-01 00:00:00)
// [4]:
// ParameterName: @v_4
// DbType: DbType.DateTime
// Value: (2024-08-01 00:00:00)
}
{
var insert2 = new Insert();
insert2.SetInto("users")
.Select ??= new Select()
.SetFrom("users2")
.AddField("id")
.AddField("name")
.AddField("age")
.AddField("created_at")
.AddField("updated_at");
// query:
// INSERT INTO users
// SELECT id, name, age, created_at, updated_at
// FROM users2
}
Update
{
var update1 = new Update()
.SetTable("users")
.AddSet("age", 21)
.AddSet("updated_at", DateTime.UtcNow)
.AddWhere("id", "=", 1);
// query:
// UPDATE users
// SET age = @s_0, updated_at = @s_1
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.Int32
// Value: 21
// [1]:
// ParameterName: @s_1
// DbType: DbType.DateTime
// Value: (2024-08-01 00:00:00)
// [2]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
// JOIN for MySQL
{
var update2 = new Update()
.SetTable("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddSet("comment", "comment1")
.AddWhere("p.id", "=", 1);
// query:
// UPDATE posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// SET comment = @s_0
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.Int32
// Value: "comment1"
// [1]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
// JOIN for PostgreSQL, SQLite and SQL Server
{
var update3 = new Update()
.SetTable("posts")
.AddSet("comment", "comment1")
.SetFrom("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddWhere("p.id", "=", 1);
// query:
// UPDATE posts
// SET comment = @s_0
// FROM posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.String
// Value: "comment1"
// [1]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
// JOIN for SQL Server
{
var update4 = new Update()
.SetTable("", "p")
.AddSet("comment", "comment1")
.SetFrom("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddWhere("p.id", "=", 1);
// query:
// UPDATE p
// SET comment = @s_0
// FROM posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.String
// Value: "comment1"
// [1]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
Delete
{
var delete1 = new Delete()
.SetFrom("users")
.AddWhere("id", "=", 1);
// query:
// DELETE
// FROM users
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
Last updated: 24 November, 2025
Editor: Izayoi Jiichan
Copyright (C) 2024 Izayoi Jiichan. All Rights Reserved.
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 is compatible. net9.0-android was computed. net9.0-browser was computed. net9.0-ios was computed. net9.0-maccatalyst was computed. net9.0-macos was computed. net9.0-tvos was computed. net9.0-windows was computed. net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
| .NET Core | netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
| .NET Standard | netstandard2.1 is compatible. |
| MonoAndroid | monoandroid was computed. |
| MonoMac | monomac was computed. |
| MonoTouch | monotouch was computed. |
| Tizen | tizen60 was computed. |
| Xamarin.iOS | xamarinios was computed. |
| Xamarin.Mac | xamarinmac was computed. |
| Xamarin.TVOS | xamarintvos was computed. |
| Xamarin.WatchOS | xamarinwatchos was computed. |
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
-
.NETStandard 2.1
- No dependencies.
-
net10.0
- No dependencies.
-
net8.0
- No dependencies.
-
net9.0
- No dependencies.
NuGet packages (1)
Showing the top 1 NuGet packages that depend on Izayoi.Data.Query:
| Package | Downloads |
|---|---|
|
Izayoi.Data.DbCommandAdapter
This is a database operation support library that includes a fast micro O/R mapper (ORM). |
GitHub repositories
This package is not used by any popular GitHub repositories.