Tiraggo

What developer doesn't have a good grasp of the T-SQL syntax? The goal behind the Tiraggo API is to mimic the T-SQL syntax. This has two advantages, your learning curve is minimal and the generated SQL is very clean.

A Simple Sample with an InnerJoin

The sample below demonstrates a self join on the Employees table which is looking for all employees with an 'a' in their last name who have people reporting to them. Kind of silly but it shows off the syntax.

Full Intellisense Support

EmployeeQuery q = new EmployeeQuery("e");
EmployeeQuery q1 = new EmployeeQuery("e1");

q.Select(q.EmployeeID, q.LastName)  // To bind to combobox
    .Where(q.LastName.Like("%a%"))
    .InnerJoin(q1).On(q.EmployeeID == q1.ReportsTo)
    .OrderBy(q.LastName.Descending);

EmployeesCollection coll = new  EmployeesCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
    foreach(Empoyees emp in coll)
    {
        // do something
    }
}

Yep, you can actually select only the columns you desire and the SQL is extremely lean.

NOTE: InnerJoin is used above, also supported are RightJoin, LeftJoin, CrossJoin, and FullJoin.

Results from the Query Above

SELECT
   e.[EmployeeID],
   e.[LastName]  
FROM
   [Employees] e 
INNER JOIN [Employees] e1 
   ON e.[EmployeeID] = e1.[ReportsTo] 
WHERE
   e.[LastName] LIKE @LastName1 
ORDER BY
   e.[LastName] DESC

Compare that SQL to the SQL generated by a Entity Framework query which does the same thing and you'll be shocked.

Supported Operators

Use the native language syntax, it works as you expect it would.

+
-
*
/
%
>
<
<=
>=
==
!=
&& = SQL 'AND' in VB use "And"
|| = SQL 'OR' in VB use "Or"

Sub Operators

ToUpper - Convert to lower case
ToLower - Left trim any leading spaces
LTrim - Left trim any trailing spaces
RTrim - - Right trim any trailing spaces
Trim - Trim both leading and trailing spaces
SubString - Return a sub-string
Coalesce - Return the first non null evaluating expression
Date - Returns only the date of a datetime type
DatePart - Returns the value of part of a datetime value.
Length - Return the length
Round - Rounds the numeric-expression to the desired places after the decimal point.
Avg - Average
Count - Count operator
Max - Maximum Value
Min - Minimum Value
StdDev - Standard Deviation
Var - Variance
Sum - Summation
Cast - SQL Cast

More Samples

Select Top

EmployeesQuery q = new EmployeesQuery();
q.tg.Top = 1; // TOP
q.Where(q.EmployeeID == 1 && (q.LastName != "googy"));

// Use the single entity since we expect only 1 record
Employees emp = new Employees();
if(emp.Load(q))
{
    // Then we loaded at least one record
}

Result:

SELECT  TOP 1 * 
FROM [Employees] 
WHERE ([EmployeeID] = @EmployeeID1 AND [LastName] <> @LastName2)

SelectAllExcept

SelectAllExcept() is not really a SubQuery, just a convenient enhancement that allows you to select all except one or more listed columns.

EmployeeQuery q = new EmployeeQuery();
// We don't want to bring back the huge photo
q.SelectAllExcept(q.Photo);

EmployeeCollection coll = new EmployeeCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
}

Results:

SELECT [EmployeeID],[LastName],[FirstName],[Supervisor],[Age]
  -- not [Photo]
FROM [dbo].[Employee]

Getting the Count

ErrorLogQuery q = new ErrorLogQuery();
q.Where(q.Message.Like("%a"));
q.tg.CountAll = true;

long count = q.ExecuteScalar<long>();

Results:

SELECT COUNT(*) AS 'Count' 
FROM [dbo].[ErrorLog] 
WHERE [Message] LIKE @Message1

Paging

Using PageSize and PageNumber.

This is the traditional way of paging and works on all versions of SQL Server. You always need an OrderBy when sorting.

ErrorLogQuery q = new ErrorLogQuery();
q.Select(q.ErrorLogId, q.Method, q.Message);
q.OrderBy(q.DateOccurred.Descending);
q.tg.PageNumber = 2;
q.tg.PageSize = 20;

ErrorLogCollection coll = new ErrorLogCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
}

Results:

