Declare InQuery Variables

[ahmad-moussawi]

Add the ability to declare variables for the query especially when the variable is used more than once.

• Easier for debugging -> informative variable @Date instead of @p1, @p2, ....
• Enhance query abstraction, inner queries can assume that some variable are declared without the need to pass variable in C# code.

db.Query("Balances")
.WithVar("date", DateTime.UtcNow)
.WhereRaw("[ValueDate] > @date")
.WhereRaw("[ValueDate] < @date")

[mota57]

@ahmad-moussawi Please take a look when you have a chance.

[mota57]

@ahmad-moussawi

I already have the code to build just the declaration part for MySQL, SQL Server, and SQLite but the challenge would be the Where conditions. Let's review how can we generate the code.

For SQLite, I look in StackOverflow and I found this answer . In order to declare variables for SQLite, we can do it like in the listing 1.1

Listing 1.1

CREATE TEMP TABLE Variables (Name TEXT PRIMARY KEY, Value TEXT);
INSERT OR REPLACE INTO Variables VALUES ('var1', 'account 1');

SELECT *
FROM accounts WHERE name =  (SELECT  Value FROM Variables where Name = 'var1')

I think we can handle this in the method Compiler.PrepareResult and the SqlResult.ToString to replace the generated "p0X" variables to the custom define variables.

For PostgreSQL there is a way to build declarations, I didn't test it but base on this answer we can use something like in the listing 1.2

listing 1.2

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

solution for SQLIte
In the listing 1.2 you can see, we just have to make sure that the variables are not generated with single quotes e.g WHERE something = 'var1' . That applies as well to the other databases.

Conclusion
This is my update, I have to continue researching about how to do it with Oracle and Firebird, but I wonder if we can't do it in Oracle and Firebird should we continue working with this feature? @ahmad-moussawi

[for7raid]

Hi @ahmad-moussawi
In PR #184 you turned into this code

DECLARE @date DATETIME = ?
SELECT * FROM [Table] WHERE [Date] = @date;

but why you want to declare the inline var in raw sql and get very platform specific code?

I hope, that we need only replace 'unnamed' @p... params to named in binding collection,
so code

new Query("Table")
  .WithVar("@date", DateTime.UtcNow)
  .Where("Date", "@date")

should be compiled on every platform to

SELECT * FROM [Table] WHERE [Date] = @date;

with bindings collection is [{@Date, 2019-05-08 12:10:00}]

[ahmad-moussawi]

@mota57 thank you for the informative research, as you've mentioned Oracle, SQLite and Postgres seem that they don't support a direct way of variable declaration, and the provided solutions would add unwanted complexity.

so sticking to the previous approach you were working on is the best for now (same example provided by @for7raid).

[mota57]

Hey @ahmad-moussawi, as @for7raid said we might need to replace @px and I did that for a simple query but also I will have to test it each function at Compiler.Condition.cs to make sure it works for every case. I still have to figure it out how to build the binding collection, I didn't know that was possible without a declaration.

var query = db.Query("Products")
              .WithVar("@name", "Chai")
              .Where("ProductName", "@name");

[for7raid]

@mota57 your last picture is not I mean
My opinion that WithVar function should only add variable to bindings collection, nothing more. Then your can use this var in any part of the query, even in raw clause. No slq "declaration" needed.

var query = db.Query("Products")
              .WithVar("@name", "Chai")
              .WithVar("@date", DateTime.Now)
              .Where("ProductName", "@name")
              .WhereRaw("@date between [SaleStart] and [SaleEnd]");

goes to

select * from [Product] where [ProductName] = @name and @date between [SaleStart] and [SaleEnd]

and compile returns binsings
[{@name, "Chai"}, {@date, 2019-05-09}]

[mota57]

@for7raid thanks for the information I did some changes based on what you said, I add the variables to the binding property, I removed the declaration part, I tested what I have with methods like WhereIn, WhereRaw, Where, OrWhere, WhereIn and with nested conditions. So, it works but I have to make a couple more changes and complete the test cases in order to upload a new branch.

Update
Well basically from what I gather I will have to make sure it will work for the following functions.

• CompileRawCondition

• CompileInCondition

• CompileBasicCondition

• CompileQueryCondition

• CompileInQueryCondition

• CompileBasicStringCondition

• WhereStarts

• WhereEnds

• WhereContains

• WhereLike

• FireBirdCompiler.CompileBasicDateCondition

• PostGresCompiler.CompileBasicDateCondition

• SqliteCompiler.CompileBasicDateCondition

• CompileBasicDateCondition

• CompileNestedCondition

• CompileExistsCondition

• WhereExists This takes a query so variables must be defined inside the inner query, not the outer query. For instance it should be written like this

   var query = db.Query("Customers").WhereExists(q => q.From("Orders").WithVar("@postal", "8200").Where("ShipPostalCode", "@postal"));

but not like this

   var query = db.Query("Customers").WithVar("@postal", "8200").WhereExists(q => q.From("Orders").Where("ShipPostalCode", "@postal"));

• Having

• HavingRaw

• HavingEnds

• WhereDate

• WhereTime

• WhereDatePart

• CombineRaw

• Except

• Intersect

• Union

• With

• WithRaw

• FireBirdCompiler.CompileUnion this method contains the same logic from Compiler class so I decided to remove it.

• HavingTrue This method doesn't apply to variable instead to columns

• CompileBooleanCondition This method doesn't apply to variable instead to columns

• CompileBetweenCondition This is a generic method and it doesn't accept strings when attempt to using integers, or datetimes or whatever. So for instance if I want to use the following code it will not compile WithVar("@f2",1)WithVar("@f2",5)..WhereBetween<int>("y", "@f1", "@f2")

• CompileTwoColumnsCondition I can't implement it because dapper replace it with a string with quotes in case for sql server instead of a raw variable.

[ahmad-moussawi]

@mota57 amazing, if you need any help/assistance let us know.

[mota57]

@ahmad-moussawi ready

[ahmad-moussawi]

@mota57 thanks for the update, appreciate it, I will review it soon and get back to you

[mota57]

@ahmad-moussawi
Any news about this ?

[timbze]

I'm curious if there's any update on this?

[mota57]

no idea, I remember I move my code but at end never went to production.

[ahmad-moussawi]

Actually it's available in the latest prerelease.
Working on the documentation before the official release.
A big thanks for @mota57

[mota57]

oh i didnt know that, thanks for letting me know.

…

On Tue, Jan 21, 2020, 1:02 PM Ahmad Moussawi ***@***.***> wrote: Actually it's available in the latest prerelease. Working on the documentation before the official release. A big thanks for @mota57 <https://github.com/mota57> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#184?email_source=notifications&email_token=ABZYERP5LAKVEE6ME3SRPZLQ64TD7A5CNFSM4GPEYTY2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJQPIPY#issuecomment-576779327>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABZYERMC35IBDA2BLBRCFWTQ64TD7ANCNFSM4GPEYTYQ> .

[brgrz]

So this was never merged? I see no traces of WithVar() anywhere in the api. Was there ever another way introduced?