Breaking Change in PLINQO 3.2

No, PLINQO 3.2 has not been released yet. However, when it is, there will be a few small but breaking changes introduced with it. As we often recommend that you use PLINQO's latest nightly builds, we wanted to bring this to your attention ASAP.

Breaking Query Extension Changes

We have added the UniqueMethodPrefix property to the Queries.cst template. Now PLINQO will generate two methods for each unique index and primary key. Methods that had previously returned an instance of an entity will now return IQueryable, thus updating to the latest version may result in compiler errors.

• MethodPrefix Property
  
  • All methods generated return IQueryable.
  • Defaults to "By"
• UniqueMethodPrefix
  
  • All methods generated return single instance of an entity.
  • Defaults to "GetBy"

Don't worry! These method names are still configurable! If you don't like this update, you can change the defaults of your Queries.cst to be whatever you prefer! Also, functionality has only been added, not lost! PLINQO now has methods that return both object and IQueryable types for queries with unique results!

Example

Product p;
using (var db = new PetshopDataContext())
{
    /**** PLINQO 3.1 ****/

    // This "ByKey" method used to return a unique result.
    p = db.Product.ByKey("BD-02");

    // To cache a product by Id, a Where statement had to be used in order to build the IQueryable.
    p = db.Product.Where(p => p.ProductId == "BD-02").FromCache().First();

    /**** PLINQO 3.2 ****/

    // There is no longer a "ByKey" method, it has been replaced with a "GetByKey" method.
    p = db.Product.GetByKey("BD-02");

    // Because there are now By methods for all properties (even ones with unique results),
    // you can now use the extension methods for your cache!
    p = db.Product.ByProductId("BD-02").FromCache().First();
}

Why make these changes?

When we first developed the naming conventions for our extension methods, we did not have as many features that extended IQueryable results. At the time, it made sense that getting by a unique set should only return a single entity rather than an IQueryable with a single result.

Times have changed, PLINQO has expanded and gotten better. PLINQO now offers more features, and frankly, we felt that the API needed to keep up. This update offers the following advantages...

• Unique result query extensions now work with batch queries.
• Unique result query extensions now work with the query result cache.
• Query Extensions are now more consistent, all "By" methods now return same type.

Enjoy!

Why CodeSmith Projects?

To me, asking "why would I want to use a CodeSmith Project?" is a lot like asking "why would I want to use a Visual Studio Solution?" Well, for starters, it...

• Saves you time.
• Takes less effort.
• Exposes more functionality.
• Drastically simplifies automation.
• IS EASY!

CodeSmith Projects let you control the generation of all your CodeSmith Templates, just like Visual Studio Solutions let you control the compilation of your Visual Studio Projects.

The CSP

A .csp (CodeSmith Project) file, is a simple concept. Like a VS solution or project, it just contains a list of files (in this case, CodeSmith Templates) a set of configuration data that it needs to take action against those files (in this case, to execute those templates). This allows you to run multiple templates from one convenient location, and to your persist the settings for those templates in between executions.

A CodeSmith Project is easy to create, just right click in CodeSmith Explorer and create a new project, or right click in Visual Studio and add a file, then select CodeSmith Project. Heck, if those aren't easy enough for you, you can always create a new file with the .csp suffix, and then when you right click in windows explorer you will be able to manage or execute it directly from the right click context menu!

Managing your CodeSmith Project is equally easy. Regardless of where you manage outputs from, you are going to get the same friendly Manage Outputs dialog. It is going to allow you to add, remove, or copy your template entries. You can disable or enable templates for generation, alter their order of execution, or even choose to execute a specific template on the spot. When managing specific template properties the standard CodeSmith Property Grid is used, giving you access to all of the properties and UI pickers from CodeSmith Studio.

Visual Studio Integration

As I mentioned above, you can add a .csp directly into any Visual Studio project. This gives you several advantages...

• Automated integration of your template output directly into your Visual Studio Project.
  
  • Don't worry about adding or updating your project files, the CodeSmith Project will take care of it for you!
  • Note: This includes recognition by any plug-ins you may have in Visual Studio, such as source control!