WITH [withStatement] AS 
(
    SELECT [errorlogid], 
        [method], 
        [message], 
        Row_number() OVER
        ( 
            ORDER BY [dateoccurred] DESC
        ) AS ESRN 
    FROM [dbo].[errorlog]
) 
SELECT * 
FROM [withStatement] 
WHERE esrn BETWEEN 21 AND 40 
ORDER BY esrn ASC

Using Skip and Take for paging.

Skip and Take Require Microsoft SQL 2012 at a minimum and is a much nicer syntax.

ErrorLogQuery q = new ErrorLogQuery();
q.Select(q.ErrorLogId, q.Method, q.Message);
q.OrderBy(q.DateOccurred.Descending);
q.Skip(40).Take(20);

ErrorLogCollection coll = new ErrorLogCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
}

Results:

SELECT [ErrorLogId],[Method],[Message]  
FROM [dbo].[ErrorLog] 
ORDER BY [DateOccurred] DESC 
OFFSET 40 ROWS 
FETCH NEXT 20 ROWS ONLY

With NoLock

OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");

oq.Select(oq.CustID, oq.OrderDate, "<sub.OrderTotal>");
oq.From
    (
        oiq.Select(oiq.OrderID,
            (oiq.UnitPrice * oiq.Quantity).Sum().As("OrderTotal"))
            .GroupBy(oiq.OrderID)
    ).As("sub");
oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID);
oq.tg.WithNoLock = true;

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

Notice that even though many query objects are being used you only need to set WithNoLock to true for the parent or main query object. The SQL generated is as follows:

Results:

SELECT o.[CustID],o.[OrderDate],sub.OrderTotal  
FROM 
(
   SELECT oi.[OrderID],
    SUM((oi.[UnitPrice]*oi.[Quantity])) AS 'OrderTotal'  
   FROM [OrderItem] oi WITH (NOLOCK) 
   GROUP BY oi.[OrderID]
) AS sub 
INNER JOIN [Order] o WITH (NOLOCK) 
ON o.[OrderID] = sub.[OrderID]

Full Expressions in OrderBy and GroupBy

This query doesn’t really make sense, but we wanted to show you what will be possible in the next release.

EmployeesQuery q = new EmployeesQuery(); 
q.Select(q.LastName.Substring(2, 4).ToLower()); 
q.OrderBy(q.LastName.Substring(2, 4).ToLower().Descending); 
q.GroupBy(q.LastName.Substring(2, 4).ToLower());

EmployeesCollection coll = new EmployeesCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
}

Results:

SELECT SUBSTRING(LOWER([LastName]),2,4) AS 'LastName' 
FROM [Employees] 
GROUP BY SUBSTRING(LOWER([LastName]),2,4) 
ORDER BY SUBSTRING(LOWER([LastName]),2,4) DESC

Select SubQuery

A SubQuery in a Select clause must return a single value.

OrderQuery orders = new OrderQuery("o");
OrderItemQuery details = new OrderItemQuery("oi");

orders.Select
(
    orders.OrderID,
    orders.OrderDate,
    details.Select
    (
        details.UnitPrice.Max()
    )
    .Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);

OrderCollection coll = new OrderCollection();
if(coll.Load(orders))
{
    // Then we loaded at least one record
}

Results:

SELECT o.[OrderID],o.[OrderDate], 
(
    SELECT MAX(oi.[UnitPrice]) AS 'UnitPrice'  
    FROM [dbo].[OrderItem] oi 
    WHERE o.[OrderID] = oi.[OrderID]
) AS MaxUnitPrice  
FROM [dbo].[Order] o

This is the same as the query above, but returns all columns in the Order table, instead of just OrderID and OrderDate. Notice that the Select clause contains orders, not orders.. The SQL produced will use the supplied alias o..

OrderQuery orders = new OrderQuery("o");
OrderItemQuery details = new OrderItemQuery("oi");

orders.Select
(
    orders, // this means orders.*
    details.Select
    (
        details.UnitPrice.Max()
    )
    .Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
);

OrderCollection coll = new OrderCollection();
if(coll.Load(orders))
{
    // Then we loaded at least one record
}

Results:

SELECT o.* 
(
    SELECT MAX(oi.[UnitPrice]) AS 'UnitPrice'  
    FROM [dbo].[OrderItem] oi 
    WHERE o.[OrderID] = oi.[OrderID]
) AS MaxUnitPrice  
FROM [ForeignKeyTest].[dbo].[Order] o

From SubQuery

