Archive
Defining type mappers when importing a database using the Importer Wizard
You can use the Importer Wizard to import an existing data base as a CodeFluent Entities model. You can access the Importer from the Modeler or the solution explorer on your CodeFluent Entities project (right click).
The Importer Wizard
The importer makes a mapping between the data source types and the CodeFluent Entities types. But, you may want to define your own mapping configuration for a given type.
To do that, go to the “advanced properties” of the Importer Wizard and select “Type Mappers” under the “Input Mapping” group and add your type mapper.
Type Mappers Configuration
Add an Importer Type Mapper
Let’s say I am importing a SQL Server 2012 database and I have a field of type “time” (more information about SQL Server 2012 type mapping here), well I would like to have a CLR type “TimeSpan” mapped to it. When I take a look at the imported model I can see that the mapped type is “time”, but when I generate the Business Object Model (BOM) layer I have a property of type “System.DateTime (nullable)”. This is because the SQL Server type “time” did not exist before the SQL Server 2008 version.
SQL Server time type
time to DateTime mapping
So we can define a “type mapper” for the SQL Server type “time”.
elapsedtime mapper
We could also have chosen “System.TimeSpan”, “timespan” or “duration” as the “Type Name”.
And now we have a CLR type “System.TimeSpan (nullable)” in our BOM for a SQL Server 2012 type “time”.
time to TimeSpan mapping
Remember that you can save your Importer Wizard configuration.
You can find more resources about the Importer Wizard here.
Regards,
Pablo Fernandez Duran
Retrieve SQL Output parameters in your BOM with CodeFluent Entities
Let’s say you have an existing stored procedure that uses OUTPUT parameters to retrieve some data and you want to access it in your Business Object Model (BOM) as an object method.
If you can modify your procedure you might want to use a more OOP (Object Oriented Programming) approach. It will be more flexible and it is what CodeFluent Entities follows.
But let’s suppose you don’t have the possibility to modify your stored procedure.
We will use an existing model to illustrate this post. We will work with the ContactManager demo project available within the CodeFluent Entities project templates in Visual Studio.
This is how our existing stored procedure looks like, it has 2 OUTPUT parameters.
CREATE PROCEDURE [dbo].[legacy_procedure] ( @param1 [int] OUTPUT, @param2 [nvarchar] (256) OUTPUT ) AS BEGIN --some interesting code here select @param1 = 42 select @param2 = 'answer' END GO
We create a method called “LegacyProcedure” related to the entity Contact
Add a method to an entity
Name the method
We need to create a “raw” method and specify the parameters for our method.
As we want to use an existing stored procedure we leave the body empty.
Defining our method
As we will work with an existing stored procedure we need to tell our method to use our existing stored procedure, and not to create his own persistence stored procedure.
In the method advanced properties at the “persistence” level we set “Persistence Name” (it must be the same name as the stored procedure).
Set the persistence method name
Now we tell the persistence producer not to produce the stored procedure.
To do that we go to the “Aspect and Producer Properties” of our method and set the “Produce” value to False.
Persistence Produce Property
Finally we need to define the parameters that are used in our method.
The method parameters
The parameter names must be the same as those declared in our method (as well as the type).
Parameter names
Now we need to set the persistence parameter direction. Displaying the advanced properties, in the persistence group we set the direction to “Output”.
Parameter persistence direction
Supposing that you have a “Persistence producer” and a “Business Object Model producer” (BOM) we build the CodeFluent Entities project.
If you have a CodeFluent Entities version former to the Build 1.0.61214.707, then you will have a compile error while building the generated BOM project.
Output parameters are not handled
The solution would be to disable the generation of our method and code it by hand in a partial class.
But, since Build 1.0.61214.707 CodeFluent Entities now supports the “out” and “ref” parameters in persistence methods.
After installing the new version, we build the CodeFluent Entities project. If we take a look at our generated method we will see that the “out” parameters are now handled by our BOM.
Out parameters are handled
Regards,
Pablo Fernandez Duran
How to work with existing database tables with CodeFluent Entities
Did you know that you could use CodeFluent Entities to work with existing database tables, such as the sys.object table for example, with zero code, and without modifying them?
In this post, we will demonstrate how to display a list of all tables in your SQL database. To follow this demo, you need to be familiar with CodeFluent Entities, and understand concepts such as entities, CFQL and raw methods.
The first thing is to create a CodeFluent project (DemoRaw.Model), a class library project for our bom (DemoRaw), and an empty MVC3 web project (DemoRaw.Web) to display our list. Then, we need to add 3 producers:
- the BOM producer
- the SQL Server producer
- the MVC3 producer
If you need help with creating a CodeFluent Entities project, you may need to look at other articles on this blog before continuing.
Next, we create an entity called SysObject, and add three properties:
- ObjectId (int)
- Name (string)
- Schema (string)
In our entity, let us add a CFQL method called LoadMyObjects. We use load raw in our stored procedure to return a collection of SysObject. Our stored procedure retrieves three properties from our sys.object table. You need to make sure that the fields name match the ones of our entity in the CodeFluent model. That way, the mapping will be handled automatically. The SQL stored procedure is like this:
select name as [SysObject_Name], object_id as [SysObject_ObjectId], schema_name(schema_id) as [SysObject_Schema] from sys.objects
Now, we need to update three settings before generating our model:
1. Because we do not need to create the SysObject table in SQL, we need to set the Produce Table property to false.
2. Our table does not need to have the tracking properties enabled, so we can remove them using the Tracking Modes property. Let us remove the User and Time from the list.
3. Last, we need to disable the concurrency mode. Let us set the Concurrency Mode to none.
Our model is ready to be built. Let us build it! Do not forget to add the DemoRaw reference to your DemoRaw.Web project before running it! Your default web browser opens, click on Sys Object, then Load My Objects, and you should see the list of tables!
This is how you can retrieve data from a specific SQL table, map them to a collection of objects, and display them on a webpage, all of this in a few minutes. If you want to go further, you can add more methods to manipulate the object. Happy CodeFluenting!
Vincent Patry
Goodbye SQL-DMO!
Starting with build 671, SQL-DMO is not longer a requirement for the CodeFluent Entities SQL Server Producer. That’s good news!
We have been relying on SQL-DMO for almost 7 years, and it has served us well. It has been marked as obsolete for something like 5 years or so, but the producer was still relying on it. One of the reasons we kept it for so long was to maintain compatibility with SQL Server 2000. Now, we don’t support that database either (june 2012).
We have replaced SQL-DMO by a similar technology without any dependency on the newer SQL-SMO (replacement for SQL-DMO) either. This database abstraction technology - our own SMO-like layer – works not only for SQL Server but also for all our supported importers and producers supported databases and modeling systems, namely: SQL Server, Oracle, PostgreSQL, MySQL, SqlLite, Xmi, Enterprise Architect, Access and SqlServerCe. It’s mainly located in the CodeFluent.Runtime.Database.dll assembly that ships with the product and in satellite ones for specific targeted systems (CodeFluent.Runtime.PostgreSQL.dll for PostgreSQL, CodeFluent.Runtime.MySQL.dll for MySQL, etc.).
Cheers,
The R&D team.
CodeFluent Entities: How to reuse existing stored procedures
In this post I’ll start from the NerdDinner application, which you can get from CodePlex (http://nerddinner.codeplex.com/). In the NerdDinner database I added the following sample stored procedure named “sp_SelectDinnersByCountry” that takes a county name as a parameter:
CREATE PROCEDURE [dbo].[sp_SelectDinnersByCountry]
(
@Country [nvarchar] (50)
)
AS
SET NOCOUNT ON
SELECT DISTINCT *
FROM Dinners
WHERE Country = @Country
RETURN
From there, I then imported the NerdDinner database into a brand new CodeFluent Entities model (as detailed on CodeFluent Entities’ Get Started page) and now what I want to do is to reuse my existing stored procedure, instead of creating a new one using CFQL.
Select the “Dinner” entity and add the following new method:
Now that our method is created we need to specify:
- it’s name in the persistence layer as my stored procedure has a different name than the one in my model,
- it’s return type (by default raw methods are void),
- mark my method so it’s not generated by the SQL Server Producer,
As a consequence the Business Object Model Producer will generate a SelectByCountry method, which calls the sp_SelectDinnersByCountry stored procedure, and returns a DataSet, so now in my app I can do something like this:
System.Data.DataSet ds = Dinner.SelectByCountry("FRANCE");
Hope this helps,
Carl
Many to Many Relationships with CodeFluent Entities
Yesterday, Scott Allen posted on his OdeToCode.com blog a very interesting blog post named Many to Many Relationships with EF illustrating how to setup a many to many relationship with EF Code First and then, and it’s the interesting part to us, how to use the SelectMany operator to “flatten” a sequence of book authors.
In fact, we thought it was so interesting that we decided to post a little something showing how to do it in a model-first way, using CodeFluent Entities. The result is the same, yet the way to do it is quite different.
Developing the Sample
Unlike EF Code First, CodeFluent Entities is model-first, therefore it all starts by creating a model.
Here’s our model (btw here’s a little preview of our soon-to-come vector-based & metro style theme):
Code generators (named producers in the product) will translate this platform independent model into actual code. So the SQL Server producer will translate the Book and Author entities into tables, their properties into columns, since a many to many relationship is declared between the Book and Author entities, an association table will also be created.
Another nice feature which you can see in the screenshot above is that you can add what we call “instances” to your entities which will be translated by actual lines of data. It’s a convenient way to initialize your generated database with data.
Now that we have our database and data, let’s write our method which will load all this data in a flat view.
Using CodeFluent Entities you can create actual SQL views and that’s precisely what we did:
Note: see the “Auto infer lightweight entitiy” checkbox on the upper right? Checking this will generate a dedicated .NET class mapped to your view 
See here for more info.
We created a view named “FlatView” which selects book ids, titles and author names. Here’s what it generates:
CREATE VIEW [dbo].[vBooksFlatView]
AS
SELECT [Books].[Id], [Authors].[Name], [Books].[Title]
FROM [Books]
LEFT OUTER JOIN [Authors_Books_Books_Authors] ON ([Books].[Id] = [Authors_Books_Books_Authors].[Id2])
LEFT OUTER JOIN [Authors] ON ([Authors_Books_Books_Authors].[Id] = [Authors].[Id])
GO
Now that we have our view, we created method which loads from this view:
Methods are once again platform independent and will be translated in regular methods by a .NET producer or into stored procedures by a persistence producer. Here’s what the SQL Server producer generates:
CREATE PROCEDURE [dbo].[Books_LoadFromFlatView] AS SELECT [vBooksFlatView].[Id], [vBooksFlatView].[Name], [vBooksFlatView].[Title] FROM [vBooksFlatView] RETURN
Now let’s run side by side EF’s dynamically generated query (on the left) and CodeFluent Entities’ static one (on the right) to compare results:
Hooray, looks good 
Points of Interest
Many to many relationships are easy to setup: create two properties, hold shift+click and make them point to one another, set its cardinality, and you’ve got your relation! Association tables and all related code will automatically be generated.
Initialize your generated database with data: specify instances to get test data or default data.
No dynamic code: CodeFluent Entities generates stored procedures, views and etc. No dynamic code is used to so you can easily debug and predict what’s going to happen at runtime.
Platform Independent: all this logic is outside of your code, it documents your application and provides a clear picture of what the application does and how it does it. Furthermore, you can add producers to translate this model to other technologies: want to go from C# to VB.NET? Push a button. Support Oracle Database or generate WCF Services? Add a producer.
Cheers,
Carl Anderson
Designing Databases with CodeFluent Entities: Inheritance
CodeFluent Entities tries to reduce the impedance mismatch between relational worlds and object-oriented worlds as much as possible and a good example of this I guess is how CodeFluent Entities supports inheritance across layers of your application.
Using CodeFluent Entities, an entity hierarchy defined in your model will be translated following the Table-Per-Type pattern in the persistence layer, when, in the generated business object model, this will be a regular class inheritance.
For instance, say we have the following hierarchy:
Here’s the result in the database:
Furthermore, data access methods will be modified accordingly to load the objects as expected in .NET. For instance, here’s the “Load” stored procedure that is automatically generated for the DiscountProduct entity:
CREATE PROCEDURE [dbo].[DiscountProduct_Load] ( @Id [uniqueidentifier] ) AS SET NOCOUNT ON SELECT DISTINCT [DiscountProduct].*, Product].* FROM [DiscountProduct] INNER JOIN [Product] ON ([DiscountProduct].[Product_Id] = [Product].[Product_Id]) WHERE ([DiscountProduct].[Product_Id] = @Id) RETURN
Cheers,
Carl Anderson
Designing Databases with CodeFluent Entities: Names
As explained in previous posts, CodeFluent Entities is way more that an ORM: you’re not just mapping classes to database objects, instead you’re creating a model which will be translated into stuff. Therefore if you add persistence producers such as SQL Server, Oracle Database or SQL Azure a database will be created or updated from it (see continuous generation from more info).
Creating a database implies naming a lot of stuff: tables, columns, constraints, stored procedures, etc., so I thought I’d give you a little details on how it works and how you can control this to create your own database.
First, CodeFluent Entities deduces names automatically from your model:
- Table names are identical to entity names,
- Columns are named “<EntityName>_<PropertyName>”,
- Procedures are named “<EntityName>_<MethodName>”
Users can override this logic by:
- defining a “Persistence Name” in the Property Grid on the object you want to change (entity, property, method, view, ..)
- setting a “Persistence Property Name Format” at the project level to customize all property names of your project (e.g. replace the default “{0}_{1}” by “{1}” to remove the entity name prefix from all column names),
- changing the naming convention
In practice a naming convention is a class implementing a naming convention rule which CodeFluent Entities will use to name persistence objects. Consequently changing the naming convention used will change the way all objects are named.
Several naming conventions are provided out-of-the-box and they all derive from one another, hence they benefit from the features of their parents. The hierarchy is as follows:
- BaseNamingConvention (default)
- FormatNamingConvention: modifies the name format of tables, columns, procedures, parameters, constraints.
- LowerCaseNamingConvention: sets all database object names in lower case.
- UpperCaseNamingConvention: sets all database object names in upper case.
- DecamelizeNamingConvention: decamelizes all names (FirstName –> First_Name)
- DecamelizeLowerCaseNamingConvention: all in lower case and decamelized (FirstName –> first_name)
- DecamelizeUpperCaseNamingConvention: all in upper case and decamelized (FirstName –> FIRST_NAME)
Furthermore you can create your own custom naming convention by implementing the INamingConvention or by deriving from existing ones.
Cheers,
Carl Anderson
CodeFluent Entities Supports Multiple Database Systems
Persistence producers translate your platform independent model into scripts and deploy them on the specified database to create your persistence layer.
Furthermore the persistence layer used is independent from the upper layers (.NET classes, services, user interfaces): switching your persistence layer does not change upper layers.
Therefore, using CodeFluent Entities allows you to create very flexible apps where switching or supporting a new database system comes down to switch or add producers:
Cheers,
Carl Anderson
What’s CodeFluent Query Language?
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.
Cheers,
Carl Anderson
