sqlkata/querybuilder

SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird

querybuilder's Issues

Support DDL Queries

Hi, i think this is really i want, about building flexible query.

But could you provide ddl query interface in future or can i contribute for that?

Below example is maybe expected that is my want to use ;)

var createTableQuery = new Query(connection)
   .CreateTable("table_name")
   .WithField("id", Field.long)
   .WithField("field_name", Field.Int)
   .WithField("field_name_2", Field.Varchar, 100)
   .WithForeignKey("fk_id", "target_table", Field.Int)
   .WithPkConstant("id" /* or with multiple columns" */);

thanks for your work!

It look great, thanks

SqlKata.Execution

@ahmad-moussawi

I personally feel like the execution library should be more inline with the format/interfaces that Dapper follow since that is going to be the de-facto execution handler here.

Also, it makes me nervous with all of the unchecked casting to XQuery; I can see people and probably myself forgetting and passing a raw Query object in for the InvalidCastException often.

Please take a moment to review the query-execution-2 branch. What I have in there isn't set in stone yet; I'd be open to input but wanted to get something up we could look at.

Notes:
a) TargetFrameworks have been extended:

1. QueryBuilder: netstandard1.3/2.0, and net45
2. QueryBuilder.Tests: netcoreapp2.0;net462
3. QueryBuilder.SqlServer.Tests: netcoreapp2.0;net462
4. QueryBuilder.Execution (both): netstandard2.0, net452/462

b) I followed a very Newtonsoft.Json-esque approach to configuration. Right now the only setting is the compiler, but there will probably be others going forward. I figure the standard use-case would be a global setting object but to allow for specialized requests; so all of the extension methods have a parameter for alternative settings. Right now, if settings aren't set, defaults to SqlServerCompiler but we can change that behavior no problem.

c) There are more methods we are going to call on Dapper; the ones I have so far in Execution2.Async.cs were auto-generated from the Dapper source by a parser I started writing using Roslyn. There are a bunch more calls we can facade-- I just need a little time to spend on learning Roslyn better ;D I think it's worth while to have a tool to generate the calls for everything we don't have to do manually.

c) I didn't spend much effort yet on refactoring test resources to share accross database test libraries; SqlServer.Tests will probably change for the better.

Compiler for Oracle queries

This library is fantastic... any chance that an Oracle compiler is on the roadmap? Thanks!

Delete statements with joins not supported

I made a typo on the alias, sorry about that.

Does not support delete statement joins
I'll try and work on a PR for this if I can finish work sometime this next week. I can see where it goes wrong in the compiler.

Example simple use case; I have a couple more complex ones too:

DELETE foo
FROM [dbo].[Foo] foo
JOIN [dbo].[Bar] bar ON foo.[FooId] = bar.[FooId]
WHERE bar.[IsWorking] = 0

Query as DELETE (broken):

new Query("dbo.Foo as foo") .Join("dbo.Bar as bar", "foo.FooId", "bar.FooId") .Where("bar.IsWorking", 0) .Delete()

Produces

DELETE FROM [dbo].[Foo] AS [foo] WHERE [bar].[IsWorking] = 0

Parameter bindings to Sql

Hi
First, great package, thanks.
Been looking for a good an linq based sql engine / mapper like this for a long time.. Great work.

We need to make sql for a dashboard component. We feed it with the whole query. It does not have an option to code add paramters.

Is there anything in the package that can keep the parameters in the query but supply a complete query with paramteres/binders and query as sql? i.e. so the query still uses paramteres but the parameter values are supplied in the complete sql string?

We can convert the binding, no issues, But is this inteeresting for the pacakage.=?

Thanks.

Nested CTE is not rendered in top level query

Amazing work on the library, been really enjoying using it for my use case.

My scenario has two CTEs. Due to program flow, I am attaching the first CTE to the second CTE query, expecting that both CTEs would be rendered in the top level query.