An aggregate requires a GROUP BY for each column in the SELECT that is not an aggregate. Sometimes you wish to include columns in your result set that you do not wish to group by. One way to accomplish this is by using a SubQuery in the From clause that contains the aggregate the way you want it grouped. The outer query contains the results of the aggregate, plus any additional columns.

If you use a SubQuery in a From clause, you must give the From clause its own alias (shown below as "sub"). In the outer query, to refer to an aliased element in the From SubQuery, use the inline raw SQL technique to qualify the aggregate's alias with the From clause alias, i.e., "".

OrderQuery oq = new OrderQuery("o");
OrderItemQuery oiq = new OrderItemQuery("oi");

oq.Select(oq.CustID, oq.OrderDate, "<sub.OrderTotal>");
oq.From
(
    oiq.Select
    (
        oiq.OrderID,
        (oiq.UnitPrice * oiq.Quantity).Sum().As("OrderTotal")
    )
    .GroupBy(oiq.OrderID)
).As("sub");
oq.InnerJoin(oq).On(oq.OrderID == oiq.OrderID);

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

Results:

SELECT o.[CustID],o.[OrderDate],sub.OrderTotal  
FROM 
(
    SELECT oi.[OrderID],
    SUM((oi.[UnitPrice]*oi.[Quantity])) AS 'OrderTotal'  
    FROM [dbo].[OrderItem] oi 
    GROUP BY oi.[OrderID]
) AS sub 
INNER JOIN [dbo].[Order] o ON o.[OrderID] = sub.[OrderID]

Where SubQuery

In and NotIn are two of the most common operators used in a Where SubQuery. The following produces a result set containing Territories that an Employee is not associated with.

// SubQuery of Territories that Employee 1 is assigned to.
EmployeeTerritoryQuery etq = new EmployeeTerritoryQuery("et");
etq.Select(etq.TerrID);
etq.Where(etq.EmpID == 1);

// Territories that Employee 1 is not assigned to.
TerritoryQuery tq = new TerritoryQuery("t");
tq.Select(tq.Description);
tq.Where(tq.TerritoryID.NotIn(etq));

TerritoryCollection coll = new TerritoryCollection();
if(coll.Load(tq))
{
    // Then we loaded at least one record
}

Results:

SELECT t.[Description]  
FROM [dbo].[Territory] t 
WHERE t.[TerritoryID] NOT IN 
(
    SELECT et.[TerrID]  
    FROM .[dbo].[EmployeeTerritory] et 
    WHERE et.[EmpID] = @EmpID1
)

Exists evaluates to true, if the SubQuery returns a result set.

// SubQuery of Employees with a null Supervisor column.
EmployeeQuery sq = new EmployeeQuery("s");
sq.tg.Distinct = true;
sq.Select(sq.EmployeeID);
sq.Where(sq.Supervisor.IsNull());

// If even one employee has a null supervisor,
// i.e., the above query has a result set,
// then run a list of all employees.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID, eq.Supervisor);
eq.Where(eq.Exists(sq));

EmployeeCollection coll = new EmployeeCollection();
if(coll.Load(eq))
{
    // Then we loaded at least one record
}

Results:

SELECT e.[EmployeeID],e.[Supervisor]  
FROM [dbo].[Employee] e 
WHERE EXISTS 
(
    SELECT DISTINCT s.[EmployeeID]  
    FROM [dbo].[Employee] s 
    WHERE s.[Supervisor] IS NULL
)

Join(query).On(SubQuery)

SubQueries cannot be used directly within a Join(SubQuery) clause, but they can be used within a Join(query).On(SubQuery) clause.

// Query for the Join
OrderItemQuery oiq = new OrderItemQuery("oi");

// SubQuery of OrderItems with a discount
OrderItemQuery oisq = new OrderItemQuery("ois");
oisq.tg.Distinct = true;
oisq.Select(oisq.Discount);
oisq.Where(oisq.Discount > 0);

// Orders with discounted items
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oiq.Discount);
oq.InnerJoin(oiq).
    On(oq.OrderID == oiq.OrderID && oiq.Discount.In(oisq));

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

Results:

SELECT o.[OrderID],oi.[Discount]  
FROM [dbo].[Order] o 
INNER JOIN [dbo].[OrderItem] oi 
ON (o.[OrderID] = oi.[OrderID] AND oi.[Discount] IN  
(
    SELECT  DISTINCT ois.[Discount]  
    FROM [dbo].[OrderItem] ois 
    WHERE ois.[Discount] > @Discount1)
)

Correlated SubQuery

