FluentSqlKata 1.0.1

.NET Standard 2.0
dotnet add package FluentSqlKata --version 1.0.1
NuGet\Install-Package FluentSqlKata -Version 1.0.1
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="FluentSqlKata" Version="1.0.1" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add FluentSqlKata --version 1.0.1
#r "nuget: FluentSqlKata, 1.0.1"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install FluentSqlKata as a Cake Addin
#addin nuget:?package=FluentSqlKata&version=1.0.1

// Install FluentSqlKata as a Cake Tool
#tool nuget:?package=FluentSqlKata&version=1.0.1

FluentSqlKata

Simple and very concrete, fluent way to build sql queries.

Features

  • Fluent queries (you have more freedom than with Linq, HQL or QueryOver)
  • OrderByAlias (Dynamic way to order by columns in runtime)
  • Can be easily used with Entity Framework 6 without huge code changes (see FluentSqlKata.EFCore6)
  • All standart SqlKata features remain without changes

Installation

Using dotnet cli

$ dotnet add package FluentSqlKata

Using Nuget Package Manager

PM> Install-Package FluentSqlKata

How to use

using FluentSqlKata;

[Fact]
public void Basic()
{
    Customer myCust = null;
    (string CustomerId, string CustomerName) result = default;

    var query = FluentQuery.Query()
        .From(() => myCust)
        .Select(() => result.CustomerId, () => myCust.Id)
        .Select(() => result.CustomerName, () => myCust.Name)
        ;

    var query_str = new SqlServerCompiler().Compile(query).ToString();

    Assert.NotNull(query_str);
    Assert.Equal("SELECT [myCust].[Id] AS [Item1], [myCust].[Name] AS [Item2] FROM [Customer] AS [myCust]", query_str);
}

[Fact]
public void Where()
{
    Customer myCust = null;
    (string CustomerId, string CustomerName) result = default;

    var query = FluentQuery.Query()
        .From(() => myCust)
        .Select(() => result.CustomerId, () => myCust.Id)
        .Select(() => result.CustomerName, () => myCust.Name)
        .Where((q) => q.Where(() => myCust.Name, "John").OrWhereContains(() => myCust.Name, "oh"))
        ;

    var query_str = new SqlServerCompiler().Compile(query).ToString();

    Assert.NotNull(query_str);
    Assert.Equal("SELECT [myCust].[Id] AS [Item1], [myCust].[Name] AS [Item2] FROM [Customer] AS [myCust] WHERE ([myCust].[Name] = 'John' OR LOWER([myCust].[Name]) like '%oh%')", query_str);
}

[Fact]
public void Join()
{
    Contact myCont = null;
    Customer myCust = null;
    (string FirstName, string LastName, string CustomerId, string CustomerName) result = default;

    var query = FluentQuery.Query()
        .From(() => myCont)
        .Join(() => myCust, () => myCust.Id, () => myCont.CustomerId)
        .Select(() => result.FirstName, () => myCont.FirstName)
        .Select(() => result.LastName, () => myCont.LastName)
        .Select(() => result.CustomerId, () => myCont.CustomerId)
        .Select(() => result.CustomerName, () => myCust.Name)
        ;

    var query_str = new SqlServerCompiler().Compile(query).ToString();

    Assert.NotNull(query_str);
    Assert.Equal("SELECT [myCont].[FirstName] AS [Item1], [myCont].[LastName] AS [Item2], [myCont].[contact_customer_id] AS [Item3], [myCust].[Name] AS [Item4] FROM [Contacts] AS [myCont] \nINNER JOIN [Customer] AS [myCust] ON [myCust].[Id] = [myCont].[contact_customer_id]", query_str);
}