Given the following code:

var cte1 = new Query("Table1");
cte1.Select("Column1", "Column2");
cte1.Where("Column2", 1);

var cte2 = new Query("Table2");
cte2.With("cte1", cte1);
cte2.Select("Column3", "Column4");
cte2.Join("cte1", join => join.On("Column1", "Column3"));
cte2.Where("Column4", 2);

var mainQuery = new Query("Table3");
mainQuery.With("cte2", cte2);
mainQuery.Select("*");
mainQuery.From("cte2");

var compiler = new SqlServerCompiler();
var sql = compiler.Compile(mainQuery);

Current behaviour:
Rendered SQL:

WITH [cte2] AS (SELECT [Column3], [Column4] FROM [Table2] INNER JOIN [cte1] ON ([Column1] = [Column3]) WHERE [Column4] = @p0) SELECT * FROM [cte2]

Bindings: [@p0: 1, @p1: 2]

As you can see above, whilst CTE1 is not rendered in the SQL, the bindings do filter up which has the side effect of throwing off the correct parameter values for the query (the query doesn't execute so its a mute point) If I use AddComponent to reattach CTE1 manually to the mainQuery, I end up with 3 bindings as the CTE1 parameter binding is promoted a second time.

Expected behaviour:

WITH [cte1] AS (SELECT [Column1], [Column2] FROM [Table1] WHERE [Column2] = @p0), [cte2] AS (SELECT [Column3], [Column4] FROM [Table2] INNER JOIN [cte1] ON ([Column1] = [Column3]) WHERE [Column4] = @p1) SELECT * FROM [cte2]

Bindings: [@p0: 1, @p1: 2]

Do you check the wehe

Suggestion - Separate Project Responsibilities

Separate Project Responsibilities

I was looking at your new code and realized that now has a file named Query.Execute, it would not be better to make a project and use SqlKata only to generate SQL, for me it is mixing the project, that is, each project makes one one thing generates SQL the other uses SQL to write to the bank and so on?

File: https://github.com/sqlkata/querybuilder/blob/query_execution/QueryBuilder/Query.Execute.cs

I'm saying this because of AsInert and Insert and so on because if not your class that generates SQL is also resolving the Bank's share would the responsibility increase?

Comments

• #32 (comment)
• #32 (comment)
• #32 (comment)

Chunk repeats the data after the second page

I have over 400k records in a table and when I try to process them in chunks of 50 it returns the same two records after the second page.
The way I'm trying to use it:

using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
    var db = new QueryFactory(connection, new SqlServerCompiler());
    // form: 5215 Client Name
    var rex1 = new Regex(@"([0-9]+)\s([a-zA-Z]+\s[a-zA-Z]+)");
    // form: Client Name (123)
    var rex2 = new Regex(@"([a-zA-Z]+\s[a-zA-Z]+)\s\(([0-9]+)\)");
    db.Query("Appointments").Chunk(50, (rows, page) =>
    {
        Console.WriteLine($"**** fetching page {page} *****");
        foreach (var item in rows)
        {
            var jsonData = (string) item.__Data;
            var j = JsonConvert.DeserializeObject<JObject>(jsonData);
            if (j.ContainsKey("Details"))
            {
                var appointmentDetails = j["Details"].Value<string>();

                if (rex1.IsMatch(appointmentDetails))
                {
                    appointmentDetails = rex1.Replace(appointmentDetails,
                        match => match.Groups[0].Value + "Client " + item.PatientId.ToString());
                }
            
                if (rex2.IsMatch(appointmentDetails))
                {
                    appointmentDetails = rex1.Replace(appointmentDetails,
                        match => "Client " + item.PatientId.ToString() + " " + match.Groups[1].Value);
                }
            
                Console.WriteLine(appointmentDetails);
            }
        }
    });
}

Application type: Console
Target Framework: .NET Framework 4.7.1

