Scribd
Upload
47 views9 pages

Query API

The document describes how to write queries using the EntitySpaces Query API. It provides examples of basic select queries, using default and custom conjunctions, aggregates, grouping, ordering, operators, and mixing AND/OR clauses. It also discusses simplifying queries by caching the query object in a local variable or adding a custom method.

Uploaded by

adikusdianto
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
47 views9 pages

Query API

The document describes how to write queries using the EntitySpaces Query API. It provides examples of basic select queries, using default and custom conjunctions, aggregates, grouping, ordering, operators, and mixing AND/OR clauses. It also discusses simplifying queries by caching the query object in a local variable or adding a custom method.

Uploaded by

adikusdianto
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

EntitySpaces Query API TM

Basic Select
Query
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Load();

Yields
SELECT * FROM [AggregateTest]

Default Conjunction
By default "AND" is used, but that can be overridden.

Query
EmployeesCollection emps = new EmployeesCollection();
emps.Query.Where(emps.Query.LastName.Like("A%"),
emps.Query.FirstName.Like("A%"));
emps.Query.Load();

Yields
SELECT * FROM [Employees]
WHERE ([LastName] LIKE @LastName1 AND [FirstName] LIKE @FirstName2)

Query - Sets the DefaultConjunction to "OR"


EmployeesCollection emps = new EmployeesCollection();
emps.Query.es.DefaultConjunction = esConjunction.Or;
emps.Query.Where(emps.Query.LastName.Like("A%"),
emps.Query.FirstName.Like("A%"));
emps.Query.Load();

Yields
SELECT * FROM [Employees]
WHERE ([LastName] LIKE @LastName1 OR [FirstName] LIKE @FirstName2)
Count
Query
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Select
(
aggTestColl.Query.Salary.Count("Count")
);
aggTestColl.Query.Load();

Yields
SELECT COUNT([Salary]) AS 'Count' FROM [AggregateTest]

Query
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.es.CountAll = true;
aggTestColl.Query.es.CountAllAlias = "Total";
aggTestColl.Query.Load();

Yields
SELECT COUNT(*) AS 'Total' FROM [AggregateTest]

Aggregates
Avg, Count, Min, Max, Sum, StdDev, and Var) all share the same syntax.

Query - Aggregate with Empty Alias uses ColumnName


AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Select
(
aggTestColl.Query.Salary.Sum()
);
aggTestColl.Query.Load();

Yields
SELECT SUM([Salary]) AS 'Salary' FROM [AggregateTest]

Query - Simple Aggregate with Alias


AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Select
(
aggTestColl.Query.Salary.Avg("Avg")
);
aggTestColl.Query.Load();

Yields
SELECT AVG([Salary]) AS 'Avg' FROM [AggregateTest]
Query - Two Aggregates
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Select
(
aggTestColl.Query.Salary.Sum("Sum"),
aggTestColl.Query.Salary.Min("Min")
);
aggTestColl.Query.Load();

Yields
SELECT SUM([Salary]) AS 'Sum',MIN([Salary]) AS 'Min'
FROM [AggregateTest]

Query - Aggregate with Distinct


AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Select
(
aggTestColl.Query.LastName.Count("Count", true)
);
aggTestColl.Query.Load();

Yields
SELECT COUNT(DISTINCT [LastName]) AS 'Count'
FROM [AggregateTest]

Query - Aggregate with Count


AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Select
(
aggTestColl.Query.Salary.Sum("Sum")
);
aggTestColl.Query.es.CountAll = true;
aggTestColl.Query.es.CountAllAlias = "Total";
aggTestColl.Query.Load();

Yields
SELECT SUM([Salary]) AS 'Sum' ,COUNT(*) AS 'Total'
FROM [AggregateTest]
Query - Aggregate with Where clause
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.es.CountAll = true;
aggTestColl.Query.es.CountAllAlias = "Total";
aggTestColl.Query.Where
(
aggTestColl.Query.IsActive.Equal("true")
);
aggTestColl.Query.Load();

Yields
SELECT COUNT(*) AS 'Total'
FROM [AggregateTest]
WHERE ([IsActive] = @IsActive1)
Group By
Query
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.es.CountAll = true;
aggTestColl.Query.es.CountAllAlias = "Count";
aggTestColl.Query
.Select (aggTestColl.Query.IsActive)
.GroupBy(aggTestColl.Query.IsActive);
aggTestColl.Query.Load();

Yields
SELECT [IsActive] ,COUNT(*) AS 'Count'
FROM [AggregateTest]
GROUP BY [IsActive]

Query - With two GroupBy's and a Where clause


AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.es.CountAll = true;
aggTestColl.Query
.Select
(
aggTestColl.Query.IsActive,
aggTestColl.Query.DepartmentID
)
.Where
(
aggTestColl.Query.IsActive.Equal(true)
)
.GroupBy
(
aggTestColl.Query.IsActive,
aggTestColl.Query.DepartmentID
);
aggTestColl.Query.Load();