• Ability to automatically generate on build.
  
  • Keeps all developers and every build in sync with the latest template output!
  • Generating an ORM? Never be out of sync with the database schema again!

Moral of the Story

Develop smarter, not harder; use CodeSmith Projects to automate your code generation. For more, check out my CodeSmith Projects Video Tutorial.

How PLINQO Improves Performance

Performance Q&A

One of the first questions people always ask about PLINQO is "do we have any performance statistics?" Well, to quote a wiser man than myself: "People can come up with statistics to prove anything. 14% of people know that." ~ Homer J. Simpson

PLINQO's primary plan for performance improvement is simple: reduce the number of trips that you need to make to the database. A round trip to the database and back is one of the most costly things an application can do, but it's also one of the most common things an application must do. PLINQO offers several easy to use features that can dramatically reduce database transactions, but it's up to the developer to use them.

Bottom Line: PLINQO can and will out preform standard LINQ to SQL. By how much? That is entirely up to you!

Batch Updates and Deletes

Updates with LINQ to SQL

To update records with LINQ to SQL, you must retrieve objects from the database, make your updates, and then commit the changes back to the database. This requires TWO round trips to the database.

1) Build the query, query the database, wait for a response, store the data in memory.
2) Make your updates, commit the changes to the database, wait for the response.

Batch Updates with PLINQO

PLINQO offers a batch update method on its table objects. To update as many records as you want, merely create a query, send it to the database, and get back your result. Unlimited rows, ONE trip.

1) Build the query, update the database, get the response.

// Update all Tasks with a StatusId of 1 to have a StatusId of 2,
context.Task.Update(t1 => t1.StatusId == 1, t2 => new Task() { StatusId = 2 });

Batch Deletes

To delete a record in LINQ to SQL you must first retrieve that record. That is TWO complete round trips to the database to delete one little record! PLINQO allows for batch deletes in the same manner as batch updates, as many records as you want, without loading records into memory, and in just ONE trip.

// Delete all tasks where StatusId is 2,
context.Task.Delete(t => t.StatusId == 2);

Stored Procedures

While the LINQ to SQL designer does support stored procedures, it only supports returning a single result set. PLINQO supports stored procedures with multiple result sets, and provides a simple way to handle those results. Again, this is yet another way PLINQO helps get you more data with fewer trips to the database.

// Create Procedure [dbo].[GetUsersWithRoles]
// As
// Select * From [User]
// Select * From UserRole
// GO
var results = context.GetUsersWithRoles();
List<User> users = results.GetResult<User>().ToList();
List<UserRole> roles = results.GetResult<UserRole>().ToList();

Batch Queries

In LINQ to SQL every query is a trip to the database. It doesn't matter if you have five queries in succession that require no logic in between, you must still make each and every query separately. The PLINQO DataContext offers an ExecuteQuery overload that will execute as many queries as you want in a single transaction. This is an extremely simple feature to use, and it can drastically improve performance in every day development scenarios.

var q1 = from u in context.User select u;
var q2 = from t in context.Task select t;
IMultipleResults results = context.ExecuteQuery(q1, q2);
List<User> users = results.GetResult<User>().ToList();
List<Task> tasks = results.GetResult<Task>().ToList();

PLINQO Cache

Yo dawg! I heard you like cache, so we put a caching mechanism in yo server side cache, so you can cache while you cache!

...but seriously, PLINQO now includes a built in caching mechanism! :)

• All IQueryable result sets can now be dynamically cached/retrieved right from the query.
• The cache is accessible via an IQueryable extension method (FromCache), thus the cached objects are not DataContext specific.
• The cache duration is set at the time of the query, it can be stored for a specific time span or use a sliding expiration.

Example

using (var context = new PetshopDataContext())
{
    // Cache a result set. (A query is made to the DB.)
    var birds = context.Product.GetByCategoryId("BIRDS").FromCache().ToList();
    // Get a single entity from that cache. (No query is made to the DB.)
    var firstBird = context.Product.GetByCategoryId("BIRDS").FromCache().FirstOrDefault();

    // Specify number of seconds to cache. (A query is made to the DB.)
    var penguin = context.Product.GetByName("Penguin").FromCache(60).FirstOrDefault();
    // Get the same result set back as a list. (No query is made to the DB.)
    var penguins = context.Product.GetByName("Penguin").FromCache(60).ToList();
}

Configuration

This feature is is not yet available in an official PLINQO release, to use the cache you will have to download the latest PLINQO Nightly Build.

To access the FromCache extension method you must...

1. Include a reference to the following assemblies...
   1. CodeSmith.Data
   2. System.Data.Linq
   3. System.Data.Services
   4. System.Web
2. Include a using/import statement for the CodeSmith.Data.Linq namespace.

Using CodeSmith.CodeFileParser

The CodeFileParser

In CodeSmith v5.1 we are introducing a new feature: the CodeFileParser!

The Inspiration

We are always wondering, how can we make CodeSmith better? We noticed that more and more cars have the 'flex fuel' logo on them these days; so we asked ourselves, "How can we learn from that? How can we make CodeSmith's fuel more flexible?" Well, CodeSmith is fueled by metadata, so how can we make metadata more flexible? ...and then it came to us: add more metadata!

The Feature

The CodeFileParser will make it easier for CodeSmith templates to use code files as their metadata source. It is a simple class that takes in a file path, or even a content string, parses the class, and returns an easy to walk/search DOM object. So think about that for a moment; this means that you can generate code, *dramatic pause*, from code.

I'll just let that sink in...
...take your time...
...pretty sweet, huh?

The Implementation

The Class

CodeSmith.Engine now contains the CodeFileParser class. It is capable of parsing both C# and VB code. As mentioned above, it is capable of taking in a file path or a content string, and it will take care of reading the file (or string) and parsing the contents for you. Under the hood the CodeFileParser uses the public NRefactory libraries created by the great team over at SharpDevelop.

// There are overloads that don't require basePath or parseMethodBodies.
public CodeFileParser(string fileName, string basePath, bool parseMethodBodies)
// There are overloads that don't require parseMethodBodies.
public CodeFileParser(string source, SupportedLanguage language, bool parseMethodBodies)

The Selection Methods

Most of the methods in NRefactory return position information in the form of Location objects, which, while very descriptive, are not the easiest thing to use when trying to take substrings or selections from the existing code. Because this can be very important when using the object DOM to assist with code generation, we have added several methods to assist with getting substrings and selections; these methods take in Location objects and return strings.

public string GetSectionFromStart(Location end)
public string GetSectionToEnd(Location start)
public string GetSection(Location start, Location end)

The CodeDomCompilationUnit

To quick and easily walk the DOM, the CodeFileParser exposes a (lazy loaded) property that returns System.CodeDom.CodeCompileUnit object. This is a standard .NET object that contains a complete code graph; this object is the quickest and easiest way to traverse your metadata. For more information about the CodeCompileUnit, please check out MSDN article.

The Visitor

When more advanced or customized information is required, the CodeFileParser exposes the CompilationUnit object, which is capable of taking in a visitor object to traverse the DOM and bring back specific data. This is an NRefactory feature, and it only requires that your visitor object implement the AbstractAstVisitor class.

The Example

The We're Already Using It!

We are already using the CodeFileParser in CodeSmith and our Plinqo templates! In CodeSmith we have implemented the CodeFileParser in our InsertClassMergeStrategy; it allows us to parse the existing code file and determine where we need to insert our new content. In Plinqo we use the CodeFileParser to assist with our MetaData class merge; it allows us to make a map of all the properties in that class and then preserve their attributes during regeneration.

The Template Code

<%@ CodeTemplate Language="C#" TargetLanguage="Text" Debug="False" CompilerVersion="v3.5" %>
<%@ Property Category="2.Class" Name="TheFile" Type="CodeFileParser" Optional="False" %>
<%@ Assembly Name="CodeSmith.CodeParser" %>
<%@ Import Namespace="System.CodeDom" %>

<%  foreach(CodeNamespace n in TheFile.CodeDomCompilationUnit.Namespaces) { %>
Namespace: <%= n.Name %>
<%      foreach(CodeTypeDeclaration t in n.Types) { %>
    Type: <%= t.Name %>
<%          foreach(CodeTypeMember m in t.Members) { %>
        Member: <%= m.Name %>
<%          } %>
<%      } %>
<%  } %>

Small footnote, thanks to Seinfeld for inspiring my section title names in this blog post.

CodeSmith on Windows 7

CodeSmith on Windows 7

First and foremost: CodeSmith 5.0 is (unofficially) 100% compatible with Windows 7, 64 bit edition!

I say "unofficially" because obviously I am not allowed to make such a statement with out extensive testing, certification, and or blah blah blah...but hey, "it works on my machine!"

But seriously, we have done some internal testing here at CodeSmith Tools, and so far we have not experienced any issues with running CodeSmith Professional 5.0 on Windows 7 (x64). So for all you brave pioneers out there: that should be one less arrow to worry about!

Windows 7 Beta

I have been running the Windows 7 Beta on my personal laptop (an ASUS G50VT) since the beginning of January. So far, I have to give it TWO THUMBS WAY UP!

I am not proud to say it, but I pretty much skipped over Vista entirely...
*insert sob story about getting frustrated with Vista here*
...to Microsoft's credit, they were not afraid to try something new; and while SP1 did fix Vista, it was a little too late for me.

After reading almost nothing but very positive reviews (and feeling guilty about still living in 2001), I decided to grab a copy of Windows 7 and give it a shot. Windows 7 was a reasonable 3 gig download. The OS installed first try, and only took less than 30 minutes. Even on my picky gaming laptop, drivers were easy to install. Almost every application I have wanted to use installed first try, and the ones that didn't were almost all fixed by simply setting compatibility mode to Vista.

Aside from being both fast and stable, Windows 7 also has plenty of other good features. Everything about the new taskbar is phenomenal. The pinning, the preview pane combined with Aero peek...it's a great series of intuitive features that all come together to form a dynamic and sleek new interface. The OS provides native Virtual Hard Drive support; you can create, mount, even boot from them! Also, the new backup system takes advantage of this by allowing Windows to back up a system image straight to VSD.

Development on Windows 7

Having had such a good experience at home, I decided to try out Windows 7 at work. First try, no errors, no hang ups, no complications, I installed all the following software...

1. Visual Studio 2008 w/ SP1
2. Microsoft SQL Server 2008
3. CodeSmith 5.0
4. TestDriven.Net
5. NUnit
6. Asp.Net MVC RC2
7. Aptana Studio
8. Firefox 3.0 w/ Firebug 1.3
9. SlySoft Virtual CloneDrive

...and so far, everything has worked flawlessly.

So in conclusion, when I say CodeSmith works on Windows 7, I am also saying that it builds on Windows 7.

Defining Enum Tables

Back in January I posted about "Defining Many To Many" tables; and now (to take a note from the Colbert Report), here is Part 2 in my infinite part series...

Better Know an ORM Programmatic Definition: Defining Enum Tables!

The idea here is that we want to generate Enums from database tables; so for each table we specify as an Enum table, we want create an Enum and populate it's values from the rows in that table. As always, the goal is to make this solution be as generic as possible. We want this to be able to work on pretty much any database we throw at it, we want it to check for any usual pitfalls, and of course we want what we generate to be as useful as possible! Let's begin with an example...

Example Table (Input)

Table Name: StarTrek
Columns: Id (int), Name (string), Captain (string)
Row 1: "Original", "James Tiberius Kirk"
Row 2: "Animated", "James Tiberius Kirk"
Row 3: "TNG", "Jean-Luc Picard"
Row 4: "DS9", "Benjamin Sisko"
Row 5: "Voyager", "Kathryn Janeway"
Row 6: "Enterprise", "Scott Bakula? Seriously?"