Am I doing something wrong?

Refactor DynamicCompile

Currently it uses reflection for method selection for each condition clause (every type and instance).
And it do same things every time (i.e. find method & invoke it) for same condition types without caching.

Possible solns:

• visitor pattern
• cached compiled expressions

Visitor pattern is trivial

Provide constant strings for operators

First of all, great initiative!

One think I was thinking though... the primary reason we would use this library is to not having to write strings or anything else that doesn't offer compile-time checking. Even a short string like "=" seems unnecessary to me.

Could you implement an alternative way to express operators?

Instead of

new Query().Where("Id", "=", 10);

maybe you could support

new Query().Where("Id", x => x.Equals(10));

It would be another small step towards full compile-time correctness checking.

Travis-CI build failing

Was broken before, but made a little worse by my other PR. I think it's fixed.

PR#10

Where with brackets ( )

Hi

I need to have a where that e.g.
Where (foo = 1 AND bar = 2) or (foo = 3 and bar = 2)...

How to add the brackets fluently?

Thanks for a great package

Regards G..

Subquery not works

From docs

var query = new Query().From("Users")
                .Select("Id")
                .Select(q => q.From("Visits").Where("UserId", 10).Count(), "VisitsCount");

Expected:

SELECT "Id",
  (
    SELECT COUNT(*) FROM "Visits" WHERE "UserId" = 10
  ) AS "VisitsCount"
FROM "Users"

Actual:
SELECT "Id" FROM "Users"

It seems like QueryColumn("column" component) just ignored here and not handled in Columnize

could you provider some tips about combining dynamic conditions in where(func()), especially meaning 'or'

sorry for my pooring english skill. thank you.

I got the following trouble. I build a sqlbuilder service for my domain bussinessw with your sqlkata,and whatever the tables or properties I want to join and search in the postgresql are depending on the viewpage form's dynamic condition, such as adding a extra input text or operator(if you have heard of i2b2,a medical service,I do the same things. ).

so, the form may have serveral groups('组' in chinese),it is 'and' relation between those groups. and in a specific group, there were several conditions, it is 'or' relation between those condition.

between the group, I could use 'foreach' and your fluent api to continually adding the where method.but in a specific group, I can't guarantee the count of 'or' condition,so ……

query.From("Countries")
    .Where(q => 
        q.Where("Population", "<", 100)
         .OrWhere("Population", ">", 200) // i can't guarantee the count.
    )

so , could you provider some tips, thanks any way.if you do not understand my poor english, i am sorry

Join On with and

Hi

Is it possible to have and AND with the ONs in a join?

i.e:
FROM Foo INNER JOIN
Bar ON Foo.XId = Bar.XId AND Foo.YId = Bar.YId

There is an "OrOn" but how to do the AndOr?

Can i use sqlkata builder for sqlite3 database?

HI,

Can i use MySqlCompiler for Sqlite3 database?

Please help me.

Regards,

AS is not working on simple select

Hi

var actual = new Query().From("foo").As("bar"); should give
SELECT * FROM [foo] as [bar]
but gives
SELECT * FROM [foo]

i know it might not give meaning in such a simple query, but i am manually making pivot query and also in other circumstances the alias on the from is good to have (e.g. deletes)

bug: SqlServer pagination on page 2 and after that

Thanks for your great project.
In SqlServer when using pagination on page 2 and after that SQL throw exception for the compiled query.

I think there is 2 bug:

1. when transforming the query to subquery there is a control for existence column in the query and add a '' to column clause but column clause has component name 'select' not 'columns'. with change it an extra '' will be removed

2. the subquery should have name (alias) in Sql Server

Chunk throws exception on second page

I am using 1.0.0-beta-458 and with the following simple code I get an exception when it tries to return the second page of results:

var query = new Query("dbo.MyTable");

var db = new QueryFactory(connection, new SqlServerCompiler());

