Basic Query Concepts

Writing queries in dbExpression is fairly similar to writing a Linq query in C#. We'll explain the similarities as we cover these concepts:

• Query Composition - How to use dbExpression syntax to fluently build strongly-typed queries.
• Query Expressions - What they are and how they relate to a Linq query.
• Expression Elements - How they are used with a Query Expression and their similarity to Linq expressions.

Query Composition

Similar to Linq, dbExpression queries are authored fluently, where the query is constructed by chaining various methods. dbExpression's chaining signatures align to methods and keywords you would use when writing SQL statements in (T)SQL. Some dbExpression core nomenclature:

• Database Accessor - The root accessor class for building SQL queries.
• Query Type - The type of query executed against the database (methods correlate to one of SELECT, INSERT, UPDATE, DELETE, or Stored Procedure).
• Entities - Data package classes (plain old class objects) that represent the tables and views within your target database.
• Schema Accessors - Classes that represent each schema in your target database.
• Entity Accessors - Classes that represent the tables and views within each of your schemas.
• Field Accessors - Classes that represent the columns within each of your tables and views.
• QueryExpression - A compile-time expression that represents a query for execution against the target database. Here's the anatomy of a QueryExpression:

Query Expressions

When you fluently construct a query using dbExpression, the backing object is a QueryExpression. Conceptually, a QueryExpression is similar to a Linq expression, and has other constituent expressions used to compose the full QueryExpression, similar to the expressions in System.Linq.Expressions that are used to construct a Linq statement/query. A QueryExpression aligns to and is semantically consistent with database languages (TSQL), where Linq expressions are semantically consistent with .NET languages like C#.

SQL statements are different based on the operation performed against the database - a SELECT statement has clauses specific to SELECT statements, as do INSERT, UPDATE, and DELETE statements. So, there are several corresponding "base" QueryExpression types, a specific type for each statement type:

Expression Elements

Expression elements are the building blocks of dbExpression enabling fluent query composition, they are fundamental to building queries. Elements are dbExpression's type system, where the element types are used to help determine the return type(s) of queries, constrain allowed parameters with database functions, and constrain/adapt the composition of one or more elements into composite elements.

Some examples of expression elements:

dbo.Person  //The person entity, which represents
            //the 'Person' table in the 'dbo' schema

dbo.Person.Id //The id field of a person entity 
              //typed as an integer, which is an Int32FieldExpression

You don't need to understand all of the expression element types in dbExpression. You'll see their types via Intellisense when building queries, you won't generally dive into the inner workings of an expression element - you'll simply use them to author queries.

Let's look at a few examples using expression elements to compose composite expression elements (which are just other expression elements):

//a DateTimeElement
db.fx.IsNull(dbo.Person.BirthDate, DateTime.UtcNow)

//an Int32Element composed using addition
dbo.Person.CreditLimit + 10_000

//a StringElement composed using concatenation
dbo.Person.FirstName + " " + dbo.Person.LastName

//an Int16Element simulating the approximation age of a person.
//composed using several database functions
db.fx.Cast(db.fx.Floor(db.fx.DateDiff(DateParts.Day, dbo.Customer.BirthDate, db.fx.GetUtcDate()) / 365.25)).AsSmallInt()

All .NET CLR primitives have a corresponding expression element type (plus strings and byte arrays). We'll create expression elements for all the things in your database - and we'll also create expression elements for your custom types, things like enums.