Table of Contents

Overview

PLINQO, which stands for Professional LINQ to Objects, is a collection of CodeSmith templates that are meant to replace and extend the LINQ to SQL designers that are included with Visual Studio 2008.

Features

The templates have the following features.

Advantages Over Designer

There are several advantages to using the PLINQO templates over the Visual Studio designer. Here is a list of the main reasons:

  1. Remove the designer black box and allow for customization of the output while still retaining the ability to use the .dbml designer to make customizations inside of Visual Studio.
  2. Ability to easily generate your entire .dbml file for a database and then the ability to regenerate that .dbml file as the schema changes. The regeneration preserves any customizations you may have made such as entity, property and relationship names. With the designer, if you make a database change, you need to drop the entity and re-add it to get any new columns or data type changes, which would cause you to lose any customizations you may have made. Also, using the templates allows you to exclude unwanted tables, stored procedures and views using filter expressions and automatically strip / clean entity and property names of things like prefixes and suffixes that your database schema may be using (ie. tbl_Customer to Customer).
  3. A business rules engine that allows you to enforce things like property length rules, required field rules, regex data validation rules as well as several other built in rules including authorization rules. The SubmitChanges method on the data context object will automatically run the rules against any entities in your change set. If all rules are not met, a BrokenRulesException will be thrown that contains a list of the broken rules.
  4. A manager class is generated for each entity that encapsulates all actions taken on an entity. Known common actions like retrieving entities by primary key, indexes, and foreign keys are generated. Any custom actions can be added and will be preserved during regeneration. While LINQ makes it easy to sprinkle your data access logic throughout your entire application, we still believe its poor design to do so and that is why we have included the manager classes.

Quick Start

Use the following steps to get started using the Linq to Sql templates.

  1. Create a new Class Library project in Visual Studio 2008.
  2. Add a new CodeSmith project file to the Visual Studio project.
  3. Add a new Output to the project file for the Dbml.cst template.
  4. Add another Output to the project file for the Entities.cst template.
  5. Optionally, Add an Output for the Managers.cst template.
  6. Optionally, Add an Output for the Queries.cst template
  7. Set the Sample.csp -> Output Options -> Add Outputs to Project to unchecked.  The templates update the project for you.
  8. Finally, Generate the Outputs. (Figure 5)

Dbml.cst Template

The Dbml.cst template is used to create a LINQ to SQL dbml file.  The file conforms to the Microsoft DbmlSchema.xsd schema.  This is the same document that the LINQ to SQL designer uses.  The generated dbml file from this template can also be edited from the LINQ to SQL designer. 

The template will create a new file if it doesn't exist.  If the file does exist, the template will read it in and update it.  This allows you to make changes to the file and not have it overwrite if the template is re-ran.  However, only some of the attributes are safe from overwriting.  Here is a list of safe attributes.  They will be listed as an xpath.

Safe Attributes to change in the Dbml file ...

Warning: Be aware that the template will drop tables, columns and associations that it did not find in the database.

Properties on the Dbml.cst template:

Property Description
CleanExpression List of regular expressions to clean table, view, column and procedure names.  Any matched text found will be removed from the name.
IgnoreList List of regular expressions used to ignore tables, views and procedures when generating mapping.
IncludeFunctions Include stored procedures and user functions in mapping.
IncludeViews Include views in mapping.
SourceDatabase The source database to generate the dbml file for.
ContextNamespace The namespace to use for the context class file.
EntityNamespace The namespace to use for the entity class files.
DbmlFile The path to the dbml file to generate.

Entities.cst Template

The entities template generates the entity classes needed by LINQ.  The classes are generated from a dbml file.  You can modify the names for classes and properties by editing the dbml file.  See Dbml.cst for a list of safe attributes to change in the dbml file.

The template will generate 2 files for every Type in the dbml file.  One file will be the generated partial class that can not be changed as it is overwritten when the template is re-ran.  It will have the following file name... <entity>.Generated.vb

The second file is a partial class that can be modified as it will not be re-generated.  You can implement the partial methods in this file.  Some partial method stubs are created by default. This file will be named... <entity>.vb

If you set the project file property on the template, the generated files will be added to the project.  The file that can not be modified will be hidden under the file that can be changed.

Properties on the Entities.cst template:

Property Description
DbmlFile The path to the dbml file used generate the entities from.
OutputDirectory The folder to save the generated files.
ProjectFile The Visual Studio project file to add the generated files to.

Managers.cst Template

The manager template is for helping you get started with business logic for the LINQ entities.  The managers will have common queries that are created from keys and indexes on the table.  The manager will also have rules for the entity properties to make sure required fields are not null and that the length of a string does not exceed the max length the column allows.

