Archive

Posts Tagged ‘SQL’

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

CodeFluent Entities vs Entity Framework : Cascade Delete

February 25, 2015 Leave a comment

Ensuring referential integrity is something very important in RDBMS. When you delete a customer you may want to delete all related information, this what is called Cascade Delete.

Let’s see how CodeFluent Entities and Entity Framework (with Model-First) handle this feature.

Entity Framework

First of all you have to add a navigation property:

Then you can select the association (the line between entities) and configure it:

The generated SQL contains:

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CustomerId] in table 'OrderSet'
ALTER TABLE [dbo].[OrderSet]
ADD CONSTRAINT [FK_CustomerOrder]
    FOREIGN KEY ([CustomerId])
    REFERENCES [dbo].[CustomerSet]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

Entity Framework Model First does not allow cascade delete on many to many association:

CodeFluent Entities

As with EF, we also need to create a relation:

All parameters are available on the same screen. Instead of EF, it’s all English so everyone can understand options.

When using this option, the cascade delete is done by the generated stored procedure looks like:

BEGIN TRANSACTION
DELETE [Order] FROM [Order]
    INNER JOIN [Customer] ON ([Order].[Order_Customer_Id] = [Customer].[Customer_Id])
    WHERE ([Customer].[Customer_Id] = @Customer_Id)
DELETE FROM [Customer]
    WHERE ([Customer].[Customer_Id] = @Customer_Id)
COMMIT TRANSACTION

Maybe you prefer to use the SQL instruction “ON DELETE CASCADE”. This option is also available with CodeFluent Entities:

Using this setting the generated constraint now uses ON DELETE CASCADE:

ALTER TABLE [dbo].[Order] ADD CONSTRAINT [FK_Ord_Orr_Cus_Cus] FOREIGN KEY (
[Order_Customer_Id]
) REFERENCES [dbo].[Customer](
[Customer_Id]
) ON DELETE CASCADE

Conclusion

Both solutions support cascade delete functionality. CodeFluent Entities has two ways to support this feature and is understandable by non-technical people.

CodeFluent Entities vs Entity Framework: Naming convention

February 12, 2015 Leave a comment

This is the first of many in a series of post covering the difference between Entity Framework 6 and CodeFluent Entities. Today we’re talking about naming convention.

Naming convention allows to transform the name of a concept in the model (entity, property, constraints, etc.) for generating database. For instance if you have an entity “Product” in your model, the generated table name may be “Product”, or “PRODUCT”, “prod” or maybe something else according to you naming convention. So naming convention is about standardization and also about making your DBA happy with the generating codeJ.

Entity Framework

Entity Framework Model-First doesn’t provide a simple way to apply a naming convention for the model. Instead you have to manually define names:

To automate the process you have to change default T4 templates located in Visual Studio installation folder, and the default generation workflow (TablePerTypeStrategy.xaml). Be sure to keep in sync the SQL template and the mapping (MSL) template.

Figure 1- Extract from the default T4 file

Then you can change model settings:

Entity Framework Code First has the notion of convention (does not work with Model First). This notion allows to set name of tables or properties:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Types().Configure(type => type.ToTable("EF_" + type.ClrType.Name));
     modelBuilder.Properties().Configure(prop => prop.HasColumnName("EF_" + prop.ClrPropertyInfo.Name));
}

Setting constraint names (foreign key names, index names) looks more complicated and requires more than one line of code.

CodeFluent Entities

CodeFluent Entities provides out of the box six naming conventions, and allows you to create your own naming convention.


 

To implement a custom naming convention you have to create a class that implements INamingConvention or inherits from BaseNamingConvention:

public class MyCustomConvention: BaseNamingConvention
{
 public override string GetName(INamedObject obj, IDictionary context)
 {
  Column column = obj as Column;
  if (column != null)
  {
   string name = (string)context["name"];
   Table table = (Table)context["table"];
   if (table != null)
   {
     column.DefaultName = "Default_" + table.Name + "_" + name;
   }
  }
  return base.GetName(obj, context);
 }

 public override string GetShortName(IShortNamedObject obj, IDictionary context)
 {
  Constraint constraint = obj as Constraint;
  if (constraint == null)
   return base.GetShortName(obj, context);

  return "myx_" + constraint.ConstraintType + "_" + constraint.Table.Name + "_" + constraint.ReferencedTable.Name;
 }
}

Conclusion

Entity Framework Model-First does not provide a simple way to define a naming convention. The Code-First approach is better for table name and column name, but it’s harder to name indices or foreign keys.

CodeFluent Entities provides out of the box six naming conventions, and let you write your own.

References :

 

Table-Valued Parameters: Basics

February 11, 2015 Leave a comment

First, what is Table-Valued Parameters ?

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data.

You can use table-valued parameters (TVP) to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Great news, you can use TVP with CodeFluent Entities! Let’s see how you can use TVP to bulk load rows based on a list of id.

Create a new CFQL method:

Note the usage of “[]” after the type name. The generated method is:

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

And the generated stored procedure:

CREATE TYPE [dbo].[cf_type_Customer_LoadByIds_0] AS TABLE (
 [Item] [uniqueidentifier] NULL)
GO
CREATE PROCEDURE [dbo].[Customer_LoadByIds]
(
 @ids [dbo].[cf_type_Customer_LoadByIds_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT [Customer].[Customer_Id], [Customer].[Customer_FirstName], [Customer].[Customer_LastName], [Customer].[Customer_DateOfBirth]
    FROM [Customer]
    WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids)))

RETURN
GO

Table-Valued Parameters require at least SQL Server 2008. Don’t forget to change the target of the SQL Server producer to use at least this version. Additionally set Legacy String Array Mode to false.

Additionally set Legacy String Array Mode to false.


References: http://www.softfluent.com/documentation/Methods_WorkingWithArrays.html

Happy coding,

The R&D Team.

Many to Many Relationships with CodeFluent Entities

June 14, 2012 Leave a comment

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

MyLibraryModel2

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:

view

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:

method

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:

result

Hooray, looks good Smile

 

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