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: