Welcome to the CodeSmith Community!

PLINQO - Supercharge LINQ to SQL

CodeSmith Community

A description has not yet been added to this group.

PLINQO - Supercharge LINQ to SQL

  • Comments 22

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.

  • Generate or update a LINQ to SQL dbml file from a database schema.
    • Includes all tables, stored procedures, functions, and views with the ability to exclude objects based on regex patterns.
    • Ability to automatically remove object prefix and suffixes (ie. tbl_ and usp_).
    • Dbml file can still be customized with the normal Visual Studio 2008 designer.
    • Dbml file can be refreshed based on the current database schema without losing customizations. (See Safe Attributes)
  • Generation of the LINQ to SQL DataContext class.
  • Generation of the LINQ to SQL entity classes.
    • Generates one file per entity instead of one massive file.
    • Generates partial classes where custom code can be written and won't be overwritten.
    • Generated entity files are added to the project as code behind files to their corresponding custom entity files.
  • Generation of entity manager classes.
    • Adds customizable business rules engine to enforce entity validation, business and security rules.
    • Provides access to common queries based on primary keys, foreign keys, and indexes.
    • Common queries are exposed as IQueryable so they can be extended.
  • All templates can be customized to meet your needs.

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. (Figure 1)
    Figure 1 - Sample Solution
  3. Add a new Output to the project file for the Dbml.cst template. (Figure 2)
    Figure 2 - Add Dbml Output
  4. Add another Output to the project file for the Entities.cst template. (Figure 3)
    Figure 3 - Add Entities Output
  5. Optionally, Add an Output for the Managers.cst template. (Figure 4)
    Figure 4 - Add Managers Output
  6. Set the Sample.csp -> Output Options -> Add Outputs to Project to unchecked.  The templates update the project for you.
  7. Finally, Generate the Outputs. (Figure 5)
    Figure 5 - Sample Generated

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

  • Database/@Class - The name of the DataContext class that will be generated.
  • Database/@EntityNamespace - The namespace for the entity classes.
  • Database/@ContextNamespace - The namespace for the DataContext class.
  • Table/@Member - The property name for the table in the DataContext class.
  • Type/@Name - The name of the entity class.
  • Column/@Member - The property name for the column in the entity class.
  • Column/@Storage - The private field LINQ to SQL will us to assign values to.
  • Association/@Member - The property name for this association.
  • Association/@Storage - The private field LINQ to SQL will us to assign values the association to.
  • Function/@Method  - The name of the method for the database procedure.
  • Parameter/@Parameter - The method argument name that maps to the database procedure parameter.

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.  Any database objects that match one of these regular expressions will be ignored.
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.cs

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

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

SampleManagerContext db = new SampleManagerContext();

// use the primary key

Task task = db.Manager.Task.GetByTaskID(taskId);

// use a foreign key

var myTasks = db.Manager.Task.GetByAssignedID(userId);

// the methods return IQueryable so you can add expressions

var openTasks = db.Manager.Task.GetByStatusID(statusId).OrderBy(t => t.CreateDate);

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.

static partial void AddRules()

{

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

    RuleManager.AddShared<Task>(new LengthRule("Name", 150));

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

    RuleManager.AddShared<Task>(new RegexRule("Name", ".*"));

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

    RuleManager.AddShared<Task>(new UpdateRule(

        new string[] { "Administrator", "Updaters" }));

}

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.

Known Issues

  • The generated DataContext does not set the connection string like the LINQ to SQL designer

Roadmap

  • Improve manager template
  • Unit test generation
  • Web service generation
  • ASP.NET, Winforms and WPF UI generation
  • Visual Basic support

Download

Download the latest release from Google Code.

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • * Please enter your name
  • * Please enter a comment
  • Post
  • Pingback from  PLINQO - Supercharge LINQ to SQL - Paul Welter

  • Linq to sql的Codesmith模板,非常好用:)

  • PLINQO, which stands for Professional LINQ to Objects, is a collection of CodeSmith templates that are

  • PLINQO, which stands for Professional LINQ to Objects, is a collection of CodeSmith templates that are

  • Hi Paul,

    Great templates!

    The only problem is that I can't get Northwind stored procedures to be compiled I get wrong properties generation - empty names for one property...

  • Alex,  

    You can change the name of the function that is a duplicate by editing the Function/@Method attribute in the dbml file.  That should get you by this problem for now. I will add a check for function names matching table names.

    ~ Paul

  • Thanks Paul, but the problem is not duplicate in the function name, but wrong name of the properties for function parameters...

    Do you compile generated code for Northwind database without problems?

  • I'm experiencing all kinds of problems when I use these templates:

    1.  "The using directive for System.Linq appeard previously in this namespace"

    Getting this on at least all of my manager classes

    2.  "The type or namespace name 'Codesmith' could not be found ..."

    Getting this all over the place as well.

    Anyways, its been about 4 hours of trying to figure out how to resolve these problems ... and nada.

    HELP!!!!

    thanks - wg

  • One more exception I'm getting (using Northwind as my sample DB):

    "The type 'LTS.BL.NorthwindDataContext' already contains a definition for 'SalesByCategory'"

  • Alex,

    Yes, I was able to get the generated code to compile from northwind after i renamed the SalesByCategory function get solve the duplicate name issue.  Maybe you could send me a sample of what is failing for you.

    ~ Paul

  • wgpubs,

    I forgot to mention that you need to add a ref to the CodeSmith.Data project that is included in the zip file.  

    Your issue with SalesByCategory is that there is a view by that name that ends up as a property on the datacontext.  There is also a stored procedure by that name, so you get a method by the same name.  You'll need to edit the dbml file to rename the function as stated in another comment.

    Not sure about the "System.Linq appeard previously ..." error.  After making the above changes and you still get the error, feel free to send me a sample so i can debug.

    thanks for the comments,

    ~ Paul

  • PLINQO, which stands for Professional LINQ to Objects, is a collection of Code Smith templates...

  • Schema.cs

    ===========  

    public static class DbmlFile

       {

           private static XmlSerializer _serializer = new XmlSerializer(typeof(Database), DbmlConstant.DbmlNamespace);

           public static void Save(Database db, string fileName)

           {

               XmlWriterSettings ws = new XmlWriterSettings();

               ws.Encoding = Encoding.UTF8;

               ws.Indent = true;

               XmlSerializerNamespaces xsn = new XmlSerializerNamespaces();

               xsn.Add("", DbmlConstant.DbmlNamespace);

               using (StreamWriter sw = new StreamWriter(fileName))

               {

                   XmlWriter w = XmlWriter.Create(sw, ws);

                   _serializer.Serialize(w, db, xsn);

                   w.Flush();

                   w.Close();

               }

               Debug.WriteLine("Saving Dbml File:" + fileName);

    /*            StringBuilder sb = new StringBuilder();

               using (StringWriter sw = new StringWriter(sb))

               {

                   XmlWriter w = XmlWriter.Create(sw, ws);

                   _serializer.Serialize(w, db, xsn);

                   w.Flush();

                   w.Close();

               }

               Debug.WriteLine("Saving Dbml File:" + fileName);

               File.WriteAllText(fileName, sb.ToString(), Encoding.UTF8);

    */

           }

    =========================

    应做如上改动以输出:正确编码的DBML文件

  • PLINQO (pronounced “Plinko”) or Professional LINQ to SQL is a collection of CodeSmith templates

  • 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 Generate or update a LINQ to SQL dbml file

Page 1 of 2 (22 items) 12
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • * Please enter your name
  • * Please enter a comment
  • Post