The template works by creating a second partial class that has a Manager property.  The manager will then have a property for each entity that has a manager. Here is a sample of the syntax for using the managers:

Dim db As New PetshopDataContext( Enter Connection info here)

' use the primary key

Dim prod As Product = db.Manager.Product.GetByKey(1)

'By CategoryID

Dim bugs As IQueryable = db.Manager.Product.GetByCategoryId("Bugs")

' the methods return IQueryable so you can add expressions

Dim bugs As IOrderedQueryable = db.Manager.Product.GetByCategoryId("Bugs").OrderBy(Function(prod As Product) prod.Name)

The manager also provides a business rules engine to your entities. In addition to the default validation rules that are generated, you can add custom rules by implementing the AddRules partial method in the custom entity class.

Shared Partial Sub AddRules()

    ' Rule allows the Name property to be a max of 150 characters.

    RuleManager.AddShared(Of Task)(New LengthRule("Name", 150))

    ' Rule that validates the value of the property using regex.

    RuleManager.AddShared(Of Task)(New RegexRule("Name", ".*"))

    ' Rule allows only users in certain security roles to update.

    RuleManager.AddShared(Of Task)(New UpdateRule( New String() = { "Administrator", "Updaters" }))

End Sub

Properties on the Managers.cst template:

Property Description
SourceDatabase The source database to keys and indexes from for generating the manager classes.
DbmlFile The path to the dbml file used generate the manager classes from.
ManagerContextName The class name of the DataContext that supports the managers.
ManagerDirectory The folder to save the generated manager files.
ManagerNamespace The namespace to use for the generated manager class files.
ProjectFile The Visual Studio project file to add the generated files to.

Queries.cst Template

The queries template is an optional alternative to the manager template.  If you don't want to use the manager framework, you can use this template to generate some common queries for an entity.  While its possible to use both the manger and query templates, they do duplicate some functionality. The template works by generating an extension module for Table<Entity>.  This allows the queries to be off the DataContext. Where as Manager uses a partial class to hide the generated code. Queries.cst uses CodeSmiths preserved merge capability.

Dim db As New PetshopDataContext( Enter Connection info here)

Dim prod As Product = db.Product.GetByKey(1)

This is an example of what the extension class looks like.

/// <summary>

/// The query extension class for Task.

/// </summary>

Public Module ProductQueryExtension

    ''' <summary>

    ''' Gets an instance by the primary key.

    ''' </summary>

    <Extension()> _

Public Function GetByKey(entity As Table(Of Product), productId As String ) As Product

If entity.Context.LoadOptions Is Nothing Then

Return Query.GetByKey.Invoke(DirectCast(entity.Context,PetshopDataContext), productId)

Else

Return entity.FirstOrDefault(Function(p)p.ProductId = productId)

End If

End Function

''' <summary>

''' Gets a query by an index.

''' <summary>

 <Extension()> _

Public Function GetByName(entity As Table(Of Product), [Name] As String) As IQueryable(Of Product)

If entity.Context.LoadOptions Is Nothing Then

Return Query.GetByName.Invoke(DirectCast(entity.Context,PetshopDataContext), [Name])

Else

Return entity.Where(Function(p)p.Name = [Name])

End If

End Function

 

   

     'Insert User Defined Extensions here.

'Anything outside of this Region will be lost at regeneration

#Region "User Extensions"

 

#End Region

 

#region "Query"

''' <summary>

''' A private class for lazy loading static compiled queries.

''' <summary>

Private Partial Class Query

Friend Shared ReadOnly GetByKey As Func(Of PetshopDataContext, String, Product) = _

CompiledQuery.Compile( _

Function(db As PetshopDataContext , productId As String) _

db.Product.FirstOrDefault(Function(p)p.ProductId = productId))

 

Friend Shared ReadOnly GetByName As Func(Of PetshopDataContext, String, IQueryable(Of Product)) = _

    CompiledQuery.Compile( _

Function(db As PetshopDataContext , [Name] As String) _

db.Product.Where(Function(p)p.Name = [Name]))

 

' Add your compiled queries here.

'Anything outside of this Region will be lost at regeneration

#Region "User Queries"

 

#End Region

End Class

#End Region

End Module

Properties on the Queries.cst template:

Property Description
SourceDatabase The source database to keys and indexes from for generating the manager classes.
DbmlFile The path to the dbml file used generate the query classes from.
QueryDirectory The folder to save the generated query extension files.
ProjectFile The Visual Studio project file to add the generated files to.

Known Issues

Roadmap

Download

Download the latest release from CodePlex.

History

 

*Breaking change