db.Query(query).Chunk(2, (rows, page) =>
{
});

It appears to be down to the code which adds the fake raw SQL clause (there is a comment about this in the code) which works for the first page as it includes a TOP clause. However, the second page query doesn't include this and so the generated SQL is invalid (there is a comma before the * in the second select):

SELECT * FROM (SELECT , *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [row_num] FROM [dbo].[MyTable]) AS [subquery] WHERE [row_num] BETWEEN @p0 AND @p1

SqlKata generating query with unfulfilled parameters

I have the following Dictionary<string, object> called datensaetze:

Key	Value
categoryID	5
customergroupID	1

Compiling this into a query:

                    var query = new Query(quelle.Tabelle).AsInsert(datensaetze);
                    var compiler = new MySqlCompiler();
                    var qStr = compiler.Compile(query).Sql;

results in the following query:

INSERT INTO `s_categories_avoid_customergroups` (`categoryID`, `customergroupID`) VALUES (@p0, @p1)

which is not executable. I can't use the QueryFactory, because I need the SQL string logged in a log file and possibly edited (see #60 )

[Bug] nested t-sql selects with limit do not compile / resolve correctly.

Hi
Great app.
If i have a nested select with a limit for t-sql the TOP 1 is resolving as " , ", not as TOP (1).

I will pull request the test as proof.,

stable release

Hi

I think it is time to remove the beta tag from the nuget.
Maybe still have a version 0.x.x? But not a pre-release.

Are the base functionality not stable?

Pivot

Hi

Great library!
Please tell me. Best way to make a pivot out of the query?

Automate build and nuget publishing in AppVeyor

Packages building process should be automated and published automatically on Nuget for both netfx and netcore frameworks

Support brace expansion for column names

e.g.