A correlated SubQuery is where the inner query relies on an element of the outer query. The inner select cannot run on its own. Below, the inner pq query uses the outer query's oiq.ProductID in the Where() clause.

OrderItemQuery oiq = new OrderItemQuery("oi");
ProductQuery pq = new ProductQuery("p");

oiq.Select(
    oiq.OrderID,
    (oiq.Quantity * oiq.UnitPrice).Sum().As("Total")
);
oiq.Where(oiq.ProductID
    .In(
        pq.Select(pq.ProductID)
        .Where(oiq.ProductID == pq.ProductID)
    )
);
oiq.GroupBy(oiq.OrderID);

OrderItemCollection coll = new OrderItemCollection();
if(coll.Load(oiq))
{
    // Then we loaded at least one record
}

Results:

SELECT oi.[OrderID],SUM((oi.[Quantity]*oi.[UnitPrice])) AS 'Total'  
FROM [dbo].[OrderItem] oi 
WHERE oi.[ProductID] IN 
(
    SELECT p.[ProductID]  
    FROM [dbo].[Product] p 
    WHERE oi.[ProductID] = p.[ProductID]
)  
GROUP BY oi.[OrderID]

Nested SubQuery

Tiraggo supports nesting of SubQueries. Each database vendor has their own limits on just how deep the nesting can go. Tiraggo supports two different syntax approaches to nested SubQueries.

Traditional SQL-style syntax is most useful if you already have a query designed using standard SQL, and are just converting it to a DynamicQuery.

OrderQuery oq = new OrderQuery("o");
CustomerQuery cq = new CustomerQuery("c");
EmployeeQuery eq = new EmployeeQuery("e");

// OrderID and CustID for customers who ordered on the same date
// a customer was added, and have a manager whose 
// last name starts with 'S'.
oq.Select(
    oq.OrderID,
    oq.CustID
);
oq.Where(oq.OrderDate
    .In(
        cq.Select(cq.DateAdded)
        .Where(cq.Manager.In(
            eq.Select(eq.EmployeeID)
            .Where(eq.LastName.Like("S%"))
            )
        )
    )
);

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

Results:

SELECT o.[OrderID],o.[CustID]  
FROM [dbo].[Order] o 
WHERE o.[OrderDate] IN 
(
    SELECT c.[DateAdded]  
    FROM [dbo].[Customer] c 
    WHERE c.[Manager] IN 
    (
        SELECT e.[EmployeeID]  
        FROM [dbo].[Employee] e 
        WHERE e.[LastName] LIKE @LastName1
    ) 
)

Nesting by query instance name can be easier to understand and construct, if you are starting from scratch, and have no pre-existing SQL to go by. The trick is to start with the inner-most SubQuery and work your way out. The query below produces the same results as the traditional SQL-style query above. The instance names are color coded to emphasize how they are nested.

// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));

// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));

// OrderID and CustID where the OrderDate is in the
// CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oq.CustID);
oq.Where(oq.OrderDate.In(cq));

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

Results:

SELECT o.[OrderID],o.[CustID]  
FROM [dbo].[Order] o 
WHERE o.[OrderDate] IN 
(
    SELECT c.[DateAdded]  
    FROM [dbo].[Customer] c 
    WHERE c.[Manager] IN 
    (
        SELECT e.[EmployeeID]  
        FROM [dbo].[Employee] e 
        WHERE e.[LastName] LIKE @LastName1
    )
)

Any, All, and Some

ANY, ALL, and SOME are SubQuery qualifiers. They precede the SubQuery they apply to. For most databases, ANY and SOME are synonymous. Usually, if you use an operator (>, >=, =, <, <=) in a Where clause against a SubQuery, then the SubQuery must return a single value. By applying a qualifier to the SubQuery, you can use operators against SubQueries that return multiple results.

Notice, below, that the ALL qualifier is set to true for the SubQuery with "cq.tg.All = true;".

// DateAdded for Customers whose Manager  = 3
CustomerQuery cq = new CustomerQuery("c");
cq.tg.All = true;
cq.Select(cq.DateAdded);
cq.Where(cq.Manager == 3);

// OrderID and CustID where the OrderDate is 
// less than all of the dates in the CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oq.CustID);
oq.Where(oq.OrderDate < cq);

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

Results:

SELECT o.[OrderID],o.[CustID]  
FROM [dbo].[Order] o 
WHERE o.[OrderDate] < ALL 
(
    SELECT c.[DateAdded]  
    FROM [ForeignKeyTest].[dbo].[Customer] c 
    WHERE c.[Manager] = @Manager1
)

