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: http://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:
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)
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:
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:
Use the view in a CFQL method:
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
The R&D team
A few months ago we added support for non-Latin characters in models. Though nice at first, we noticed that it slowed down method inference noticeably as we had to load non-Latin character sets to parse CFQL when a majority of our users were using Latin characters indeed.
This is why starting with build 646 and upper, we disabled non-Latin character support in CFQL. Nonetheless, you’ll be glad to know that this is configurable, so if you do want to use Cyrillic in your CFQL queries you’ll have to enable it explicitly, and here’s how to do it!
Enabling support is done through an appSetting in the configuration file. Here’s a sample CodeFluent.Build.exe.config configuration file which you can place next to CodeFluent.Build.exe (e.g. “%ProgramFiles(x86)%\SoftFluent\CodeFluent\Modeler”):
<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <add key="CfqlAllowsNonLatinCharacters" value="true" /> </appSettings> </configuration>
Please note that you’ll also need to set that appSetting in the devenv.exe.config configuration file to be able to use non-Latin characters in CFQL queries at design time as well.
CodeFluent Query Language (a.k.a. CFQL) allows you to write platform independent data accessing queries which producers (producer = code generator) will translate into optimized stored procedures.
For instance you can define the following CFQL query in your model:
<cf:method name="LoadByName" body="load(Name) where Name startswith @Name"/>
The SQL Server Producer will generate the following stored procedure:
CREATE PROCEDURE [dbo].[Test_LoadByName] ( @Name [nvarchar] (256) ) AS SET NOCOUNT ON SELECT DISTINCT [Test].[Test_Id], [Test].[Test_Name] FROM [Test] WHERE ([Test].[Test_Name] LIKE (@Name + '%')) RETURN
And the Oracle Database Producer will generate the following equivalent:
PROCEDURE LoadByName(CF_CURSOR OUT "Sample"."CF_#Runtime".CF_CURSOR_TYPE, "#Name" NVARCHAR2) AS V_CF_CURSOR "Sample"."CF_#Runtime".CF_CURSOR_TYPE; BEGIN OPEN V_CF_CURSOR FOR SELECT "Test"."Test_Id", "Test"."Test_Name" FROM "Sample"."Test" WHERE ("Test"."Test_Name" LIKE ("#Name" || N'%')); CF_CURSOR := V_CF_CURSOR; END;
As you can see data accessing queries are not built in-memory dynamically at run time but instead a classic stored procedure is generated at development time.
As a consequence developers can:
- see what’s going to happen at run time and avoid bad surprises (like this one when using EF for instance),
- debug it easily if they need to,
- change the code if the need to.
CodeFluent Entities provides “Methods” which allows developers to define custom data accessing methods (load, loadone, delete, count, search) which persistence producers will translate into stored procedures.
For instance, the following method:
Will generate the following stored procedure:
CREATE PROCEDURE [dbo].[Setting_LoadByName]
@Name [nvarchar] (256)
SET NOCOUNT ON
SELECT DISTINCT [Setting].[Setting_Id], [Setting].[Setting_Name], [Setting].[Setting_Value] FROM [Setting]
WHERE ([Setting].[Setting_Name] = @Name)
You can also create what we call Raw methods which are platform specific code such as T-SQL or PL-SQL, but you can also mix both, that is to say create a CFQL method so upper layers benefit from the out-of-the-box features provided by such methods and just override the actual body of the stored procedure.
For instance say you wanted to create a topped method which the number of returned rows can be passed to the method. The “TOP” statement doesn’t exist in CFQL (see TOP in CFQL for more information) so a way to do this would be to mix CFQL and T-SQL for instance.
To do so, start by defining your CFQL body:
And then define the Raw Text property of the body (select your method, and click on the “Bodies” property in the property grid):
Once this is done, if you build your model, the stored procedure quoted hereunder will be generated, and in upper layers your method will look just as expected (returning a SettingCollection, mapping returned columns to properties, and taking an int maxCount parameter):
CREATE PROCEDURE [dbo].[Setting_BoundedLoadAll]
SET NOCOUNT ON
SELECT TOP(@maxCount) * from Setting
CFQL actually provides built-in support for Many-To-Many relations, which often seems unnatural to developers. As a consequence, a common mistake is to write raw SQL to access a value contained in a Many-To-Many relation property, when in fact it can be done without any extra work.
For instance, in the following example a User can have several Roles, and one Role can be assigned to several Users. Nevertheless, if you want to load all users with a role name containing a specific token, instead of writing a raw SQL query, you could do it in a single line CFQL query, such as:
<Email typeName="Email" collectionKey="true"/>
<cf:method name="LoadByRoleToken" body="load(string token) where Roles.Name contains @token" />
<Name collectionKey="true" />
<Users typeName="UserCollection" />
As you can see in the example above, you can access entity properties using dots even on collection properties :)
CFQL is made to use entities directly, you don’t have to use their keys explicitly (even though you can). The key point in CFQL is to be decoupled from the database’s physical definition, so keep it as simple as possible using entities for maximum flexibility. Here’s an example illustrating this point. One could be tempted to write such a CFQL query:
<cf:method name="DeleteByRoleId" body="delete(int RoleId) where Role.Id=@RoleId " />
Of course, this method is valid, however writing so implies that the identifier of the Role entity is of the integer type, so if you change its type, or change its key into a composite key, you’ll have to write this method over. However, using entities directly instead of their keys solves this issue:
<cf:method name="DeleteByRole" body="delete(Role) where Role=@Role" />
Both CFQL queries will actually produce the same SQL statement, however the second version is way more flexible.
Microsoft, starting with SQL Server 2008, added support for a new parameter type in SQL Server: Table-Valued parameters. Using them, you can send multiple rows of data to a T-SQL stored procedure, without creating a temporary table for instance.
Starting with the build 50204.579, the SQL Server producer now provides built-in support for those types. Thanks to this new feature you can now design CFQL queries such as:
<cf:method name="LoadCategories" body="load(int ints) where Id in (@ints)"/>
Which will generate the following code:
CREATE TYPE [dbo].[cf_type_Category_LoadCategories_0] AS TABLE(
[Item] [INT] NULL)
CREATE PROCEDURE [dbo].[Category_LoadCategories]
@ints [dbo].[cf_type_Category_LoadCategories_0] READONLY,
@_orderBy0 [nvarchar] (64) = NULL,
@_orderByDirection0 [BIT] = 0
SET NOCOUNT ON
DECLARE @_c_ints INT; SELECT @_c_ints= COUNT(*) FROM @ints
SELECT DISTINCT [Category].[Category_Id], [Category].[Category_Name], [Category].[_rowVersion]
WHERE [Category].[Category_Id] IN ((SELECT * FROM @ints))
The feature is actually broader than that, you can also:
- In CFQL, use the ‘=’ operator on table-valued types, e.g. count(int ints) where @ints = Id,
- Use .NET CLR types, e.g. raw(System.Uri uris),
- Use already existing table-valued types in database,
- Map table-valued types to .NET types such as DataTable, IDataReader and IEnumerable<SqlDataRecord>,
- Map them to generated collection types you created in the model.
Using CFQL (CodeFluent Query Language) you can define platform independent persistent methods in your model which will then be translated into actual stored procedures by persistence producers such as SQL Server producer and the Oracle Database producer.
Moreover, upper layers producers will also generate the corresponding code mapped onto the generated stored procedure.
- In the following model:
<Employee> <Id /> <Name /> <Address typeName="Address" /> </Employee> <Address> <Id /> <Line1 /> <Line2 /> <Zip /> <City /> <Country /> </Address>
- Adding the following CFQL method to the Employee entity:
<cf:method name="LoadByCity" body="load(string cityName) where Address.City Like @cityName" />
- Will generate the following T-SQL stored procedure using the SQL Server producer:
CREATE PROCEDURE [dbo].[Employee_LoadByCity] (@cityName [nvarchar] (256), ...) AS SELECT DISTINCT [Employee].[Employee_Id], [Employee].[Employee_Name], [Employee].[Employee_Age], (...) FROM [Employee] LEFT OUTER JOIN [Address] ON ([Employee].[Employee_Address_Id] = [Address].[Address_Id]) WHERE ([Address].[Address_City] LIKE @cityName)
Here’s how to do it in your application using the Modeler: