Tiraggo.ef

The Tiraggo Dynamic Query API for the Entity Framework


Project maintained by BrewDawg Hosted on GitHub Pages — Theme by mattgraham

The Tiraggo Dynamic Query API for the Entity Framework

Copyright © Mike Griffin 2013
Mikes Personal Site

License: MIT

NuGet Package install from within Visual Studio using the NuGet Package Manager.

Please, if you like Tiraggo.EF spread the word via Twitter or your Blog ...


Tired of LINQ and Lamba?

Tap into your T-SQL knowledge and write queries that actually make sense. Have you ever stumbled upon LINQ queries that are so unintelligible and complex you needed to crawl under your desk and have a good cry? Have you ever taken a look at the over complicated SQL generated by LINQ? And what's with this not being able to select certain columns? This is 2013 folks.

Now you can have the best of both worlds; the Tiraggo Dynamic Query API (based on EntitySpaces) and your Entity Framework entities.

The Syntax

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 SQL spit out 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);

using(MyEntities context = new MyEntities())
{
    IList<Employee> employees = q.ToList<Employee>(context);
}

Yep, you can actually select only the columns you desire and the SQL is extremely lean. Tiraggo.EF only requires a single .NET assembly and no config settings.

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.

There are more ways than ToList() to fetch data.

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

using (MyEntities context = new MyEntities ())
{
  // List
  List<ErrorLog> list = q.ToList<ErrorLog>(context);

  // Array
  ErrorLog[] array = q.ToArray<ErrorLog>(context);

  // Dictionary where "Key" is the Primary Key
  Dictionary<Guid, ErrorLog> dict = 
          q.ToDictionary<Guid, ErrorLog>(context);

  // Anonymous - useful when bringing back extra columns in a join 
  var anonymous = q.ToAnonymousType(context)
      .Select(p => new { p.ErrorLogId, p.Message }).ToList();
}

How does it work

A reference to "Tiraggo.DynamicQuery.EF.dll" will be added to your solution as a reference. This is the only assembly your application will to use at runtime to use your Tiraggo.EF query classes.

There are two ways you can generate your Tiraggo.EF query classes, from your EDMX files or from your database schema.

1) Using EDMX Files as Input

When you install the Tiraggo.EF NuGet package it will install two T4 templates into your Visual Studio solution. Both will run successfully but only one will generate code depending on whether you have V2 or V3 EDMX files. The templates will be installed in your Models folder.

Provide the name of your EDMX File

You will have to edit the two T4 templates and add the name of your EDMX file in both templates.

Like so:

// ******************************************
// *** CHANGE THIS TO YOUR EDMX FILE NAME ***
// ******************************************
string edxmFileName = @"MyModel.edmx";

Once you see which template generates your code you can remove the other if you like.

You can tell what version your EDMX files are by looking at them in XML format, the version is right at the top:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas...">

Notice below that the "TiraggoEF_v3.tt" template generated the Query class in my solution. This is because I am in VS2012 and generating V3 EDMX files.

ScreenShot

Tiraggo.EF Assemblies

There is also a "TiraggoEdmx.EF.dll" assembly added to a folder in your project named "TiraggoEdmx". This assembly is used by the Tiraggo.EF T4 templates and you do not need to ship this assembly. This assembly provides the low level metadata that Microsoft EDMX API refuses to serve up. For example, the mappings between your conceptual model and the storage model including primitive information such as low level SQL Server columns types like "nvarchar". I am making some enhancements in the assembly and will release "TiraggoEdmx" as a separate NuGet package on it's own. TiraggoEdmx is a far superior way to write T4 templates against EDMX files, everything is fully exposed.

2) Database Schema as Input

If you are using code first you will not have .EDMX files. There is a template that you can run on My2ndGeneration that will generate the same query classes in seconds. It's in the Tiraggo.EntityFramework namespaces. You can use the Cloud or Desktop version of My2ndGeneration

Back to the Syntax

Supported Operators

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

Sub Operators

More Samples

Select Top

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

using(MyEntities context = new MyEntities())
{
    IList<Employee> employees = q.ToList<Employee>(context);
}

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();
q.SelectAllExcept(q.Photo);  // We don't want to bring back the huge photo

using(MyEntities context = new MyEntities())
{
    IList<Employee> employees = q.ToList<Employee>(context);
}

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;

using (MyEntities context = new MyEntities ())
{
    long count = q.ToScalar<long>(context);
}

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;

using (MyEntities context = new MyEntities())
{
    IList<ErrorLog> errors = q.ToList<ErrorLog>(context);
}

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);

using (MyEntities context = new MyEntities())
{
    IList<ErrorLog> errors = q.ToList<ErrorLog>(context);
}

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;

using (MyEntities context = new MyEntities())
{
    IList<Order> orders = q.ToList<Order>(context);
}

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());

using (MyEntities context = new MyEntities())
{
    IList<ErrorLog> errors = q.ToList<ErrorLog>(context);
}

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")
);

using(MyEntities context = new MyEntities())
{
    IList<Order> theOrders = orders.ToList<Order>(context);
}

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")
);

using(MyEntities context = new MyEntities())
{
    IList<Order> theOrders = orders.ToList<Order>(context);
}

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);

using(MyEntities context = new MyEntities())
{
    IList<Order> oq = orders.ToList<Order>(context);
}

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));

using(MyEntities context = new MyEntities())
{
    IList<Territory> territories = tq.ToList<Territory>(context);
}

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));

using(MyEntities context = new MyEntities())
{
    IList<Employee> employees = eq.ToList<Employee>(context);
}

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));

using(MyEntities context = new MyEntities())
{
    IList<Order> orders = oq.ToList<Order>(context);
}

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);

using(MyEntities context = new MyEntities())
{
    IList<Order> orders = oiq.ToList<Order>(context);
}

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%"))
            )
        )
    )
);

using(MyEntities context = new MyEntities())
{
    IList<Order> orders = oq.ToList<Order>(context);
}

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));

using(MyEntities context = new MyEntities())
{
    IList<Order> orders = oq.ToList<Order>(context);
}

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);

using(MyEntities context = new MyEntities())
{
    IList<Order> orders = oq.ToList<Order>(context);
}

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);

using(MyEntities context = new MyEntities())
{
    IList<Order> orders = oq.ToList<Order>(context);
}

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);

using(MyEntities context = new MyEntities())
{
    IList<OrderDetails> details = oq.ToList<OrderDetails>(context);
}

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")
);

using(MyEntities context = new MyEntities())
{
    IList<Employee> employees = q.ToList<Employee>(context);
}

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);

using(MyEntities context = new MyEntities())
{
    IList<Employee> employees = q.ToList<Employee>(context);
}

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>"); 

using(MyEntities context = new MyEntities())
{
    IList<Employee> employees = q.ToList<Employee>(context);
}

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);

using(MyEntities context = new MyEntities())
{
    IList<Order> orders = oq.ToList<Order>(context);
}

The rest of the Tiraggo Suite

Tiraggo - A C# Cross Platform ORM Architecture derived from EntitySpaces (Full Xamarin Mono Touch Support).

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