Archive

Posts Tagged ‘CodeFluent Entities’

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.

Team Work with CodeFluent Entities


Working on your own and working along a whole developers team are obviously not the same. As a team, you will experience issues that you would never encounter working alone.

In order to help you work in a harmless way with CodeFluent Entities in that context, we wrote a new paper about every little things you can configure while you are:

  • Designing your model collaboratively
  • Generating your own database
  • Handling work station custom connection strings
  • Using source control

You can get this paper here.

Happy Configurating,

The R&D Team.

CodeFluent Entities and Fetch Performance Benchmark


Two month ago, we heard about a benchmark that fetch performance of .NET Data Access Tools and ORMs. This benchmark has been introduced by Frans Bouma in this following blog post.

We started by creating a simple implementation with CodeFluent Entities and then analyze the result. Thanks to the flexibility of our product, it seems natural for us to provide an optimized but generic implementation. This means that the code is still generated.

CodeFluent Entities provides dynamics modeling features like “Aspects” to implement application-wide behaviors. This is what we call dynamic modeling. In a nutshell, in CodeFluent Entities, dynamic modeling is materialized as aspects and it allows developers to inject extra-behaviors in models.

That’s what gave us the idea of developing a new Aspect called “FasterReader” to reduce the number of data type conversion.
We explained our approach in a blog Post named “Fetch performance of CodeFluent Entities compared to others“.
This Aspect is generic (not “benchmark dedicated”) and can be used in any kind of model.

Once the article is published, we sent a Pull Request on the RawDataAccessBencher GitHub repository to submit the CodeFluent Entities implementation:

pullrequest

The initial commit is available here and includes the CodeFluent Entities Model with the SoftFluent.FasterReader.xml aspect.

We also modified the README.MD file with the following changes:

CodeFluent Entities is a Model-First code generator. CodeFluent Entities will access database using automatically generated stored procedures. For more information about CodeFluent Entities and this benchmark: http://blog.codefluententities.com/2014/03/27/fetch-performance-of-codefluent-entities/.

Of course, this readme includes a link to our blog post that explains the approach.

Then, Frans Bouma asked us to make some understandable changes before merging the code:

  • remove links to CodeFluent Entities website
  • remove a large file with all the procs
  • remove useless files in various folders regarding TFS, scc, resx, .user, etc.
  • remove the CodeFluent Entities Model

For your information about the last statement: we have chosen to add this project to let users generate again the code and to be completely transparent.

Of course, feedbacks has been considered a few days later:

pull_request_changes

At the end of April, the CodeFluent Entities implementation was finally merged:
merge

But… the 5th of May, Frans Bouma removed our code:

CFE_Removal

With the following reason:

The CodeFluent code was removed as it kept the connection open during
individual fetches which therefore turned out to be faster than everyone
else. This was hard to find, and therefore a form of cheating.

By default, CodeFluent Entities opens one connection per thread but we attempted to explain that we can change this behavior. His reaction was the following:

Wont_Merge

You will find below the result with the custom configuration:

ADO.NET’s Connection Pooling keeps all connections open so that doesn’t change a lot of things so we don’t change this setting:

Results

We understand the way out products connects to a database out-of-the-box is not ok for Mr Bouma, but we don’t really understand why he went so mad about this, treating us cheaters. Again, this is how it works out-the-box. Why should we investigate why our product – based on stored procedures – is faster than others? Mr Bouma could have discussed with us – like he did in the beginning of the whole process – and we could have changed this default behavior happily to comply with his rules. We were not trying to make a fool of him or to hide crucial information from him, just compete with other products. It’s a sad way of doing thing, and in the end, this behavior does not change anything to the numbers.

Moreover, if you want to check or test our implementation (the Model is included), please visit the following GitHub repository.

The R&D Team.

ASP.NET Identity v2 and CodeFluent Entities

April 30, 2014 Leave a comment

Update (2014/06/23) – please check out this related post :

A few weeks ago, we wrote about ASP.NET Identity v1 and CodeFluent Entities. The ASP.NET Identity system is designed to replace the previous ASP.NET Membership and Simple Membership systems. It includes profile support, OAuth integration, works with OWIN and is included with the ASP.NET templates shipped with Visual Studio 2013.

ASP.NET Identity 2.0 was released last month so today we’ll see how to use it with CodeFluent Entities.

What’s new?

One of the drawbacks of the first release was that the primary key must be of type “string”. This new version allows to use any type as primary key. To do so, interfaces now have a second generic parameter, “TKey”, to specify the type of the primary key (int, Guid, etc.).
They also introduce new interfaces:

  • IUserLockoutStore<User, TKey>: allow to lock-out an account after a determined failed connection attempts
  • IUserEmailStore<User, TKey>: allow to store an email address and to confirm it (by sending an email for example)
  • IUserPhoneNumberStore<User, TKey>: allow to store a phone number and to confirm it (by sending a SMS for example)
  • IUserTwoFactorStore<User, TKey>: enable two-factor authentification (for example login/password and a token sent by email or SMS)

The implementation

 

Here’s the CodeFluent Entities Model to support those interfaces:

model

You’ll note that our model contains:

  • Implementation rules. We don’t even have to add partial class, everything is in the model
  • validation rules (EmailValidation) to ensure email address is valid
  • Unique contraints (UserName, Email, Role Name)

As for ASP.NET Identity v1, each method is only one or two lines of code:

public class UserStore :
    IUserStore<User, Guid>,
    IUserPasswordStore<User, Guid>,
    IUserSecurityStampStore<User, Guid>,
    IUserRoleStore<User, Guid>,
    IUserLoginStore<User, Guid>,
    IUserClaimStore<User, Guid>
{
    public Task<User> FindAsync(UserLoginInfo login)
    {
        return Task.FromResult(User.LoadByProviderKey(login.ProviderKey));
    }

    public Task ResetAccessFailedCountAsync(User user)
    {
        user.AccessFailedCount = 0;
        return Task.FromResult(0);
    }

    public Task<int> GetAccessFailedCountAsync(User user)
    {
        return Task.FromResult(user.AccessFailedCount);
    }

    // and so on
}

The full implementation is available on our GitHub repository.

The R&D team.

Follow

Get every new post delivered to your Inbox.

Join 56 other followers