[Fact]
public void JoinBuilder()
{
    Contact myCont = null;
    Customer myCust = null;
    (string FirstName, string LastName, string CustomerId) result = default;

    var query = FluentQuery.Query()
        .From(() => myCont)
        .Join(() => myCust, (join) => join.WhereColumns(() => myCust.Id, () => myCont.CustomerId))
        .Select(() => result.FirstName, () => myCont.FirstName)
        .Select(() => result.LastName, () => myCont.LastName)
        .Select(() => result.CustomerId, () => myCust.Id)
        ;

    var query_str = new SqlServerCompiler().Compile(query).ToString();

    Assert.NotNull(query_str);
    Assert.Equal("SELECT [myCont].[FirstName] AS [Item1], [myCont].[LastName] AS [Item2], [myCust].[Id] AS [Item3] FROM [Contacts] AS [myCont] \nINNER JOIN [Customer] AS [myCust] ON ([myCust].[Id] = [myCont].[contact_customer_id])", query_str);
}

[Fact]
public void GroupBy()
{
    Customer myCust = null;
    Contact myCont = null;

    (string CustomerName, int ContactCount) result = default;

    var query = FluentQuery.Query()
        .From(() => myCont)
        .Join(() => myCust, (join) => join.WhereColumns(() => myCust.Id, () => myCont.CustomerId))
        .SelectCount(() => result.ContactCount, () => myCont.Id)
        .Select(() => result.CustomerName, () => myCust.Name)
        .GroupBy(() => myCust.Name)
        ;

    var query_str = new SqlServerCompiler().Compile(query).ToString();

    Assert.NotNull(query_str);
    Assert.Equal("SELECT COUNT(myCont.Id) AS Item2, [myCust].[Name] AS [Item1] FROM [Contacts] AS [myCont] \nINNER JOIN [Customer] AS [myCust] ON ([myCust].[Id] = [myCont].[contact_customer_id]) GROUP BY [myCust].[Name]", query_str);
}

[Fact]
public void OrderBy()
{
    Customer myCust = null;

    var query = FluentQuery.Query()
        .SelectAll(() => myCust)
        .OrderByColumn(() => myCust.Name)
        ;

    var query_str = new SqlServerCompiler().Compile(query).ToString();

    Assert.NotNull(query_str);
    Assert.Equal("SELECT [myCust].[Name] AS [Name], [myCust].[Id] AS [Id] FROM [Customer] AS [myCust] ORDER BY [myCust].[Name]", query_str);
}

[Fact]
public void OrderByAlias()
{
    Customer myCust = null;
    CustomerModel model = null;

    var query = FluentQuery.Query()
        .From(() => myCust)
        .SelectRaw(() => model.Name, "ISNULL({0}, 'Uknown')", () => myCust.Name)
        .OrderByAlias(() => model.Name)
        ;

    var query_str = new SqlServerCompiler().Compile(query).ToString();

    Assert.NotNull(query_str);
    Assert.Equal("SELECT ISNULL(myCust.Name, 'Uknown') AS Name FROM [Customer] AS [myCust] ORDER BY ISNULL(myCust.Name, 'Uknown')", query_str);
}

Nuget

FluentSqlKata

How to contribute

If you have any issues please provide us with Unit Test Example.

To become a contributer please create an issue ticket with such enqury.

Donations

Donate with Ӿ nano crypto (XNO).

FluentSqlKata Donations

Thank you!

Product Versions
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows
.NET Core netcoreapp2.0 netcoreapp2.1 netcoreapp2.2 netcoreapp3.0 netcoreapp3.1
.NET Standard netstandard2.0 netstandard2.1
.NET Framework net461 net462 net463 net47 net471 net472 net48
MonoAndroid monoandroid
MonoMac monomac
MonoTouch monotouch
Tizen tizen40 tizen60
Xamarin.iOS xamarinios
Xamarin.Mac xamarinmac
Xamarin.TVOS xamarintvos
Xamarin.WatchOS xamarinwatchos
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.0.1 147 8/6/2022
1.0.0 146 8/6/2022
1.0.0-beta1 72 8/3/2022