Below, is a nested SubQuery. The ANY qualifier is set to true for the middle SubQuery with "cq.tg.Any = true;".

// Employees whose LastName begins with 'S'.
EmployeeQuery eq = new EmployeeQuery("e");
eq.Select(eq.EmployeeID);
eq.Where(eq.LastName.Like("S%"));

// DateAdded for Customers whose Managers are in the
// EmployeeQuery above.
CustomerQuery cq = new CustomerQuery("c");
cq.tg.Any = true;
cq.Select(cq.DateAdded);
cq.Where(cq.Manager.In(eq));

// OrderID and CustID where the OrderDate is 
// less than any one of the dates in the CustomerQuery above.
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oq.CustID);
oq.Where(oq.OrderDate < cq);

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

Results:

SELECT o.[OrderID],o.[CustID]  
FROM [dbo].[Order] o 
WHERE o.[OrderDate] < ANY 
(
    SELECT c.[DateAdded]  
    FROM [dbo].[Customer] c 
    WHERE c.[Manager] IN 
    (
        SELECT e.[EmployeeID]  
        FROM [dbo].[Employee] e 
        WHERE e.[LastName] LIKE @LastName1
    )
)

Case().When().Then().End() Syntax

EmployeesQuery q = new EmployeesQuery();
q.Select(q.EmployeeID, q.FirstName);
q.Where(q.EmployeeID == 2);

OrderDetailsQuery oq = new OrderDetailsQuery();
oq.Select
(
  oq.UnitPrice.Case()
    .When("yay").Then("wow")
    .When(oq.Exists(q)).Then("Exists!!")
    .When(oq.Quantity >= 50).Then(oq.UnitPrice)
    .When(oq.Quantity  / 50 / 50 == 0).Then(oq.UnitPrice)
    .When(oq.Quantity >= 50 && oq.Quantity < 250).Then(1)
    .When(oq.Quantity >= 250 && 
            oq.Quantity < 1000).Then(oq.UnitPrice * .80)
    .Else("Huh?")
    .End()
);
oq.Where(oq.Quantity.Sum() >= 50 && oq.Quantity.Avg() < 250);
oq.OrderBy(oq.OrderID.Descending, oq.Quantity.Descending);

OrderDetailsCollection coll = new OrderDetailsCollection();
if(coll.Load(OrderDetails))
{
    // Then we loaded at least one record
}

Results:

SELECT 
  CASE UnitPrice  
    WHEN 'yay' THEN 'wow' 
    WHEN  EXISTS 
    (
        SELECT [EmployeeID],[FirstName]  
        FROM [Employees] 
        WHERE [EmployeeID] = @EmployeeID1
    ) THEN 'Exists!!' 
    WHEN [Quantity] >= @Quantity2 THEN [UnitPrice] 
    WHEN (([Quantity] / 50) / 50) = @Expr3 THEN [UnitPrice] 
    WHEN ([Quantity] >= @Quantity4 AND [Quantity] < @Quantity5) THEN 1 
    WHEN ([Quantity] >= @Quantity6 AND [Quantity] < @Quantity7) THEN 
         ([UnitPrice] * 0.8) 
    ELSE 'Huh?'  
    END    
FROM [Order Details] 
WHERE (SUM([Quantity]) >= @Quantity8 AND AVG([Quantity]) < @Quantity9) 
ORDER BY [OrderID] DESC,[Quantity] DESC

Another Case/When Query

EmployeeQuery q = new EmployeeQuery();

q.Select
(
    q.LastName
        .Case()
            .When(q.LastName.Like("%a%"))
            .Then("Last Name Contains an A")
            .Else("Last Name Doesnt Contain an A")
        .End().As("SpecialLastName")
);

EmployeeCollection coll = new EmployeeCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
}

Having Clause

EmployeeQuery q = new EmployeeQuery();
q.Select(q.EmployeeID, q.Age.Sum().As("TotalAge"));
q.Where(q.EmployeeID.IsNotNull());
q.GroupBy(q.EmployeeID);
q.Having(q.Age.Sum() > 5);
q.OrderBy(q.EmployeeID.Descending);

EmployeeCollection coll = new EmployeeCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
}

Results:

SELECT [EmployeeID] AS 'EmployeeID',SUM([Age]) AS 'TotalAge' 
FROM [dbo].[Employee] 
WHERE[EmployeeID] IS NOT NULL 
GROUP BY [EmployeeID] 
HAVING SUM([Age]) > @Age2 
ORDER BY [EmployeeID] DESC

Union, Intersect, and Except

These might be kind of silly but they demonstrate syntax.

Union

EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");

// This leaves out the record with Age 30
eq1.Where(eq1.Age < 30);
eq1.Union(eq2);
eq2.Where(eq2.Age > 30);

Intersect

EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");

// This leaves out the record with Age 30
eq1.Where(eq1.FirstName.Like("%n%"));
eq1.Intersect(eq2);
eq2.Where(eq2.FirstName.Like("%a%"));

Except

EmployeeQuery eq1 = new EmployeeQuery("eq1");
EmployeeQuery eq2 = new EmployeeQuery("eq2");

// This leaves out the record with Age 30
eq1.Where(eq1.FirstName.Like("%J%"));
eq1.Except(eq2);
eq2.Where(eq2.FirstName == "Jim");

Raw SQL Injection Everywhere

There may be times when you need to access some SQL feature that is not supported by the DynamicQuery API. But, now having used and fallen in love with DynamicQuery, the last thing you want to do is stop and go write a stored procedure or create a view. We have always supported the raw injection feature in our Select statement, but it will soon be available almost everywhere. The way it works is you pass in raw SQL in the form of a string surrounded by < > angle brackets. That indicates that you want the raw SQL passed directly to the database engine “as is”.

Here is an example query. You would never write a query like this in reality. Tiraggo supports this simple query without having to use < > angle brackets. This is just to show all of the places that can accept the raw SQL injection technique:

EmployeesQuery q = new EmployeesQuery();
q.Select("<FirstName>", q.HireDate);
q.Where("<EmployeeID = 1>");
q.GroupBy("<FirstName>", q.HireDate);
q.OrderBy("<FirstName ASC>"); 

EmployeeCollection coll = new EmployeeCollection();
if(coll.Load(q))
{
    // Then we loaded at least one record
}

The SQL Generated is as follows (and works)

Results:

SELECT FirstName,[HireDate] AS 'HireDate'  
FROM [Employees] WHERE (EmployeeID = 1) 
GROUP BY FirstName,[HireDate] 
ORDER BY FirstName ASC

Of course, you could easily write the above query without injection, but you get the idea. The escape hatch will be available to you almost everywhere ….

EmployeesQuery q = new EmployeesQuery();
q.Select(q.FirstName);
q.Where(q.EmployeeID == 1);
q.OrderBy(q.FirstName.Ascending);
q.GroupBy(q.FirstName, q.HireDate);

Using the raw SQL injection techniques above will allow you to invoke SQL functions that we don’t support, including database vender specific SQL, and so on. Hopefully, you will almost never have to resort to writing a custom load method to invoke a stored procedure or an entirely hand written SQL statement. Of course, you can use our native API everywhere and just inject the raw SQL on the GroupBy for instance. You can mix and match to get the desired SQL.

Some Final Thoughts

The examples given above were designed to demonstrate (and test) usage in a variety of settings. They are not necessarily the simplest, or most efficient, way to achieve the desired result set. Think of them as an API usage guide, not as design guidelines. Most SubQueries can be re-written as Joins, and most Joins can be re-written as SubQueries. If, while coding, you are having trouble conceptualizing one approach, then try the other.

Technically, a JOIN's ON clause can take almost any where_condition, and Tiraggo supports many of these, including SubQueries. But, I agree with most authorities on the subject, that the ON clause should be reserved for the conditions that relate the two tables. All other conditional statements should be placed in a WHERE clause. Typically, the ON clause only contains a column from each table and a comparison operator.

For example, take the Join(query).On(SubQuery) example above. The much simpler query below returns the same result set. Its Where() clause not only simplifies the On() clause, but eliminates the SubQuery completely.

// Query for the Join
OrderItemQuery oiq = new OrderItemQuery("oi");

// Orders with discounted items
OrderQuery oq = new OrderQuery("o");
oq.Select(oq.OrderID, oiq.Discount);
oq.InnerJoin(oiq).On(oq.OrderID == oiq.OrderID);
oq.Where(oiq.Discount > 0);

OrderCollection coll = new OrderCollection();
if(coll.Load(oq))
{
    // Then we loaded at least one record
}

The rest of the Tiraggo Suite

Tiraggo.js - A JavaScript ORM to Compliment the Main Tiraggo Architecture