Where

The Where() method integrates a condition in the operations that may use it (Select) or that require it as a mandatory part (Delete, Update, etc.).

A condition has two main characteristics:

1. it consists of a column (its value) compared with a pre-established value or with another column, whose comparison relationship is determined by a specific criterion (equal to, not equal to, greater than, less than, etc. ).
2. it can be linked to another or other conditions, with an inclusive (AND) or optional (OR) relationship.

A typical SQL Where clause can be described by an example like this:

… §WHERE§ ( column1 $=$ value1 §AND§ column2 $>=$ value2 ) §OR§ ( column1 $!=$ value )

Because a Where condition can be very complex, UniDB has various Where() methods override and utility classes for managing various scenarios.

---

Creating a Condition

UniDB uses a very practical way of establishing a relationship between a column and a value. Inside the Table instance, under the C property (C stands for Columns), there is the list of all that Table’s columns. Each column is a property of the Column class which exposes various comparison methods.

table_instance.§C§.column_name.$comparison_method$(%value%);

table_instance	The name of the variable which represents the Table you are working with.
C	The property containing all this Table’s columns.
column_name	The column you want to involve in this condition.
comparison_method	One of the methods for establishing comparison criteria.
value	The value you want to compare with the column’s value. The parameter is an object data type and can receive any kind of value. However, this value should be of the same type as the column’s type. For example, if the column is an int column, the value has to be an integer number. The parameter supports SQL Functions (🢂 see the SQL Functions page for details).

COMPARISON METHODS	SQL SYMBOL	RELATIONSHIP
Equal (value)	=	Column’s value is equal to value.
NotEqual (value)	!=	Column’s value is different from value.
Greater (value)	>	Column’s value is greater than value.
GreaterOrEqual (value)	>=	Column’s value is equal to or greater than value.
Less (value)	<	Column’s value is less than value.
LessOrEqual (value)	<=	Column’s value is equal to or less than value.
Like (value)	LIKE	Column’s string value contains value substring.
NotLike (value)	NOT LIKE	Column’s string value doesn’t contain value substring.
Between (value1, value2)	BETWEEN	Column’s value is between value1 and value2 (included).
NotBetween (value1, value2)	NOT BETWEEN	Column’s value is not between value1 and value2 (included).
In (values)	IN	Column’s value is one of the given values.
NotIn (values)	NOT IN	Column’s value is not one of the given values.
IsNull ( )	IS NULL	Column’s value is NULL.
NotNull ( )	IS NOT NULL	Column’s value is not NULL.
IsTrue ( )	= 1	Column’s (boolean) value is true.
IsFalse ( )	= 0	Column’s (boolean) value is false.

var TestDB = new UniDB.Test();
var myUsers = TestDB.GetTable_Users();

// The myUser's column 'username' value is equal to "mario".
// SQL equivalent: WHERE username = 'mario'
myUsers.C.username.Equal("mario")

// The myUser's column 'age' value is equal to or greater than 18.
// SQL equivalent: WHERE age >= 18
myUsers.C.age.GreaterEqual(18)

// The myUser's column 'weight' is between 60 and 90.
// SQL equivalent: WHERE weight BETWEEN (60,90)
myUsers.C.weight.Between(60, 90)

// The myUser's column 'country' is "Italy" or "France" or "Spain".
// SQL equivalent: WHERE country IN ("Italy", "France", "Spain")
myUsers.C.country.In("Italy", "France", "Spain")

---

Where (basic usage)

The simplest way to use the Where() method is with just one condition. In this case, you have just to specify the name of a column with a comparison method.

… .$Where$(%condition%) …

var TestDB = new UniDB.Test();
var myUsers = TestDB.GetTable_Users();

// SQL equivalent: WHERE age >= 18
_ = myUsers
    .Select()
    .Where(myUsers.C.age.GreaterEqual(18))
    ...

---

WhereAND, WhereOR

The WhereAND() and WhereOR() methods are two simple alternatives for creating clauses in which all the given conditions are connected by an AND or an OR logic. These methods expect you to define two or more conditions.

… .$Where*$(%condition1%, %condition2%, …, %conditionX%) …

var TestDB = new UniDB.Test();
var myUsers = TestDB.GetTable_Users();

// SQL equivalent: WHERE age >= 18 AND weight BETWEEN (60,90)
_ = myUsers
    .Select()
    .WhereAND(myUsers.C.age.GreaterEqual(18), myUsers.C.weight.Between(60, 90))
    ...

// SQL equivalent: WHERE country = "Italy" OR country = "France"
_ = myUsers
    .Select()
    .WhereOR(myUsers.C.country.Equal("Italy"), myUsers.C.country.Equal("France"))
    ...

---

Where (complex usage)

The Where() method has an override for creating complex clauses with nested conditions like this one:

… §WHERE§ ( column1 $=$ value1 §AND§ column2 $>=$ value2 ) §OR§ ( column1 $!=$ value §AND§ column3 $<=$ value )

In this case, you have to involve the AND and the OR classes with the static method Q:

#AND#.$Q$(%conditions%)

#OR#.$Q$(%conditions%)

Basically, these two methods return a group of conditions, connected by AND or OR, that can be linked again with an AND or an OR:

var TestDB = new UniDB.Test();
var myUsers = TestDB.GetTable_Users();

// SQL equivalent:
// WHERE (age <= 18 AND weight BETWEEN (30,50)) OR (age > 18 AND weight BETWEEN (51,90)) 
_ = myUsers
    .Select()
    .Where(
       OR.Q(
           AND.Q(myUsers.C.age.LessEqual(18), myUsers.C.weight.Between(30, 50),
           AND.Q(myUsers.C.age.Greater(18), myUsers.C.weight.Between(51, 90)
       )
    )
    ...