Home > Oracle Database, SQL Server, SQLAzure > CFQL Expressions

CFQL Expressions


CodeFluent Query Language (CFQL) is platform independent and is a mix of traditional SQL and object-oriented syntaxes. When writing your methods (Load, Loadone, Search, Count, Delete, Raw) you can provide expressions in your WHERE and ORDER BY statements. As a quick recall here’s the global CFQL syntax:

<method> (<arguments>) [from <source>] [<where> <expression>] [<order by> <expression>]

For starters, expressions support literals:

  • parameters (e.g. @MyArg)
  • integer (e.g. 2403)
  • real (e.g. 1966.2403)
  • string (e.g. ‘CodeFluent’)
  • boolean (e.g. true or false)
  • hexadecimal (e.g. 0xDEADBEEF)
  • date/time (e.g. #1966/24/03 08:00:00#)
  • guid (e.g. ‘EAE89191-76D4-4f67-9B96-1CB8CC8D27BC’)

Furthermore, expressions also support “Entity Path” a.k.a. “EPath” which uses object-oriented ‘dot’ type syntax to navigate between entities in the persistence layer. In practice an EPath expression can be properties (including relation properties or enumeration properties), or a path of entity properties separated by dots (e.g. Orders.Products.Price).

EPath expressions are transformed into a relational syntax tree by the CodeFluent Entities model-to-persistence engine and ultimately this syntax tree will be produced as a stored procedure.

Here’s a chunk of a sample model illustrating an EPath expression:

<Customer>

  <PaymentInfo typeName="PaymentInfo" />
   <cf:method name="LoadVisaCustomers" body="load(CreditCardType CreditCard) where PaymentInfo.CreditCard = CreditCardType.Visa" />

</Customer>

<PaymentInfo>
    <CreditCard typeName="CreditCardType" />
    <Customer typeName="Customer" />
</PaymentInfo>

<CreditCardTypeEnum>
    <Visa />
    <Mastercard />
</CreditCardTypeEnum>

In this example:

  • We defined a LoadVistaCustomers method on the Customer entity
  • The method loads a collection of Customers
  • The method has one argument of enumeration type CreditCartType (defined after)
  • The method loads all Customers that have a PaymentInfo with a Visa CreditCartType.

What’s great with EPath is that from this expression joins will be deduced automatically in SQL, there’s no need for me to write them.

Two more points of interests regarding EPath:

  1. Equalities can be done using entity names, you don’t need to use entity keys: “load(Role) where Role = @Role” instead of “load(int roleId) where Role.Id = @roleId”. This way changing an entity key to a composite entity key won’t break your methods 😉
  2. The ‘Dot’ notation is also available on collections (including Many-To-Many) such as: “load(string productCode) where Products.Code = @productCode
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s