Archive

Posts Tagged ‘CFQL’

Table-Valued Parameters: Reuse model collection types

July 31, 2015 Leave a comment

This is the last post of the Table-Valued Parameters (TVP) series. In the previous post we saw we can use TVP with CodeFluent Entities. We started with a single column TVP, then we create a more complex table type. The last thing is that CodeFluent Entities can generate a TVP per entity. Let’s see what does this means:

Database:

CREATE TYPE [dbo].[CustomerType] AS TABLE (
 [Customer_Id] [int] NOT NULL,
 [Customer_FirstName] [nvarchar] (256) NULL,
 [Customer_LastName] [nvarchar] (256) NULL,
 [_rowVersion] [binary] (8) NULL)
GO

CREATE PROCEDURE [dbo].[Category_ProcessCategoriesWithCollection]
(
 @categories [dbo].[CategoryType] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_categories int; SELECT @_c_categories= COUNT(*) FROM @categories
SELECT * FROM Category
INNER JOIN @categories AS c
ON [Category].[Category_Name] LIKE c.Category_Name + '%'
RETURN
GO

Business Object Model:

public static void ProcessCategoriesWithCollection(
    Samples.TVP.CategoryCollection categories
)
{
    CodeFluent.Runtime.CodeFluentPersistence persistence =
      CodeFluentContext.Get(
        Samples.TVP.Constants.Samples_TVPStoreName
      ).Persistence;
    persistence.CreateStoredProcedureCommand(
      null,
      "Category",
      "ProcessCategoriesWithCollection");
    persistence.AddArrayParameterObject("@categories", categories);
    ...
}

So we have a strongly typed code generated the model. How do we generate this code?

First we have to set the table type name (name used to create the database type)


Then we indicate the BOM producer to use user defined type (UDT):


Yep, only two simple attributes to set and CodeFluent Entities generates lots of code for you J

Happy Querying,

The R&D Team.

Table-Valued Parameters: Multi-Columns

July 24, 2015 Leave a comment

In previous blog posts we’ve seen how to use Table-Valued Parameters (TVP) that contains one column. Today we’ll see how to use TVP that contains two columns or more.

First we create our model:

CodeFluent Entities cannot create multi-columns TVP, so we simply use “object[]” to declare the parameter and we will create the table type by hand in an SQL file. For much convenience we’ll include the hand-written script in the generation process, so each time you generate the model, the custom script is executed J Persistence producers provide a way to execute a hand-made script before or after a standard generated script. Generated scripts are ran in a specific order, and you can plug-in your own hand-made ones by using the script name prefixed with the desired adjective (before or after). You’ll find a list of possibilities on the documentation page.

Let’s create a new SQL file named “before_<Project default namespace>_procedures.sql”

Then copy these lines into the before script file:

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[Customer_TVPInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Customer_TVPInsert]
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'CustomerTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[CustomerTableType]
GO

CREATE TYPE dbo.CustomerTableType AS TABLE
(
	FirstName	nvarchar(256),
	LastName	nvarchar(256)
)

We can now map the method parameter to this Table Type:

Note: If the parameter is not in the list, you can create it by using the Add button.

That’s it, we can use the generated code:

Customer.TVPInsert(new Tuple<string, string>[]
{
    Tuple.Create("John", "Doe"),
    Tuple.Create("Jane", "Doe")
});

Or maybe you prefer a typed version:

public class CustomerTvp
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public CustomerTvp(string firstName, string lastName)
    {
        FirstName = firstName;
        LastName = lastName;
    }
}
Customer.TVPInsert(new CustomerTvp[]
{
    new CustomerTvp("John", "Doe"),
    new CustomerTvp("Jane", "Doe")
});

The TVPInsert method has a parameter of type “object[]”. You can change it to a typed one as we’ve seen in a previous blog post.

 

And voilà !

Happy Querying,

The R&D Team.

Categories: .NET Tags: , , , ,

Table-Valued Parameters: Use IEnumerable instead of an array

July 17, 2015 Leave a comment

We’ve seen that CodeFluent Entities supports Table-Valued Parameters. The generated .NET method has a parameter of type array:

public static CustomerCollection LoadByIds(System.Guid[] ids)

What you may not know is that you can IEnumerable, IEnumerable<T>, DataTable or IDataReader instead of the array!

Select your parameter and set Type name to the desired type name:

After generating the code, the signature of the method is

public static SampleAdvancedTvp.CustomerCollection LoadByIds(
    System.Collections.IEnumerable ids
)

Happy Querying,
THe R&D Team.

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: 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:

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

Breaking Change: Enabling non-Latin character support


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.

 

Cheers,

Carl Anderson

Follow

Get every new post delivered to your Inbox.

Join 56 other followers