Yields
SELECT [IsActive],[DepartmentID] ,COUNT(*) AS 'Count'
FROM [AggregateTest]
WHERE ([IsActive] = @IsActive1 )
GROUP BY [IsActive],[DepartmentID]
Query - Select, Where, GroupBy, and OrderBy
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.es.CountAll = true;
aggTestColl.Query
.Select (aggTestColl.Query.IsActive,
aggTestColl.Query.DepartmentID)
.Where (aggTestColl.Query.IsActive.Equal(true))
.GroupBy(aggTestColl.Query.IsActive,
aggTestColl.Query.DepartmentID)
.OrderBy(aggTestColl.Query.DepartmentID.Ascending,
aggTestColl.Query.IsActive.Ascending);
aggTestColl.Query.Load();

Yields
SELECT [IsActive],[DepartmentID] ,COUNT(*) AS 'Count'
FROM [AggregateTest]
WHERE ([IsActive] = @IsActive1 )
GROUP BY [IsActive],[DepartmentID]
ORDER BY [DepartmentID] ASC,[IsActive] ASC

Query - Select, Where, GroupBy WithRollup, and OrderBy


AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.es.CountAll = true;
aggTestColl.Query
.Select (aggTestColl.Query.IsActive,
aggTestColl.Query.DepartmentID)
.Where (aggTestColl.Query.IsActive.Equal(true))
.GroupBy(aggTestColl.Query.IsActive,
aggTestColl.Query.DepartmentID)
.OrderBy(aggTestColl.Query.DepartmentID.Ascending,
aggTestColl.Query.IsActive.Ascending);
aggTestColl.Query.es.WithRollup = true;
aggTestColl.Query.Load();

Yields
SELECT [IsActive],[DepartmentID] ,COUNT(*) AS 'Count'
FROM [AggregateTest]
WHERE ([IsActive] = @IsActive1 )
GROUP BY [IsActive],[DepartmentID] WITH ROLLUP
ORDER BY [DepartmentID] ASC,[IsActive] ASC
Operators in Queries
Query
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Where(aggTestColl.Query.IsActive == true);
aggTestColl.Query.Load();

Yields
SELECT * FROM [AggregateTest] WHERE ([IsActive] = @IsActive1)

Query
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Where(aggTestColl.Query.Salary >= 30.00);
aggTestColl.Query.Load();

Yields
SELECT * FROM [AggregateTest] WHERE ([Salary] >= @Salary1)

Distinct and Top in Queries


Query – Distinct Rows
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.Select
(
aggTestColl.Query.LastName,
aggTestColl.Query.FirstName
);
aggTestColl.Query.es.Distinct = true;
aggTestColl.Query.Load();

Yields
SELECT DISTINCT [LastName],[FirstName] FROM [AggregateTest]

Query – Top 5
AggregateTestCollection aggTestColl = new AggregateTestCollection();
aggTestColl.Query.OrderBy(aggTestColl.Query.Salary.Descending);
aggTestColl.Query.es.Top = 5;
aggTestColl.Query.Load();

Yields
SELECT TOP 5 * FROM [AggregateTest] ORDER BY [Salary] DESC
Mixing AND / OR in Queries
Query
EmployeesCollection emps = new EmployeesCollection();
emps.Query
.Select
(
emps.Query.EmployeeID,
emps.Query.FirstName,
emps.Query.LastName
)
.Where
(
emps.Query.Or
(
emps.Query.LastName.Like("%A%"),
emps.Query.LastName.Like("%O%")
),
emps.Query.BirthDate.Between("1/1/1940", "1/1/2006")
)
.OrderBy
(
emps.Query.LastName.Descending,
emps.Query.FirstName.Ascending
);
emps.Query.Load();

Yields
SELECT [EmployeeID],[FirstName],[LastName]
FROM [Employees]
WHERE (([LastName] LIKE @LastName1 OR [LastName] LIKE @LastName2)
AND [BirthDate] BETWEEN @BirthDate3 AND @BirthDate4)
ORDER BY [LastName] DESC,[FirstName] ASC
Simplifying Queries
Let's simplify the query above to something more readable. Of course, the queries below yield
the same sql so it's not re-listed.

Query - Cache the Query object in a local variable


EmployeesCollection emps = new EmployeesCollection();
EmployeesQuery q = emps.Query;
q.Select(q.EmployeeID, q.FirstName, q.LastName)
.Where
(
q.Or(q.LastName.Like("%A%"), q.LastName.Like("%O%")),
q.BirthDate.Between("1/1/1940", "1/1/2006")
)
.OrderBy(q.LastName.Descending, q.FirstName.Ascending);
q.Load();

Query - Better yet, add a method directly to your concrete query class.
public class EmployeesQuery : esEmployeesQuery
{
public bool CustomLoad()
{
Select(EmployeeID, FirstName, LastName, TitleOfCourtesy)
.Where
(
Or(LastName.Like("%A%"), LastName.Like("%O%")),
this.BirthDate.Between("1/1/1940", "1/1/2006")
)
.OrderBy(LastName.Descending, FirstName.Ascending);
return this.Load();
}
}

You might also like