// this 
new Query("Users").Join("Profiles", "Profiles.UserId", "Users.Id")
  .Select("Users.{Id, Name, Age}", "Profiles.{Avatar, LastLoggedIn})

// instead of
new Query("Users").Join("Profiles", "Profiles.UserId", "Users.Id")
  .Select("Users.Id", "Users.Name", "Users.Age", "Profiles.Avatar", "Profiles.LastLoggedIn")

how to distinguish 'schema' in DeepJoin method ?

such as the flowing code

var q6 = new Query().From("streets").Select("test.cities.id","test.cities.name").DeepJoin("cities.countries.continents");
      var c6 = new Compiler().Compile(q6.Clone()).ToString();

if the table city,the table country,the table continent were exsited in diffrent schema, how could I build the query sql string, thanks for your help.

actually, I took the method that using set search_path to myschema,public in database's configuration level(I use postgresql), it help to avoid write specific schema name, but if your project could provider some elegant method……
Very grateful to you!

Problem SqlResult Property Bindings?

Example:

1. Problem

It was to have 2 parameters !!!!!

2. Problem

Problem resulting in ordering the parameters go wrong !!!

Code:

querybuilder/QueryBuilder/BaseQuery.cs

Line 46 in 4e5030d

var bindings = Get(item, engine)

var bindings = Get(item, engine)
                    .SelectMany(clause => clause.GetBindings(engine))
                    .Where(x => x != null);

Do you check the fields which are belong the table from the Where condition

thanks

SqlKata -v 1.0.0-beta-357 What has changed?

What were the new features or was it only fixes ?

Rename project files and update namespaces?

Would you like me to commit a PR to rename QueryBuilder/QueryBuilder.Tests to SqlKata/Tests.SqlKata (incl. namespace updates) or let me know.

Thanks,

XML Documentation on all public methods

The Problem

Howdy! So, I've started to play around with SqlKata on a pretty large project with a lot of team members. The existing documentation has been very helpful to get started with the basics. However, I've found myself reading the code quite a bit to get an idea of how more complex scenarios work.

The Use-Case

While this makes a case for simply beefing up the existing github website, I believe it would be more helpful to include detailed XML documentation for public members and classes. Most developers I know tend to explore a new library via:

• An IDE, by exploring XML documentation comments in the IDE's intellisense tooltip box
• Exploring a complete reference website, which can be generated from a project with XML documentation (doxygen). This could even be hosted on the Github site.
• Lastly, exploring the code - which becomes clearer with XML documentation comments.

The Question

Would this be a worthwhile contribution? Some developers leave these out as a choice, as to not clutter the code itself. If this is something that would be considered helpful, I'd gladly volunteer (without any promises as to timeframe) to begin writing this documentation. Thoughts?

string with only number is transform to int

If I do

.Update(
new[] { "myColumn" },
new object[] { "090" }
);

after compile I get
UPDATE my_table SET myColumn = 090

is a problem for me.
In my case, MyColumn is a Char(3)

New feature for Compiler Class?

Example:

var compiler = new MySqlCompiler();

Query q1 = new Query("people");
q1.Insert(new Dictionary<string, object>
{
	["name"] = "Peter",
	["created"] = DateTime.Now.AddDays(-2)
});

SqlResult r1 = compiler.Compile(q1);

var q2 = q1.NewQuery()
	.From("people")
	.Select("*");

SqlResult r2 = compiler.Compile(q2);

System.Console.WriteLine(r1);
System.Console.WriteLine(r2);

Result Correct:

I would like to create a code in the Compiler class where it would leave the same class as if it were a new instance without doing the NewQuery Command?

Example

var compiler = new MySqlCompiler();

Query q1 = new Query("people");
q1.Insert(new Dictionary<string, object>
{
	["name"] = "Peter",
	["created"] = DateTime.Now.AddDays(-2)
});

SqlResult r1 = compiler
  .Compile(q1);  //After this line q1 is at its default and initial value without new instance

var q2 = q1
	.From("people")
	.Select("*");

Having a single instance generating SQL that from compiling the same class could generate new SQL without the intervention of the NewQuery() method!

ERROR when fire twice SqlServerCompiler().Compile(query)

When I try to convert my Paginated Query object to string

In this case, if my object Query contains SKIP or OFFSET and TAKE or LIMIT

var query = new Query()
				.Select("Id", "Name")
				.From("MyTable")
				.OrderBy("Name")
				.Limit(20)
				.Offset(1);

When I try convert to string
SqlServerCompiler compiler = new SqlServerCompiler(); string myQuery = compiler.Compile(query).ToString();

When I invoke a compiler(Query) the first time, the result is
SELECT TOP (20) [Id], [Name] FROM [MyTable] ORDER BY [Name]
IT'S OK

And if I need Invoke for second time the same Query, the result is
SELECT , [Id], [Name] FROM [MyTable] ORDER BY [Name]
IT'S WRONG The TOP (20) are missing and put a comma at this place

Release a netstandard compatible version

We'd like to use this library in a netstandard1.3 project, but the latest version on Nuget only supports netcoreapp1.0. Would it be possible to release another beta version with the netstandard support in the master branch?

Bindings are stateful

As the title states, Bindings is stateful for QueryFactory. I'm using it like so:

            var rows = new[]
            {
                new object[] {Guid.NewGuid(), -10.0, 40},
            };
            var columns = new[] {"id", "lat", "lng"};
            // This is okay
            _db.Query(_tableName).Insert(columns, rows);
            _db.Query(_tableName).First<Node>();

this fails because after Insert, the Bindings for the PostgresCompiler / SqlResult has the bindings set to the row values, which causes First to fail, as the LIMIT clause gets an unexpected parameter (the UUID in this case).

I am unsure whether this i intended, but the proper usage right now for me is to reinstantiate the PostgresCompiler and QueryFactory every time

Realise it is on the roadmap but executing stored procedures would really make this a useful library....

Hi,

Looks like a great library. But it needs stored procedure support.

Make WrapTable method in AbstractCompiler virtual

I'd like to create a compiler subclass to deal with our table naming conventions. We have period (.) characters in some database table names. This gets incorrectly split.

I'd like to provide additional logic on how SqlKata splits the table names in this situation. If the WrapTable method is virtual, I can override and provide my additional logic. Alternatively, allowing escape characters in the split operations of Wrap() however that felt like a performance penalty for something that probably isn't common.

Dot net 4.5 not supported?

I am getting following error when installing from Nuget

PM> Install-Package SqlKata -Pre
Installing 'SqlKata 1.0.0-beta-32'.
Successfully installed 'SqlKata 1.0.0-beta-32'.
Adding 'SqlKata 1.0.0-beta-32' to QueryBuilder.
Uninstalling 'SqlKata 1.0.0-beta-32'.
Successfully uninstalled 'SqlKata 1.0.0-beta-32'.
Install failed. Rolling back...
Install-Package : Could not install package 'SqlKata 1.0.0-beta-32'. You are trying to install this package into a project that targe
ts '.NETFramework,Version=v4.5', but the package does not contain any assembly references or content files that are compatible with t
hat framework. For more information, contact the package author.
At line:1 char:16

• Install-Package <<<< SqlKata -Pre
  • CategoryInfo : NotSpecified: (:) [Install-Package], InvalidOperationException
  • FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand

Update docs /readme

Update docs and readme with new nuget build number and to show support for net45

Getting an Syntax error try limit offset on SQL Server

Hi! Nice Project!

I'm on the start with sqlkata and I step in to a probelm with ms sql server (2012).

I was try your doc example for limit and offset with a northwind db :

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [OrderID]) AS [row_num] FROM [Orders Qry]) WHERE [row_num] BETWEEN 35 AND 48