Example Enum (Output)

public enum StarTrek : int
{
    /// <summary>James Tiberius Kirk</summary>
    Original = 1,
...etc...
    /// <summary>Scott Bakula? Seriously?</summary>
    Enterprise = 6
}

So, what is the logic?

1) Explicitly identify select the table.

While associations can be determined by examining keys and other qualities, Enum tables just don't have enough unique qualities to identify in that manner; thus we will want to explicitly choose our Enum tables. For this task I recommend a Regex for Enum table names; however you could always use a good old fashion table list. Now that we have identified that the table SHOULD be an Enum, we need to determine if it CAN be an Enum table...

2) The table must have a primary key.

Enums values can be assigned a numeric value, so to allow for association mapping and logical comparisons it's a good idea that our generated Enums are assigned meaningful values at generation time. NOTE: While assigning a numeric value to Enum values can server many different purposes, the following was specifically chosen because it allows for Enums to act as database associations for business entity objects.

2.A) The table must have a primary key composed of a single column. (It's hard to have a composite key that evaluates to a single numerical value.)

2.B) The primary key column must be of a number type. (This is so that the Enum values can be assigned to the key value.)

3) The table must have a column to specify the values.

Well if the table is the Enum itself, where are the values going to come from? You have to chose which column the value is going to come out of! Again I recommend using a Regex to find this column by name, but if that fails (or if you are feeling lazy) you could default to taking the first column of a string type.

4) The table must have at least one row.

Firstly, this is because there's not a lot of use for an empty Enum; but also, some languages (such as VB) don't support it.

Additionally

When generating the enums, it might come in handy to generate the description for each value as well (as we did in the example above); so, in the code below is an extra function for finding that description with (surprise surprise) a Regex.

And finally, here is what your code might look like...

public static Regex EnumTableNameRegex = new Regex("(E|e)num$", RegexOptions.Compile);
public static Regex EnumValueColumnRegex = new Regex("((V|v)alue)|((N|n)ame)|((T|t)ype(C|c)ode)", RegexOptions.Compile);
public static Regex EnumDescriptionColumnRegex = new Regex("(D|d)esc", RegexOptions.Compile);

public bool IsEnum(TableSchema table)
{
    return EnumTableNameRegex.IsMatch(table.Name)                            // 1) Matches the enum regex.
        && table.PrimaryKey != null                                                          // 2) Has a Primary Key...
        && table.PrimaryKey.MemberColumns.Count == 1                         // a) ...that is a single column...
        && IsNumeric(table.PrimaryKey.MemberColumns[0].SystemType)   // b) ...of a number type.
        && !string.IsNullOrEmpty(GetEnumNameColumnName(table))         // 3) Contains a column for name.
        && table.GetTableData().Rows.Count > 0;                                      // 4) Must have at least one row.
}

private bool IsNumeric(Type t)
{
    return t == typeof(byte)
           || t == typeof(sbyte)
           || t == typeof(short)
           || t == typeof(ushort)
           || t == typeof(int)
           || t == typeof(uint)
           || t == typeof(long)
           || t == typeof(ulong);
}

public string GetEnumValueColumn(TableSchema table)
{
    string result = GetEnumColumn(table, EnumValueColumnRegex);

    // If no Regex match found, use first column of type string.
    if (string.IsNullOrEmpty(result))
        foreach (ColumnSchema column in table.Columns)
            if (column.SystemType == typeof(string))
            {
                result = column.Name;
                break;
            }

    return result;
}

private string GetEnumColumn(TableSchema table, Regex regex)
{
    string result = string.Empty;

    foreach (ColumnSchema column in table.Columns)
        if (regex.IsMatch(column.Name))
        {
            result = column.Name;
            break;
        }

    return result;
}

public string GetEnumDescriptionColumn(TableSchema table)
{
    return GetEnumColumnName(table, EnumDescriptionExpressions);
}