Modernized BY Paul Cordova --> @ EntitySpaces Modernized
Click here for the Glossy Site ...
Supports .NET Standard 2.x, .NET Core 2.x & 3.x .NET Framework 4.5.1, 4.6, 4.6.1
Available on Nuget for SqlServer, SQLite, MySQL or PostgreSQL
See Mike's Blog for EntitySpaces news ...
EntitySpaces is a Fluent API for SQL Server, SQLite, MySQL, PostgreSQL and more on the way. If you are familiar with the SQL syntax then you are already an expert in EntitySpaces. EntitySpaces is also high performance, transactional, and very intuitive. EntitySpaces Studio is used to generate your C# classes from your database schema.
In this example we are going to sum the total # of items for each order. Each order can have many order detail records so we group our query by OrderId and sum up the quantity as 'TotalQuantity'. Notice that we can access the derived 'TotalQuantity' column through the dynamic property.
Use of 'out var'
Notice the judicial use of the "our var" syntax of C# in the example code below. The "out var" syntax allows you to delcare a variable that is created for you such as the 'OrderDetailQuery' object 'od' in the InnerJoin() below. Then you are then free to use the 'od' variable throughout the query as is done in the Select() statement. This is also true for constructors. For example, notice how "out var o" is used on the creation of the OrdersQuery().
OrdersCollection coll = new OrdersQuery("o", out var o)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(o.OrderID == od.OrderID)
.Select(o.OrderID, od.Quantity.Sum().As("TotalQuantity"))
.GroupBy(o.OrderID)
.OrderBy(o.OrderID.Ascending)
.ToCollection<OrdersCollection>();
foreach(Orders order in coll)
{
Console.WriteLine(order.OrderID + " : " + order.dynamic.TotalQuantity);
}The SQL generated is just as you would expect.
SELECT o.[OrderID], SUM(od.[Quantity]) AS 'TotalQuantity'
FROM [Orders] o
INNER JOIN [Order Details] od ON o.[OrderID] = od.[OrderID]
GROUP BY o.[OrderID]
ORDER BY o.[OrderID] ASCThe output is as follows is ...
| OrderID | TotalQuantity |
|---|---|
| 10248 | 27 |
| 10249 | 49 |
| 10250 | 60 |
The sample below demonstrates a self join on the Employees table which is looking for all employees whose Supervisor has an 'a' in their last name. Kind of silly but it shows off the syntax.
EmployeesCollection coll = new EmployeesQuery("e", out var e) // Employees
.InnerJoin<EmployeesQuery>("r", out var reportsTo).On(e.ReportsTo == reportsTo.EmployeeID)
.Select(e.EmployeeID, e.LastName, reportsTo.LastName.As("SupervisorName"))
.Where(reportsTo.LastName.Like("%a%"))
.OrderBy(reportsTo.LastName.Descending).Distinct()
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}Notice that the SQL is extremely lean.
Results from the Query Above. SQL Parameters are always used to avoid SQL Injection Attacks.
SELECT DISTINCT e.[EmployeeID],e.[LastName],r.[LastName] AS 'SupervisorName'
FROM [Employees] e
INNER JOIN [Employees] r ON e.[ReportsTo] = r.[EmployeeID]
WHERE r.[LastName] LIKE @LastName1
ORDER BY r.[LastName] DESCAny, All, and Some all follow the same rules. You them with operators (==, !=, >, >=, <, or <=) in the "nested" syntax as shown below.
EmployeesCollection coll = new EmployeesQuery("q", out var q)
.Where(q.EmployeeID > (() =>
{
return new EmployeesQuery("e", out var q1)
.Select(q1.EmployeeID)
.Where(q1.EmployeeID.IsNotNull()).Any(); // <= Any indicated here !
})
)
.ToCollection<EmployeesCollection>();SQL Generated:
SELECT * FROM [Employees] q
WHERE q.[EmployeeID] > ANY
(
SELECT e.[EmployeeID]
FROM [Employees] e
WHERE e.[EmployeeID] IS NOT NULL
)This example uses OuterApply to select each customer and their last 2 orders.
CustomersCollection coll = new CustomersQuery("c", out var c)
.OuterApply<OrdersQuery>(out var o, () =>
{
return new OrdersQuery("o", out var subQuery)
.Select(subQuery.OrderID, subQuery.OrderDate)
.Top(2)
.Where(subQuery.CustomerID == c.CustomerID)
.OrderBy(subQuery.OrderDate.Descending, subQuery.OrderID.Ascending);
})
.Select(c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate)
.ToCollection<CustomersCollection>();
// Notice the "dynamic" property accessor for accessing the columns brought
// back from the Orders table.
foreach(Customers cust in coll)
{
Console.WriteLine(cust.CustomerID);
Console.WriteLine(cust.CompanyName);
Console.WriteLine(cust.dynamic.OrderID);
Console.WriteLine(cust.dynamic.OrderDate);
} SQL Generated:
SELECT c.[CustomerID],c.[CompanyName],o.[OrderID],o.[OrderDate]
FROM [Customers] c
OUTER APPLY
(
SELECT TOP 2 o.[OrderID],o.[OrderDate]
FROM [Orders] o
WHERE o.[CustomerID] = c.[CustomerID]
ORDER BY o.[OrderDate] DESC,o.[OrderID] ASC
) AS oEach customer and their last 2 orders.
| CustomerID | CompanyName | OrderID | OrderDate |
|---|---|---|---|
| ALFKI | Alfreds Futterkiste | 11011 | 04/09/1998 12:00:00 AM |
| ALFKI | Alfreds Futterkiste | 10952 | 03/16/1998 12:00:00 AM |
| ANATR | Ana Trujillo Emparedados y helados | 10926 | 03/04/1998 12:00:00 AM |
| ANATR | Ana Trujillo Emparedados y helados | 10759 | 11/28/1997 12:00:00 AM |
| ANTON | Antonio Moreno Taquería | 10856 | 01/28/1998 12:00:00 AM |
| ANTON | Antonio Moreno Taquería | 10682 | 09/25/1997 12:00:00 AM |
| AROUT | Around the Horn | 11016 | 04/10/1998 12:00:00 AM |
| AROUT | Around the Horn | 10953 | 03/16/1998 12:00:00 AM |
Here we use Union to find employees whose first name begins with F, C, or M. Of course, this isn't a great way to determine this data but it demonstrate syntax.
EmployeesCollection coll = new EmployeesQuery("q1", out var q1)
.Select(q1.EmployeeID, q1.FirstName, q1.LastName)
.Where(q1.FirstName.Like("F%"))
.Union(() =>
{
return new EmployeesQuery("q2", out var q2)
.Select(q2.EmployeeID, q2.FirstName, q2.LastName)
.Where(q2.FirstName.Like("C%"));
})
.Union(() =>
{
return new EmployeesQuery("q3", out var q3)
.Select(q3.EmployeeID, q3.FirstName, q3.LastName)
.Where(q3.FirstName.Like("M%"));
})
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT q1.[EmployeeID],q1.[FirstName],q1.[LastName]
FROM [Employees] q1 WHERE q1.[FirstName] LIKE @FirstName1
UNION SELECT q2.[EmployeeID],q2.[FirstName],q2.[LastName]
FROM [Employees] q2 WHERE q2.[FirstName] LIKE @FirstName2
UNION SELECT q3.[EmployeeID],q3.[FirstName],q3.[LastName]
FROM [Employees] q3 WHERE q3.[FirstName] LIKE @FirstName3OrdersCollection coll = new OrdersQuery("o", out var oQuery)
.Select(oQuery.OrderID, oQuery.EmployeeID)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(oQuery.OrderID == od.OrderID)
.InnerJoin<EmployeesQuery>("e", out var e).On(e.EmployeeID == oQuery.EmployeeID
&& oQuery.EmployeeID.In(() =>
{
return new EmployeesQuery("ee", out var ee)
.InnerJoin<OrdersQuery>("eo", out var eo).On(ee.EmployeeID == eo.EmployeeID)
.InnerJoin<OrderDetailsQuery>("eod", out var eod).On(eo.OrderID == eod.OrderID)
.Select(eo.EmployeeID)
.Distinct();
})
)
.ToCollection<OrdersCollection>();
if (coll.Count > 0)
{
// We loaded some records
}SQL Generated:
SELECT o.[OrderID], o.[EmployeeID]
FROM [Orders] o
INNER JOIN [Order Details] od ON o.[OrderID] = od.[OrderID]
INNER JOIN [Employees] e ON (e.[EmployeeID] = o.[EmployeeID] AND o.[EmployeeID] IN
(
SELECT DISTINCT eo.[EmployeeID]
FROM [Employees] ee
INNER JOIN [Orders] eo ON ee.[EmployeeID] = eo.[EmployeeID]
INNER JOIN [Order Details] eod ON eo.[OrderID] = eod.[OrderID]
)Exists evaluates to true if the SubQuery returns a result set.
EmployeesCollection coll = new EmployeesQuery("e", out var eq)
.Select(eq.EmployeeID, eq.ReportsTo)
.Where(eq.Exists(() =>
{
// SubQuery of Employees with a null Supervisor column.
return new EmployeesQuery("s", out var sq)
.Select(sq.EmployeeID).Where(sq.ReportsTo.IsNull()).Distinct();
}))
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT e.[EmployeeID], e.[ReportsTo]
FROM [Employees] e
WHERE EXISTS (
SELECT DISTINCT s.[EmployeeID]
FROM [Employees] s
WHERE s.[ReportsTo] IS NULL
)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.
// Territories that Employee 1 is not assigned to.
TerritoriesCollection coll = new TerritoriesQuery("t", out var tq)
.Select(tq.TerritoryID, tq.TerritoryDescription);
.Where(tq.TerritoryID.NotIn(() =>
{
return new EmployeeTerritoriesQuery("et", out var etq)
.Select(etq.TerritoryID)
.Where(etq.EmployeeID == 1);
}))
.ToCollection<TerritoriesCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT t.[Description]
FROM [dbo].[Territory] t
WHERE t.[TerritoryID] NOT IN
(
SELECT et.[TerrID]
FROM .[dbo].[EmployeeTerritory] et
WHERE et.[EmpID] = @EmpID1
) Notice how in the Select() statement we use the "escape hatch" mechanism and declare "<sub.OrderTotal>" as a string. What does this do? Anything you pass in within "<>" brackets is take "as-is". We need to do this here because the nested query in the From() clause is aliased as "sub" and we need to access the derived "OrderTotal" column. In an upcoming version the "out var" syntax will be supported on the Alias and you will no longer have to use the escape hatch. This isn't always true of the From clause it only has to do with this particular query.
OrdersCollection coll = new OrdersQuery("o", out var o)
.Select(o.CustomerID, o.OrderDate, "<sub.OrderTotal>")
.From<OrderDetailsQuery>(out var od, () =>
{
return new OrderDetailsQuery("od", out var subQuery)
.Select(subQuery.OrderID, (subQuery.UnitPrice * subQuery.Quantity).Sum().As("OrderTotal"))
.GroupBy(subQuery.OrderID);
}).As("sub")
.InnerJoin(o).On(o.OrderID == od.OrderID)
.ToCollection<OrdersCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT o.[CustomerID], o.[OrderDate], sub.OrderTotal
FROM
(
SELECT od.[OrderID],SUM((od.[UnitPrice] * od.[Quantity])) AS 'OrderTotal'
FROM [Order Details] od
GROUP BY od.[OrderID]
) AS sub
INNER JOIN [Orders] o ON o.[OrderID] = sub.[OrderID]A Nested Query in a Select clause must return a single value.
OrdersCollection coll = new OrdersQuery("o", out var orders)
.Select
(
orders.OrderID,
orders.OrderDate,
// Embed another query (see 'SQL Generated' below)
new OrderDetailsQuery("oi", out var details).Select(details.UnitPrice.Max())
.Where(orders.OrderID == details.OrderID).As("MaxUnitPrice")
)
.ToCollection<OrdersCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT o.[OrderID],o.[OrderDate],
(
SELECT MAX(oi.[UnitPrice]) AS 'UnitPrice'
FROM [Order Details] oi
WHERE o.[OrderID] = oi.[OrderID]
) AS MaxUnitPrice
FROM [Orders] oDetermines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.
OrdersCollection coll = new OrdersQuery("o", out var o)
.Select
(
o.Over.Sum(o.Freight).PartitionBy(o.EmployeeID).As("FreightByEmployee"),
o.Over.Sum(o.Freight).PartitionBy(o.EmployeeID, o.ShipCountry).As("FreightByEmployeeAndCountry")
)
.OrderBy(o.EmployeeID.Ascending, o.ShipCountry.Ascending)
.ToCollection<OrdersCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT
SUM(o.[Freight]) OVER( PARTITION BY o.[EmployeeID] ) AS 'FreightByEmployee',
SUM(o.[Freight]) OVER( PARTITION BY o.[EmployeeID], o.[ShipCountry] ) AS 'FreightByEmployeeAndCountry'
FROM [Orders] o
ORDER BY o.[EmployeeID] ASC,o.[ShipCountry] ASCThis might look like a complicated query but it's really quite simple. As you look at the code below think of it this way. Within the From() statement is a nested query. Also, notice how we grab aliased columns via the "out" parameter. We also give our nested query an alias of "sub". Finally, the outer query selects and orders columns from the nested query using the aliased columns as well as applies the OVER syntax over the aliased columns. The outer query also uses the sophisticated ROWS syntax. Notice how simple the SQL generated from this query actually is, and it looks just like the C# code.
// We grab these aliases in the nested query via "out" parameters
esAlias aliasCompany = null, aliasPeriod = null, aliasAmount = null, aliasItemCount = null;
OrdersCollection coll = new OrdersQuery("q", out var q)
.From<OrdersQuery>(out var sub, () => // mimic a CTE
{
// Nested Query
return new OrdersQuery("o", out var o)
.InnerJoin<CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
.Select
(
// We're going to grab the aliased columns here for re-use in the outer query later
o.Count().As("TotalItems", out aliasItemCount),
c.CompanyName.As("CompanyName", out aliasCompany),
o.OrderDate.DatePart("year").As("Period", out aliasPeriod),
((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2)
.Sum().Round(2).As("Amount", out aliasAmount)
)
.GroupBy(c.CompanyName, o.OrderDate.DatePart("year"));
}).As("sub")
// Now act on "sub" query columns
.Select(
aliasCompany(), aliasPeriod(), aliasAmount(), aliasItemCount(),
q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).OrderBy(aliasPeriod().Ascending)
.Rows.UnBoundedPreceding.As("CumulativeAmount"),
q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).As("TotalAmount")
)
.OrderBy(aliasCompany().Ascending, aliasPeriod().Ascending)
.ToCollection<OrdersCollection>();
if(coll.Count > 0)
{
// we loaded data
}SQL Generated:
SELECT
sub.[CompanyName],
sub.[Period],
sub.[Amount],
sub.[TotalItems],
SUM([Amount]) OVER( PARTITION BY [CompanyName] ORDER BY sub.[Period] ASC
ROWS UNBOUNDED PRECEDING ) AS 'CumulativeAmount',
SUM([Amount]) OVER( PARTITION BY [CompanyName] ) AS 'TotalAmount'
FROM
(
SELECT
COUNT(*) AS 'TotalItems',
c.[CompanyName] AS 'CompanyName',
DATEPART(year, o.[OrderDate]) AS 'Period',
CAST(SUM(ROUND((((1.00 - od.[Discount]) * od.[UnitPrice]) * od.[Quantity]), 2))
AS decimal(19, 2)) AS 'Amount'
FROM [Orders] o
INNER JOIN [Customers] c ON c.[CustomerID] = o.[CustomerID]
INNER JOIN [Order Details] od ON od.[OrderID] = o.[OrderID]
GROUP BY c.[CompanyName], DATEPART(year, o.[OrderDate])
)
AS sub
ORDER BY sub.[CompanyName] ASC, sub.[Period] ASCThe output is as follows is ...
| CompanyName | Period | Amount | TotalItems | CumulativeAmount | TotalAmount |
|---|---|---|---|---|---|
| Alfreds Futterkiste | 1997 | 2022.50 | 6 | 2022.50 | 4273.00 |
| Alfreds Futterkiste | 1998 | 2250.50 | 6 | 4273.00 | 4273.00 |
| Ana Trujillo Emparedados y helados | 1996 | 88.80 | 2 | 88.80 | 1402.95 |
| Ana Trujillo Emparedados y helados | 1997 | 799.75 | 4 | 888.55 | 1402.95 |
| Ana Trujillo Emparedados y helados | 1998 | 514.40 | 4 | 1402.95 | 1402.95 |
| Antonio Moreno Taquería | 1996 | 403.20 | 1 | 403.20 | 7023.97 |
| Antonio Moreno Taquería | 1997 | 5960.77 | 14 | 6363.97 | 7023.97 |
| Antonio Moreno Taquería | 1998 | 660.00 | 2 | 7023.97 | 7023.97 |
| Around the Horn | 1996 | 1379.00 | 5 | 1379.00 | 13390.65 |
| Around the Horn | 1997 | 6406.90 | 18 | 7785.90 | 13390.65 |
| Around the Horn | 1998 | 5604.75 | 7 | 13390.65 | 13390.65 |
| Berglunds snabbköp | 1996 | 4324.40 | 9 | 4324.40 | 24927.58 |
| Berglunds snabbköp | 1997 | 13849.02 | 27 | 18173.42 | 24927.58 |
| Berglunds snabbköp | 1998 | 6754.16 | 16 | 24927.58 | 24927.58 |
And and Or work just as you would expect, use parenthesis to control the order of precedence. You can also concatentat and use all kinds of operators in your queries. See the tables at the end of this document.
EmployeesCollection coll = new EmployeesQuery("e", out var q)
.Select(q.EmployeeID, (q.LastName + ", " + q.FirstName).As("FullName"))
.Where(q.EmployeeID > 4 && (q.EmployeeID < 10 || q.EmployeeID == 100))
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
}SQL Generated:
SELECT
e.[EmployeeID],
(e.[LastName] + ', ' + e.[FirstName]) AS 'FullName'
FROM [Employees] e
WHERE e.[EmployeeID] > @EmployeeID1
AND
(
e.[EmployeeID] < @EmployeeID2 OR e.[EmployeeID] = @EmployeeID3
)Here the Orders table is joined with the OrderDetails table. The Orders.OrderID column is brought back along with all columns from the OrderDetails table. Notice how the Select() statement uses 'od' without a column declared. This results in 'od.*' in the SQL.
OrdersCollection coll = new OrdersQuery("oq", out var o)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(o.OrderID == od.OrderID)
.Select(o.OrderID, od) // Notice the 'od' results in 'od.*'
.Where(od.Discount > 0)
.ToCollection<OrdersCollection>();
if (coll.Count > 0)
{
// data was loaded
}SQL Generated:
SELECT oq.[OrderID], od.*
FROM [Orders] oq
INNER JOIN [Order Details] od ON oq.[OrderID] = od.[OrderID]
WHERE od.[Discount] > @Discount1Employees emp = new EmployeesQuery("q", out var q)
.Where(q.ReportsTo.IsNotNull())
.OrderBy(q.LastName.Descending).Top(1)
.ToEntity<Employees>();
if (emp != null)
{
// Then we loaded at least one record
}SQL Generated:
SELECT TOP 1 *
FROM [Employees]
WHERE [ReportsTo] IS NOT NULL
ORDER BY [LastName] DESCSelectAllExcept() is just a convenient way to select all columns except one or more listed columns.
// We don't want to bring back the huge photo
EmployeesCollection coll = new EmployeesQuery("q", out var q)
.SelectAllExcept(q.Photo)
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT q.[EmployeeID],q.[LastName],q.[FirstName],q.[Title], -- all except q.Photo
FROM [Employees] qPageSize / PageNumber
This is the traditional way of paging and works on all versions of SQL Server. You always need an OrderBy when sorting.
EmployeesCollection coll = new EmployeesQuery("q", out var q)
.Select(q.EmployeeID, q.LastName)
.OrderBy(q.LastName.Ascending)
.PageNumber(2).PageSize(20)
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
}SQL Generated:
WITH [withStatement] AS
(
SELECT [EmployeeID],[LastName],
ROW_NUMBER() OVER( ORDER BY [LastName] ASC) AS ESRN
FROM [Employees]
)
SELECT *
FROM [withStatement]
WHERE ESRN BETWEEN 21 AND 40
ORDER BY ESRN ASCSkip / Take
Skip and Take Require Microsoft SQL 2012 at a minimum and is a much nicer syntax.
EmployeesCollection coll = new EmployeesQuery("q", out var q)
.Select(q.EmployeeID, q.LastName)
.OrderBy(q.LastName.Ascending)
.Skip(40).Take(20)
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
}SQL Generated:
SELECT [EmployeeID],[LastName]
FROM [Employees]
ORDER BY [LastName] ASC
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY SelectT DISTINCT clause to retrieve the only distinct values in a specified list of columns.
// Distinct list of Employee's who have orders ...
EmployeesCollection coll = new EmployeesQuery("e", out var e)
.Select(e.EmployeeID)
.InnerJoin<OrdersQuery>("o", out var o).On(e.EmployeeID == o.EmployeeID)
.Distinct()
.ToCollection<EmployeesCollection>();SQL Generated:
SELECT DISTINCT e.[EmployeeID]
FROM [Employees] e
INNER JOIN [Orders] o ON e.[EmployeeID] = o.[EmployeeID]EmployeesCollection coll = new EmployeesQuery("e", out var e)
.Select(e.EmployeeID)
.InnerJoin<OrdersQuery>("o", out var o).On(e.EmployeeID == o.EmployeeID)
.Where(o.Freight > 20)
.es.WithNoLock()
.ToCollection<EmployeesCollection>();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:
SQL Generated: (Notice that "WITH (NOLOCK)" was applied on both tables involved in the query)
SELECT e.[EmployeeID]
FROM [Employees] e WITH (NOLOCK)
INNER JOIN [Orders] o WITH (NOLOCK) ON e.[EmployeeID] = o.[EmployeeID]
WHERE o.[Freight] > @Freight1This query doesn’t really make sense, but we wanted to show you what will is possible.
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
}SQL Generated:
SELECT SUBSTRING(LOWER([LastName]),2,4) AS 'LastName'
FROM [Employees]
GROUP BY SUBSTRING(LOWER([LastName]),2,4)
ORDER BY SUBSTRING(LOWER([LastName]),2,4) DESCYou can cast your types to other SQL types using Cast()
OrderDetailsCollection coll = new OrderDetailsQuery("o", out var o)
.Select
(
(o.Quantity * o.UnitPrice).Cast(esCastType.Decimal, 34, 4).As("Cost")
)
.ToCollection<OrderDetailsCollection>();SQL Generated:
SELECT
CAST((o.[Quantity] * o.[UnitPrice]) AS decimal(34, 4)) AS 'Cost'
FROM
[Order Details] oEmployeesQuery 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
}SQL Generated:
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] DESCEmployeeCollection coll = new EmployeeQuery("e", out var q)
.Select(q.EmployeeID, q.Age.Sum().As("TotalAge"))
.Where(q.EmployeeID.IsNotNull())
.GroupBy(q.EmployeeID)
.Having(q.Age.Sum() > 5)
.OrderBy(q.EmployeeID.Descending)
.ToCollection<EmployeeCollection>();
if(coll.Count > 0)
{
// Then we loaded at least one record
}SQL Generated:
SELECT e.[EmployeeID] AS 'EmployeeID', SUM([Age]) AS 'TotalAge'
FROM [dbo].[Employee] e
WHERE e.[EmployeeID] IS NOT NULL
GROUP BY e.[EmployeeID]
HAVING SUM([Age]) > @Age2
ORDER BY e.[EmployeeID] DESCHere we are getting the count of Employees who have NULL as their ReportsTo ...
int count = new EmployeesQuery("e", out var q)
.Select(q.Count())
.Where(q.ReportsTo.IsNull())
.ExecuteScalar<int>();SQL Generated:
SELECT COUNT(*)
FROM [Employees] e
WHERE e.[ReportsTo] IS NULLThere 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:
EmployeesCollection coll = new EmployeesQuery("e", out var q)
.Select("<FirstName>", q.HireDate)
.Where("<EmployeeID = 1>")
.GroupBy("<FirstName>", q.HireDate)
.OrderBy("<FirstName ASC>")
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
// Then we loaded at least one record
}The SQL Generated is as follows (and works)
SQL Generated:
SELECT FirstName, e.[HireDate]
FROM [Employees] e
WHERE (EmployeeID = 1)
GROUP BY FirstName,[HireDate]
ORDER BY FirstName ASCOf 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);
.Where(q.EmployeeID == 1)
.OrderBy(q.FirstName.Ascending)
.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.
EntitySpaces collections have a 'Filter' property that allows you to use a Linq query to filter and sort a collection after it has been loaded. Basically, any Linq query is valid, you can use Where() and OrderBy() and so on. Setting the 'Filter' property doesn't remove any records, it just temporarily hides them. If you use foreach() you will only see those records that meet the Filter criteria. To remove the filter just set the 'Filter' property to null, all of the original records then become visible again.
using System.Linq;
EmployeesCollection coll = new EmployeesCollection();
if (coll.LoadAll())
{
// Filter on FirstName containing an "a"
coll.Filter = coll.AsQueryable().Where(d => d.FirstName.Contains("a"));
foreach (Employees employee in coll)
{
// Each employee's FirstName contains an 'a'
}
// Clear the filter
coll.Filter = null;
foreach (Employees employee in coll)
{
// All employees are now back in the list
}
}If you prefer you can use the old school syntax which doesn't use the generic methods with the "out var" technique. See the example below:
EmployeesQuery eQuery = new EmployeesQuery("e");
OrdersQuery o = new OrdersQuery("o");
OrderDetailsQuery od = new OrderDetailsQuery("od");
eQuery.Select(eQuery.EmployeeID)
.InnerJoin(o).On(eQuery.EmployeeID == o.EmployeeID)
.InnerJoin(od).On(o.OrderID == od.OrderID)
.Where(o.Freight > 20);
EmployeesCollection coll = new EmployeesCollection();
if(coll.Load(eQuery))
{
// The data was loaded
}EntitySpaces will serialize any derived columns which are brought back by a query via a JOIN, aggregates, or by creating an extra column on the fly via concatenation such as is done with "fullName" column shown in the example below. Even though there is not a "fullName" property on the Employees object the "fullName" value will still serialize correctly.
EmployeesCollection coll = new EmployeesQuery("e", out var e)
.Select
(
e.EmployeeID, e.LastName, e.FirstName,
(e.LastName + ", " + e.FirstName).As("fullName") // derived column
)
.OrderBy(e.LastName.Descending)
.ToCollection<EmployeesCollection>();
if (coll.Count > 0)
{
string json = JsonConvert.SerializeObject(coll);
}Notice the "fullName" column is present in the JSON, no need for intermediate classes or "newing" up anonymous objects.
[
{
"EmployeeID": 6,
"LastName": "Suyama",
"FirstName": "Michael",
"fullName": "Suyama, Michael"
},
{
"EmployeeID": 193,
"LastName": "Smith",
"FirstName": "Frank",
"fullName": "Smith, Frank"
}
]EntitySpaces is both Hiearchical and Transactional. If you are saving a nested set of hierarchical objects then a transaction is implicitly created for you. However, if you need to save two disparate unrelated objects as shown in the sample below then you should use an esTransactionScope to ensure they both succeed or fail as a unit.
using (esTransactionScope scope = new esTransactionScope())
{
Employees employee = new Employees();
employee.FirstName = "Mike";
employee.LastName = "Griffin";
employee.Save();
Products product = new Products();
product.ProductName = "Some Gadget";
product.Save();
scope.Complete(); // last line of using statement
}In this example below we are using the EntitySpaces hierarchical model and there is no need to declare an esTransactionScope.
// Create an order
Orders order = new Orders
{
OrderDate = DateTime.Now
};
// Add an OrderDetails Record to the Order
order.OrderDetailsCollection.Add(new OrderDetails
{
UnitPrice = 55.00M,
Quantity = 4,
ProductID = 8
});
order.Save(); // Saves hierarchically// Create a new Employee
Employees newEmp = new Employees();
newEmp.FirstName = "Joe";
newEmp.LastName = "Smith";
newEmp.Save();
// Load that same Employee
Employees employee = new Employees();
if (employee.LoadByPrimaryKey(newEmp.EmployeeID.Value))
{
// Modify that Employee
employee.FirstName = "Bob";
employee.Save();
// Delete that Employee
employee.MarkAsDeleted();
employee.Save();
}Collection are simple enumerable lists of single entities.
EmployeesCollection coll = new EmployeesCollection();
if (coll.LoadAll())
{
foreach (Employees emp in coll)
{
}
}Use the native language syntax, it works as you expect it would.
| Operator | Description |
|---|---|
| + | plus operator |
| - | minus operator |
| * | multiple operator |
| / | divison operator |
| % | mod operator |
| > | greater-than operator |
| < | less-than operator |
| <= | less-than or equal-to operator |
| >= | greater-than or equal to operator |
| == | equal to operator |
| != | not-equal to operator |
| && | and operator |
| || | or operator |
| Sub Operator | Description |
|---|---|
| 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 |
For information on the following operators see SELECT - OVER Clause (Transact-SQL).
Typical syntax is OVER( PARTITION BY 'clause' ORDER BY 'clause' ROWS or RANGE 'clause')
| Sub Operator | SQL Function |
|---|---|
| Over.RowNumber() | ROW_NUMBER() |
| Over.Rank() | RANK() |
| Over.DenseRank() | DENSE_RANK() |
| Over.PercentRank() | PERCENT_RANK() |
| Over.Ntile() | NTILE() |
| Sub Operator | SQL Function |
|---|---|
| Over.Avg() | AVG() OVER() |
| Over.Count() | COUNT() OVER() |
| Over.CountBig() | COUNT_BIG() OVER() |
| Over.Max() | MAX() OVER() |
| Over.Min() | MIN OVER() |
| Over.StdDev() | STDDEV() OVER |
| Over.StdDevP() | STDDEVP() OVER() |
| Over.Var() | VAR() OVER() |
| Over.VarP() | VARP() OVER() |
| Sub Operator | SQL Function |
|---|---|
| Over.CumeDist() | CUME_DIST() |
| Over.FirstValue() | FIRST_VALUE() |
| Over.LastValue() | LAST_VALUE() |
| Over.Lag() | LAG() |
| Over.Lead() | LEAD() |
| Over.PercentileCont() | PERCENTILE_CONT() |
| Over.PercentileDisc() | PERCENTILE_DISC() |
- Install EntitySpaces Studio
- SQL Server - EntitySpaces.ORM.SqlServer
- SQLite - EntitySpaces.ORM.SQLite
- MySQL - EntitySpaces.ORM.MySQL
- PostgreSQL - EntitySpaces.ORM.PostgreSQL
Generating your Classes via EntitySpaces Studio It's very simple. You only need to execute two templates. The Custom classes are generated only once, that is where you can add custom code and overide EntitySpaces functionality if need be. The Generated classes are generated any time your database schema changes, you never edit these classes.
However, first you will need to go to the "Settings" tab and then the "Connection" tab and connect to your database, there is a dialog box that can help you do that, it's very simple.
Setup SQL Server connection string in your C# .NET Project
// esDataProviderFactory is a one time setup
esProviderFactory.Factory = new EntitySpaces.Loader.esDataProviderFactory();
// Add a connection
esConnectionElement conn = new esConnectionElement();
conn.Provider = "EntitySpaces.SqlClientProvider";
conn.DatabaseVersion = "2012";
conn.ConnectionString = "User ID=sa;Password=blank;Initial Catalog=Northwind;Data Source=localhost";
esConfigSettings.ConnectionInfo.Connections.Add(conn);Setup SQLite connection string in your C# .NET Project
// esDataProviderFactory is a one time setup
esProviderFactory.Factory = new EntitySpaces.Loader.esDataProviderFactory();
// Add a connection
esConnectionElement conn = new esConnectionElement();
conn.Provider = "EntitySpaces.SQLiteProvider";
conn.DatabaseVersion = "2012";
conn.ConnectionString = @"Data Source=C:\MyFolder\Northwind.db3;Version=3;";
esConfigSettings.ConnectionInfo.Connections.Add(conn);Setup MySQL connection string in your C# .NET Project
// esDataProviderFactory is a one time setup
esProviderFactory.Factory = new EntitySpaces.Loader.esDataProviderFactory();
// Add a connection
esConnectionElement conn = new esConnectionElement();
conn.Provider = "EntitySpaces.MySqlProvider";
conn.DatabaseVersion = "2012";
conn.ConnectionString = "Database=Northwind;Data Source=localhost;User Id=myuser;Password=mypassword;";
esConfigSettings.ConnectionInfo.Connections.Add(conn);