Search Results

Keyword: ‘cfql’

Table-Valued Parameters: CFQL operators

July 10, 2015 Leave a comment

CodeFluent Query Language (CFQL) allows to quickly create simple methods. CFQL provides support for common operations with Table Value Parameters.

To show result of queries we will use the following data:

IN

 

Using the IN operator you can exclude values that aren’t included in a list. For instance:

load(guid[] ids) WHERE Id IN (@ids)

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByIdsIn]
(
 @ids [dbo].[cf_type_Customer_LoadByIdsIn_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer]
WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids)))

RETURN
GO

 

 // John and Jane
CustomerCollection.LoadByIds(new int[] { 1, 2 });
 // Empty result set
CustomerCollection.LoadByIds(new int[0]);

Equals (=)

 

Using the equals (=) operator you can exclude values that aren’t included in a list, but unlike the IN operator, when the list is empty no filter is applied. For instance:

load(guid[] ids) WHERE Id = @ids

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByIdsEquals]
(
 @ids [dbo].[cf_type_Customer_LoadByIdsEquals_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer] LEFT OUTER JOIN @ids AS _t_ids ON ((@_c_ids = 0)
OR (([Customer].[Customer_Id] = _t_ids.Item)))
WHERE ((@_c_ids = 0) OR (([Customer].[Customer_Id] = _t_ids.Item)))

RETURN
GO

 

 // John and Jane
CustomerCollection.LoadByIds(new int[] { 1, 2 });
 // John, Jane and Jimmy
CustomerCollection.LoadByIds(new int[0]);

Comparison operators: Like, StartsWith, greater than, freetext, etc.

 

You can use comparison operators between a single value and a TVP:

load(string[] names) WHERE Name STARTSWITH @names

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByNamesStartsWith]
(
 @names [dbo].[cf_type_Customer_LoadByNamesStartsWith_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_names int; SELECT @_c_names= COUNT(*) FROM @names
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer] LEFT OUTER JOIN @names AS _t_names ON ((@_c_names = 0)
OR (([Customer].[Customer_Name] LIKE (_t_names.Item + '%'))))
WHERE ((@_c_names = 0)
OR (([Customer].[Customer_Name] LIKE (_t_names.Item + '%'))))

RETURN
GO

 

 // Jane, John
CustomerCollection.LoadByNamesStartsWith(new string[] { "Ja", "Jo" });
 // Jane, John and Jimmy
CustomerCollection.LoadByNamesStartsWith(new string[0]);


Custom

 

You can use RAW methods with Table Value Parameter. Here’s an example with inline SQL:

LOAD(string[] names)
WHERE [EXISTS (SELECT * FROM @names AS n WHERE $Name$ = n.Item)]

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadCustom]
(
 @names [dbo].[cf_type_Customer_LoadCustom_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_names int; SELECT @_c_names= COUNT(*) FROM @names
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer]
WHERE EXISTS (
SELECT * FROM @names AS n WHERE [Customer].[Customer_Name] = n.Item
)

RETURN
GO

 

 // Jane, John
CustomerCollection.LoadCustom(new string[] { "Jane", "John" });
 // Empty result set
CustomerCollection.LoadCustom(new string[0]);

Happy Querying,

The R&D Team.

“IS NOT NULL” and “IS NULL” in CFQL

July 31, 2014 Leave a comment

In CodeFluent Query Language (CFQL), the SQL statement “expression IS NOT NULL” is “expression Exists” and the SQL statement “expression IS NULL” is “NOT expression EXISTS”.

There is no need to write raw SQL to test if a value exists in a method as we sometimes see in models. This can be done in full CFQL thanks to the Exists operator, this way keeping your model as platform independent as possible:

LOAD() WHERE (NOT FirstName EXISTS) AND (LastName EXISTS)

More about CFQL:

Happy CFQL-ing,

The R&D team

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

Hands-on CFQL

July 25, 2013 Leave a comment

If you are familiar with CodeFluent Entities it is possible that you have already used the CFQL. The acronym CFQL stands for CodeFluent Query Language. In a nutshell, the CodeFluent Query Language is a specific language that specifically eases stored procedures creation, custom methods and rule definitions.

As you know, according to the database that you use you will have to write either PL-SQL or T-SQL depending if you are using a SQL Server or an Oracle database. CodeFluent Query Language is platform independent and is therefore used to declare platform independent queries which will then be translated by producers in platform specific code.

Therefore, we are going to take a look at it, to see how CFQL works.

To illustrate this article, we will use the “SampleAdvertising” model provided by CodeFluent Entities. We will also create a new “ClassLibrary” project which will contain the business code generated by CodeFluent Entities. We will also add a new “Console Application” project to our solution to test the custom methods we are about to create.

1

First we will add our producers, in this case we will need two of these, the Business Object Model producers which allows us to generate our business code, and the SQL producer which will generate our database, tables, stored procedures, views…

For the Business Object Model producer, we will set its targeted project to our class library project.

2

Regarding the SQL Server producer, we will create a new folder named “SQL Scripts” in our class library project which will contain all the files generated by this producer.

3

Now that we have added our producers, we can start creating our custom methods using the CodeFluent Query Language. We will start by creating all our methods, then we will use some of them in our console application.

For instance, we will create custom methods for our “Customer” entity. To do so, in the modeler, click on the “Customer” entity and hit “Add method” from the ribbon.

4

First, we will create a custom “Load” method, “LoadByCountry”, which will enable us to load our customers depending on their country. Therefore, add the following code to create our custom method:

5

As you can see, the syntax of the CodeFluent Query Language is really close from the SQL language.

We will now create a “LoadOne” method named “LoadOneByName” which will return a single line of data, in this case a customer based on its name. Since the generated method manipulates a single line of data, in the generated Business Object Model, the method is generated in the entity class and not the collection class as for classic load methods.

6

Then we will create a “Load Raw” method named “LoadTodaysCustomers”. Raw methods are methods in which a developer can code whatever he wants: platform specific code (e.g. CSharp, SQL, etc.), CodeFluent Query Language (CFQL), or a mix of both. In this example, we will create a method which will return today new customers. As you can see in the screenshot below, creating a raw method allows to target specific platform and language, in our case it will be T-SQL.

7

Now, we will create a “LoadOne Raw” method named “LoadLastFiveMinCreatedCustomer” which will return a single row of a customer created within the last five minutes.

8

Once this method created, we will create a custom search method to find our customers based on their city. We will name it “SearchByCity”.

9

Then we will create a new method to count our customers based on their city according to the screenshot below, name it “CountByCity”.

10

Finally, we will create a custom method named “DeleteByCountry” to delete our customers based on their country.

11

Now that all the methods we wanted have been created, let’s build our solution to generate our database and our business code (right click on our CodeFluent Entity project > build).

Once the build is complete and successful, open the file “Program.cs” of our console application project and add the following code to get all our customers from the database.

foreach (Customer cust in CustomerCollection.LoadAll())
{
    Console.WriteLine(cust.Name + " " + cust.Country + " " + cust.City);
}

Then, we will start by testing our “LoadByCountry” custom method

CustomerCollection custByCountry = null;
Console.Write("LoadByCountry, enter a country: ");
string countryInput = Console.ReadLine();
if (countryInput != null && countryInput.Trim() != String.Empty)
{
    custByCountry = CustomerCollection.LoadByCountry(countryInput);
}
if (custByCountry != null)
{
    foreach (Customer cust in custByCountry)
    {
        Console.WriteLine(cust.Name + " " + cust.Country + " " + cust.City);
    }
}

As you can see on the screenshot below, our custom method returns our customers based on their country.

12

Now, we will test our “LoadOneByName” custom method. Insert the following code into the “Program.cs” file.

Console.WriteLine("\r\n");
Console.Write("LoadOneByName, enter a name: ");
string nameInput = Console.ReadLine();
Customer singleCustByName = null;
if (nameInput != null && nameInput.Trim() != String.Empty)
{
    singleCustByName = Customer.LoadOneByName(nameInput);
}
if (singleCustByName != null)
{
    Console.WriteLine(singleCustByName.Name + " " + singleCustByName.Country + " " + singleCustByName.City);
}

You can see the result in the screenshot below.

13

We can take a look to our raw method “LoadTodaysCustomers”. In order to test this method add the following code to the “Program.cs” file.

CustomerCollection custFromToday = null;
Console.WriteLine("\r\n");
Console.WriteLine("LoadTodaysCustomers:");
custFromToday = CustomerCollection.LoadTodaysCustomers();
if (custFromToday != null)
{
    foreach (var cust in custFromToday)
    {
        Console.WriteLine(cust.Name + " " + cust.Country + " " + cust.City);
    }
}

As you can see in the following screenshot, our method returns all our customers since they were all generated today.

14

Then, we can test our “LoadLastFiveMinCreatedCustomer” method, to do so, insert the following code into the “Program.cs” file.

Console.WriteLine("\r\n");
Console.WriteLine("LoadLastFiveMinCreatedCustomer:");
Customer lastModifiedCust = Customer.LoadLastFiveMinCreatedCustomer();
if (lastModifiedCust != null)
{
    Console.WriteLine(lastModifiedCust.Name + " " + lastModifiedCust.Country + " " + lastModifiedCust.City);
}

We can verify the result thanks to the two following screenshots.

15

16

We will now test our “SearchByCity” method, to do so, add the following code to the “Program.cs” file.

Console.Write("SearchByCity, enter a city: ");
string cityInput = Console.ReadLine();
CustomerCollection searchByCity = CustomerCollection.SearchByCity(cityInput);
foreach (Customer cust in searchByCity)
{
    Console.WriteLine(cust.Name + " " + cust.Country + " " + cust.City);
}

And this is what you should get.

17

Then we will test our “CountByCity” method which allow us to count our customers based on their city.

Console.Write("CountByCity, enter a city: ");
string cityCountInput = Console.ReadLine();
Console.WriteLine("Number of customer in " + cityCountInput + ": " + CustomerCollection.CountByCity(cityCountInput));

You can see the result in the following screenshot.

18

Finally, we can test our “DeleteByCountry” custom method and here is the code to test it.

Console.Write("DeleteByCountry, enter a country: ");
string deleteCountryInput = Console.ReadLine();
Console.WriteLine("You are about to delete all customers from " + deleteCountryInput + ". Are you sure? (y/n)");
string deleteConfirm = Console.ReadLine();
if(deleteConfirm.ToLower() == "y")
{
    CustomerCollection.DeleteByCountry(deleteCountryInput);
}

You can call again the “CustomerCollection.LoadAll” method to verify that customers related to the country you have specified in the previous method.

19

Cheers,

Aymeric ROLAND.

CFQL Expressions

September 13, 2011 Leave a comment

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

Sorting in CFQL

September 8, 2011 1 comment

CFQL allows you to sort the loaded records by specifying the “ORDER BY” statement in your queries.

The order by syntax is pretty much the SQL syntax:

order by <identifier1> [asc | desc], <identifier2> [asc | desc], … , <identifierN> [asc | desc]

Note that identifiers can be expressions navigating your model such all:

load order by Customer.Reference

The expression orders all records of the declaring entity by the Reference of its related Customer (Customer is a relation property of the declaring entity).

Other than that, ASC and DESC keywords work exactly as in SQL.

Since the sort order is defined in a CFQL query, the generated stored procedure will always sort at run time using the specified column and direction (by the way if no direction is specified, ascending is used by default).

Arguments in CFQL

August 19, 2011 Leave a comment

CFQL methods can take arguments. Arguments in CFQL are composed of an optional type name and a name.

If the type name isn’t specified CodeFluent Entities looks for a property in the current entity matching the argument name.

For instance, instead of writing:

<Product>
  <Id />
  <Code />
  <Price />
 
  <cf:method name="LoadByCode" body="load(string code) where Code = @code" />
</Product>

You can write:

<cf:method name="LoadByCode" body="load(Code) where Code = @Code" />

Note: CFQL is case insensitive, writing “Code” or “code” or “LOAD” or “load” doesn’t matter.

This actually allows you to write flexible models: since if you change the property type, you won’t have to update your methods.

Keeping the same concern in mind, you can write expressions using entity names instead of their keys, such as:

<cf:method name=”LoadByRole” body=”load(Role Role) where Role = @Role” />

This should be preferred to writing methods such as this one:

<cf:method name=”LoadByRole” body=”load(guid roleId) where Role.Id = @roleId” />

The result will indeed be the same, however, if you change the type of the Role identifier, or if you update the Role entity to have a composite key your code will break. To the contrary, using the first method using the “Role = @Role” statement will keep on working.

Many-To-Many Relations in CFQL


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:

<User>
<Id/>
<Email typeName="Email" collectionKey="true"/>
<Password />
<Roles typeName="RoleCollection"/>
<cf:method name="LoadByRoleToken" body="load(string token) where Roles.Name contains @token" />
</User>

<Role>
<Id />
<Name collectionKey="true" />
<Users typeName="UserCollection" />
</Role>

As you can see in the example above, you can access entity properties using dots even on collection properties 🙂

Use Entities Rather Than Their Keys in CFQL


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.

“TOP” in CFQL

May 10, 2011 1 comment

A “TOP” SQL statement can be added to CFQL methods by setting the maxCount attribute on the method. Therefore, there’s no need to write raw SQL to do so, and the following LoadTopTen method:

<cf:entity name=”Artist”>
(…)
<cf:method name=”LoadTopTen” body=”load() raw”>SELECT TOP(10) * FROM Artist</cfmethod>
</cf:entity>

Can instead be written:

<cf:entity name=”Artist”>
(…)
<cf:method name=”LoadTopTen” body=”load” maxCount=”10″ />
</cf:entity>

This way you’re entirely platform independent and you can let producers translate those for you.