CFQL Raw methods


CodeFluent Query Language (aka CFQL) allows developers to define platform agnostic methods. For exemple you can write:

LOAD(FirstName) WHERE FirstName STARTSWITH @FirstName 

This CFQL method will be translated to SQL by the persistence producer you decide to use. This is very powerful but when you want to write advanced procedures, you have to write platform specific code.

Read more about CFQL: https://blog.codefluententities.com/2013/07/25/hands-on-cfql/.

Inline SQL code

You can inline SQL code in CFQL by surrounding it with “[“ and “]”:

LOAD(FirstName, int year) WHERE FirstName STARTSWITH @FirstName AND [DATEPART(yy, $Customer::DateOfBirth$) = @year] 

You have to set CheckLevel to None to confirm that you understand what you are doing:

Method Properties

The SQL code generated:

CREATE PROCEDURE [dbo].[Customer_LoadByFirstNameAndYear]
(
 @FirstName [nvarchar] (256),
 @year [int],
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Customer].[Customer_Id], [Customer].[Customer_FirstName], [Customer].[Customer_LastName], [Customer].[Customer_DateOfBirth] 
    FROM [Customer]
    WHERE (([Customer].[Customer_FirstName] LIKE (@FirstName + '%')) AND DATEPART(yy, [Customer].[Customer_DateOfBirth]) = @year)

SQL method

Sometimes you need to write a custom method directly in SQL. Set the text to:

  • LOAD(arguments) RAW when the return type correspond to a collection of the current entity
  • LOADONE(arguments) RAW when the return type correspond to one instance of the current entity
  • RAW(arguments) otherwise. You have to specify the return type in the property grid

If you want to target multiple DBMS, you have to write one raw body by DBMS:

CodeFluent Query Language Editor

SQL code generated:

CREATE PROCEDURE [dbo].[Customer_LoadRaw]
(
 @year [int],
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT * FROM Customer 
WHERE DATEPART(yy, [Customer].[Customer_DateOfBirth]) = @year

Use persistent view

Persistent views will be translated to SQL views. Persistent views are a key point since they can be used:

  • To focus, simplify, and customize the perception each user has of the database.
  • To control access to rows and columns of data.
  • To aggregate data for performance.

Persistent views in CodeFluent Entities are attached to the entity concept: an entity can have multiple views. Views can be used for CFQL methods Load, LoadOne, Count, Delete and Search
Views can be auto-generated or you can provide the SQL code.

Create the view:

View Editor

CustomerView

Use the view in a CFQL method:

LoadFromView

The generated SQL code:

CREATE VIEW [dbo].[vCustomerCustomerView]
AS
SELECT 
    [Customer].[Customer_Id], 
	[Customer].[Customer_DateOfBirth], 
	[Customer].[Customer_FirstName] + ' ' + [Customer].[Customer_LastName] AS FullName
FROM Customer

CREATE PROCEDURE [dbo].[Customer_LoadFromView]
(
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT DISTINCT [vCustomerCustomerView].[Customer_Id], [vCustomerCustomerView].[Customer_DateOfBirth], [vCustomerCustomerView].[FullName] 
    FROM [vCustomerCustomerView]
    WHERE ([vCustomerCustomerView].[FullName] LIKE 'J%')

Target Name Transformation (TNT)

Using the name of a column in a RAW method is not safe. Indeed CodeFluent Entities allows to define its own naming convention. So if you write the name of a column in a raw method and then you change the naming convention of your project, your method won’t work anymore.
To handle this case, CodeFluent Entities introduce TNT. In a Raw method you can refers to a column by using for example “$Customer::DateOfBirth$”. This will be replaced by CodeFluent Entities by the name of the column corresponding to the property “DateOfBirth” of the entity “Customer”.

TNT supports the following syntaxes:

  • $[EntityName]$ corresponds to the table name,
  • $[PropertyName]$ corresponds to the property name,
  • $[EntityName]::[PropertyName]$ corresponds to the column name,
  • $[EntityName]:[ViewName]$ corresponds to the view name,
  • $[EntityName]:[ViewName]:[PropertyName]$ corresponds to a column name in the defined view,
  • $[Namespace].[EnumerationName].[EnumerationValue]$ corresponds to the enumeration value of an enumeration declared in the model.

The full documentation is available here: http://www.softfluent.com/documentation/Methods_TargetNameTransformation.html

Happy CFQLing,

The R&D team

  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