“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

Using SQL Server datetime2 data type

July 28, 2014 Leave a comment

CodeFluent Entities supports DateTime2 since the build 714. A datetime2 defines a date that is combined with a time of day that is based on 24-hour clock. Datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

To use DateTime2 instead of DateTime you have to configure the SQL producer:

SQL Server Use datetime2

The created table uses datetime2:

CREATE TABLE [dbo].[Customer] (
 [Customer_Id] [uniqueidentifier] NOT NULL,
 [Customer_DateOfBirth] [datetime2] NULL,
)

Note: To use datetime2 in your application you have to set useDateTime2=”true” in the configuration file:

<configuration>
  <configSections>
    <section name="MyDefaultNamespace" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime" />
  </configSections>
  <MyDefaultNamespace connectionString="..." useDateTime2=”true” />
</configuration>

DateTime2 allows to specify the precision from 0 to 7 digits. The default precision is 7. This value is configurable at property level by setting the SQL Server specify attribute “sql size”:

SQL Server Sql Size
Now the generated script looks like:

CREATE TABLE [dbo].[Customer] (
 [Customer_Id] [uniqueidentifier] NOT NULL,
 [Customer_DateOfBirth] [datetime2] (6) NULL,
)

Note that Microsoft recommends using datetime2 data type for new work:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffsetprovides time zone support for globally deployed applications.

Happy coding,

The R&D team

SQL Server specific data types

July 24, 2014 Leave a comment

CodeFluent Entities can use SQL Server specific data types such as Geography, Geometry and HierarchyId.

The first step is to register “Microsoft.SqlServer.Types.dll” into the model:

 

Add Reference

 

SQL Server Reference

Note: You must add the same reference in the BOM project.

Then set the type name of the property to Microsoft.SqlServer.Types.SqlGeography:

Type Name Geography

Choose Type SqlGeography

We also have to set the database type. As this is specific to SQL Server, we have to use the SQL Server producer attribute “sqlType”:

Sql Data Type

The same apply for Geometry and HierarchyId data types.

The table is generated, let’s add a simple method that compute intersection of two geography object. The code is really specific to SQL Server so we have to create a RAW method:

CFQL SqlGeography

Don’t forget to set the return type name of the method to SqlGeography:

Return Type Name SqlGeography

Let’s use the generated code:

Sample sample1 = new Sample();
sample1.Geography = SqlGeography.Parse("LINESTRING(-122.360 47.656, -122.343 47.656)");
sample1.Save();

Sample sample2 = new Sample();
sample2.Geography = SqlGeography.Parse("LINESTRING(-122.360 47.656, -122.343 47.656)");
sample2.Save();

var intersection = Sample.GetIntersection(sample1.Id, sample2.Id);
Console.WriteLine(intersection.ToString()); //LINESTRING (-122.34300000005148 47.656000000089243, -122.3599999999485 47.655999999910769)

If intersection is null, this means that you need to add an assembly binding in the app.config/web.config file:

<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Types" culture="neutral" publicKeyToken="89845dcd8080cc91"/>        
        <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

This example shows how to use SqlGeography with CodeFluent Entities. The same works with SqlGeometry, HierarchyId, and any types that implement IBinarySerialize.

Happy storing,

The R&D Team

Persistent List

July 16, 2014 Leave a comment

CodeFluent Entities has a very powerful type system. But do you know you can persist a list of string, a list of integer or another kind of list (double, boolean, etc.)?

CodeFluent Entities maps all known types to their equivalent when switching from one layer to another. On the other hand, for any other “unknown” type, CodeFluent Entities relies on several kinds of serialization: binary serialization, XML serialization, and Lightweight serialization.

So if you use a List<string> the data will be persisted by serializing it as XML or Binary depending on your configuration. If you prefer to store the list as comma separated values you can use the PersitentList from the CodeFluent Runtime.

Persistent List Choose Type Name

 

The code is very easy to use:

Customer customer = new Customer();
customer.Name = "John Doe";
customer.Contacts = 
           new CodeFluent.Runtime.Utilities.PersistentList<string>('|');
customer.Contacts.Add("Jane");
customer.Contacts.Add("Bob");
customer.Contacts.Add("Ashley");
customer.Save();

In the database the row is stored as text:

Persistent List Database

But where is the magic?

This PersistentList implements the ICodeFluentSerializable interface:

/// <summary>
/// Allows an object to control its own serialization and deserialization in CodeFluent persistence layer context.
/// </summary>
public interface ICodeFluentSerializable
{
    /// <summary>
    /// Serializes this instance.
    /// </summary>
    /// <param name="mode">The serialization mode.</param>
    /// <returns>The serialized instance. May be null.</returns>
    object Serialize(PersistenceSerializationMode mode);

    /// <summary>
    /// Deserializes the specified object instance.
    /// </summary>
    /// <param name="type">The serialized object instance type. May not be null.</param>
    /// <param name="mode">The serialization mode.</param>
    /// <param name="serializedInstance">The serialized object instance. May be null.</param>
    /// <returns>The deserialized instance. May be null.</returns>
    object Deserialize(Type type, PersistenceSerializationMode mode, object serializedInstance);
}

So there is no magic. You can implement this interface for your custom object and persist them in the database in a custom manner. I remind you that if your custom class does not implement this interface, the XML serializer or Binary serializer will be used by default.

Happy storing,

The R&D team.

SQL Server In-Memory OLTP

July 10, 2014 Leave a comment

In-Memory OLTP comes with Microsoft SQL Server 2014 and can significantly improve OLTP database application performance. It is a memory-optimized database engine integrated into the standard SQL Server engine. This system provides memory-optimized tables which are fully transactional and are accessed using class Translact-SQL instructions.

In-Memory Tables comes with some limitations. We won’t enumerate all but only those which are related to CodeFluent Entities:

  1. Foreign keys aren’t supported
  2. RowVersion and Timestamp columns aren’t supported: http://msdn.microsoft.com/en-us/library/dn133179.aspx
  3. Default constraints aren’t supported
  4. Some Transact-SQL constructs aren’t supported: http://msdn.microsoft.com/en-us/library/dn246937.aspx

Let’s handle those four points!

Foreign Keys

There are two options:

  • Don’t create relation :(
  • Create relation without foreign key :)

The second solution requires the usage of an Aspect. Fortunately we already wrote it a few time ago: http://www.softfluent.com/forums/codefluent-entities/how-to-disable-creating-foreign-key-by-sql-producer-

Even if foreign keys do not exist anymore, CodeFluent Entities still generates LoadBy_Relation methods so you won’t see any difference in your code. :)

Foreign Keys

RowVersion

RowVersion is not supported by In Memory tables so let’s remove it. We have to set “Concurrency Mode” to “None”:

RowVersion

 

Default Constraints

Default constraints used by tracking columns (creation time & last write time) are not supported. Here we have two options:

  • Remove default constraints :(
  • Move them into the Save stored procedure :)

The first option is available in the Property Grid at project or entity level by removing the tracking time columns:

Properties

The second option can be done with an Aspect as you can see in the full example (see below). The edited INSERT statement looks like:

    INSERT INTO [Customer] (
        [Customer].[Customer_Id],
        [Customer].[Customer_Name],
        [Customer].[Customer_ContactSource_Id],
        [Customer].[_trackCreationUser],
        [Customer].[_trackLastWriteUser],
        [Customer].[_trackLastWriteTime])
    VALUES (
        @Customer_Id,
        @Customer_Name,
        @Customer_ContactSource_Id,
        @_trackLastWriteUser,
        @_trackLastWriteUser,
        (GETDATE())) -- Default Value

Tansact SQL

By default the SQL Server Producer surround the procedure code with a transaction. This transaction isn’t supported when using In Memory Table. The following exception is thrown when calling the stored procedure:

Unhandled Exception: System.Data.SqlClient.SqlException: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

To remove it, we have to configure the SQL Server to not produce it:

SQL Server

 

Migrate the table

After those small changes, we can migrate the table to an In Memory table:

Migration

Migration Result

 

We can now use the In Memory table from the application:

Customer customer = new Customer();
customer.Name = "John Doe";
customer.Save();

All-in-One method

All the previous steps are automated by an aspect. All you have to do is include the aspect and set “enabled” on tables:

SqlServer In Memory Aspect

The full code sample including the aspect is available on our GitHub repository.

The R&D Team

Store Enums as Strings


CodeFluent Entities fully suports .NET enumerations since 2005. This supports includes:

  • Being able to create your own enumerations at design time,
  • Use already existing CLR enumerations.
  • Using our designer, you can create and use enumerations in your model (e.g. OrderStatus in the screenshot below):

Enumeration

Currently CodeFluent Entities allows to store the enumeration value as a numeric value. Sometimes you prefer storing it as text so we write an aspect to do that.

Without the Aspect:
Without the aspect

With the Aspect:
With the Aspect

After adding the Aspect, you’ll see new properties in the property grid:

Store Enumeration As Text Property

The “Store Enumeration As Text” property exists at:

  • Enumeration level
  • Property level

You can also specified the column size. By default the column size will be computed from the enumeration values when possible. For example if you have a flag enumeration with values “First”, “Second”, “Third” the longest value will be “First, Second, Third”, so the column size will be 21. Of course you can override the value by setting the property “Default Column Size” at enumeration level or “Column Size” at property level.

How to install the aspect

Create a C# project in the solution and copy the Aspect files: https://github.com/SoftFluent/CodeFluent-Entities/blob/master/Extensions/SoftFluent.StoreEnumAsText/SoftFluent.Samples.StoreEnumAsText.Aspects/StoreEnumerationAsTextAspect.cs

Add a reference in the model to the aspect project:

Solution Explorer

 

Then, select those two projects:

 

Add Reference

Finally, add the Aspect into your model:

Add Existing Aspect

The code of the aspect is available on GitHub repository. Please leave feedback on how you liked this Aspect and what we could improve. You can also find additional resources about Aspects here.

Happy Aspecting!

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

Follow

Get every new post delivered to your Inbox.

Join 49 other followers