Documentation - Query Composition - Syntax

On this page

The Query Class
Property Container
Query Instance
Anatomy of a Query
Fluent Syntax
Builder Syntax
A Complete Query
Fluent Syntax
Builder Syntax
Builder Syntax vs Fluent Syntax

The Query Class#

The Query class allows you to craft from the simplest to the most complex queries.

You can then pass your instance of the Query class to one of the many available methods in the repository for the query to be applied to operations like Retrieve, Update, Delete.

It is a generic class with one generic type parameter: the entity's property container.

Property Container#

When The Sharp Factory App generates the C# code in your solution, amongst other things, it generates a property container class for each entity, with one static field per column. Each field has the column's .NET type.

The naming convention is EntityName + Property. For example an Entity called Customer will have a corresponding class called CustomerProperty, and CustomerProperty.LastName is a Prop<CustomerProperty, string>.

Entities with navigation properties also get a navigation property container, EntityName + NavProperty, for example CustomerNavProperty.Orders. See Navigation Properties.

The enums CustomerProp and CustomerNavProp are generated too, but queries do not use them.

Query Instance#

Start by creating an instance of the Query class passing the entity's property container as generic type parameter like so:

// create an instance and// specify the entity's property containervar query = new Query<CustomerProperty>();

Pass that instance to one of the repository methods available on that Entity's repository like this:

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>();// pass the query to one of the repository methods// in this case the ToList on the Customer repositoryvar result = repo.AppDb.Sales.Customer.ToList(query);

Anatomy of a Query#

A Query is composed of multiple parts or sections.

Predicate: Here you specify all the where clauses.
Sorting: Here you specify all the sorting criteria.
Navigation Properties: Here you specify all the related entities to include in the result. Only entities that have navigation properties have this part.
Pagination: Here you specify the pagination criteria to apply to the query.

The different parts of the query are optional.

Fluent Syntax#

The following example demonstrates how to build the query "skeleton" using Fluent Syntax.

This means concatenating conditions in a single statement.

// create an instance and// specify the entity's property containervar query = new Query<CustomerProperty>()    .BeginPredicate()        // where conditions go here    .EndPredicate()    .BeginSorting()        // sorting criteria goes here    .EndSorting()    .BeginNavProps()        // navigation properties go here    .EndNavProps()    .BeginPagination()        // pagination settings go here    .EndPagination();

Builder Syntax#

The following example demonstrates how to build the query "skeleton" using Builder Syntax.

This means concatenating conditions using delegates.

Builder syntax can make it easier to dynamically craft queries for more complex scenarios.

// create an instance and// specify the entity's property containervar query = new Query<CustomerProperty>()    .Builder(builder =>    {        builder        .Predicate((predicate) =>        {            // where conditions go here        })        .NavProps((navProps) =>        {            // navigation properties go here        })        .Sorting((sort) =>        {            // sorting criteria goes here        })        .Pagination((pagination) =>        {            // pagination settings go here        });    });

A Complete Query#

All the parts together: the customers with a balance whose last name starts with "A" and whose status is "new" or "inactive", sorted by last name and then newest first, each with its orders and the orders' lines, 50 per page, the second page.

Fluent Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// customers with a balance, whose last name starts with "A"// and whose status is "new" or "inactive"var query = new Query<CustomerProperty>()    .BeginPredicate()        .Where(CustomerProperty.Balance).GreaterThan(0)        .Where(CustomerProperty.LastName).StartsWith("A")        .Where((group) =>        {            group            .Where(CustomerProperty.Status).Equals("new")            .Or(CustomerProperty.Status).Equals("inactive");        })    .EndPredicate()    .BeginSorting()        .OrderBy(CustomerProperty.LastName)        .OrderByDescending(CustomerProperty.CreatedDate)    .EndSorting()    .BeginNavProps()        .Append(CustomerNavProperty.Orders).Then(OrderNavProperty.Lines)    .EndNavProps()    .BeginPagination()        .PageSize(50)        .PageNumber(2)        .ReturnTotalCount(true)    .EndPagination();// pass the query to one of the repository methodsvar customers = repo.AppDb.Sales.Customer.ToList(query);// populated because ReturnTotalCount is truevar totalRecords = query.RecordCount;var totalPages = query.PageCount;

Builder Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// the same query in builder syntaxvar query = new Query<CustomerProperty>()    .Builder((builder) =>    {        builder        .Predicate((predicate) =>        {            predicate            .Where(CustomerProperty.Balance).GreaterThan(0)            .Where(CustomerProperty.LastName).StartsWith("A")            .Where((group) =>            {                group                .Where(CustomerProperty.Status).Equals("new")                .Or(CustomerProperty.Status).Equals("inactive");            });        })        .Sorting((sort) =>        {            sort            .OrderBy(CustomerProperty.LastName)            .OrderByDescending(CustomerProperty.CreatedDate);        })        .NavProps((navProps) =>        {            navProps            .Append(CustomerNavProperty.Orders).Then(OrderNavProperty.Lines);        })        .Pagination((pagination) =>        {            pagination            .PageSize(50)            .PageNumber(2)            .ReturnTotalCount(true);        });    });// pass the query to one of the repository methodsvar customers = repo.AppDb.Sales.Customer.ToList(query);

// the lambdas can also be written in a single expressionvar query = new Query<CustomerProperty>().Builder(b => b    .Predicate(p => p.Where(CustomerProperty.LastName).StartsWith("A"))    .Sorting(s => s.OrderBy(CustomerProperty.LastName))    .Pagination(g => g.PageSize(50).PageNumber(2))    .NavProps(n => n.Append(CustomerNavProperty.Orders)));

Every value is checked by the compiler against the type of its column, and each column offers only the conditions its type has. See Filtering and Conditions.

Builder Syntax vs Fluent Syntax#

They both produce equivalent SQL Queries.

Choosing one or the other is a matter of taste.

We recommend you experiment with both and find the best option for your use case.

For more information on how to craft queries and use filtering see the Filtering section of the documentation.