The SqlServer don't except the row_num from the inner select. It them to need something like this:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [OrderID]) AS [row_num] FROM [Orders Qry])As rowsorder WHERE [rowsorder].row_num BETWEEN 35 AND 48

Don't know if I did something wrong. Can you please check, if the compiler works correctly?
Tanks!

Sorry, for my bad English. hope you get the sense out of it!

Greetings from Germany

sascha

How can I add ON DUPLICATE KEY UPDATE to my query?

I'm currently adding it via regex, but there's got to be a way with SqlKata, right?

.NET Standard 2.0 ?

You could add support for .NET Standard 2.0 ?

Inserting multiple rows in one query

I expected the following code:

    var query = new Query("table")
        .Insert(new Dictionary<string, object>
        {
            ["Col1"] = 3,
            ["Col2"] = "blue"
        })
        .Insert(new Dictionary<string, object>
        {
            ["Col1"] = 4,
            ["Col2"] = "red"
        });

to yield the following SQL for ToString():

INSERT INTO [table] ([Col1], [Col2]) VALUES (3, 'blue'), (4, 'red')

but instead it yields:

INSERT INTO [table] ([Col1], [Col2]) VALUES (4, 'red')

Only the final call to Query.Insert has any effect. I'm happy to submit a PR for this feature if you're interested.

Is it planned to add base ddl support?

create database
create table
alter column
add constraint
create index
...

It would be nice to add this functionality.

Returning clause

Thanks for your project, that help with writing pure SQL queries 🙂

It will be cool to have that feature in query builder.
Http://knexjs.org/#Builder-returning

Use `[]` as column identifiers placeholders in raw expressions instead of `{}`

Since {} are used in string interpolation in c#, avoiding these characters is more recommended

var colName = "Age";

// this 
db.Query("Users").SelectRaw($"[Id], count([{colName}])")
// instead of
db.Query("Users").SelectRaw($"{{Id}}, count({{{colName}}})")

WhereNotContains not implemented

Hi,

It looks like the WhereNotContains method has not been implemented (v1.0.0.465) - is this an oversight?

Thanks!
Mark