PLINQO - Supercharged LINQ to SQL

In the time that LINQ to SQL has been available, we have been identifying ways to make LINQ to SQL better. We have compiled all of those cool tips and tricks including new features into a set of CodeSmith templates. PLINQO opens the LINQ TO SQL black box giving you the ability to control your source code while adding many new features and enhancements. It's still LINQ to SQL, but better!

What's New Quick Start Managers/Query Extensions Entity Clone Entity Detach Enum Generation Many to Many Relationships Auditing Data Services and WCF Query Result Cache

What's Improved Synchronizing Changes Organized Structure Rules Metadata Customization/Sync Performance Improvements Serialization

PLINQO's generated output is high quality, easy to maintain code that creates a solid foundation and will jump start any project on the road to success. PLINQO takes advantage of many CodeSmith features such as merge strategies, CodeSmith projects and Visual Studio integration.

PLINQO is available in C# and VB when you download the latest version of CodeSmith. Also, PLINQO is constantly on the move with new features and bug fixes. To stay on top of the latest with PLINQO, check out the downloads area.

Quick Start

A working project in seconds! Think about the time it normally takes to get a project up and running (creating the solution, add the projects, organize your code, etc). PLINQO has made this process as easy as selecting a database. Below is a picture of the CodeSmith Quick Start properties. As usual with CodeSmith, you are in control and can change any of the QuickStart properties to generate the project solution you want. Once you click generate, you can checkout all of the features of PLINQO by either playing around with the Dynamic Data interface, creating tests or moving toward completions of your project.

Only the SourceDatabase property is required.

Clicking generate produces the solution below. PLINQO even opens it for you!

The working solution includes a completely configured CodeSmith project(.csp) making regenerating an extremely easy process.

Clicking generate produces the solution below. PLINQO even opens it for you!

Easy Synchronization of Database Changes

Changes are an inevitable part of any project. The LINQ to SQL designer in Visual Studio provides a lot of support for manipulating entities, but does NOT make it easy when a refactor is required. The LINQ to SQL designer requires that the entity be dropped and recreated to generate the necessary updates. When the entity is dropped, any updates made for that entity are also lost and now must be re-created. This is time consuming, tedious and results in work being done over and over again. With PLINQO, make changes, right-click, generate, DONE! PLINQO makes it that easy. PLINQO supports syncing the database with the dbml while preserving any manual dbml updates.

Managers and Query Extension classes

PLINQO includes the option to generate a manager or query extension class 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 option to create manager classes or query extension classes.

Managers

The manager classes are built to encapsulate business logic and designed to contain the entire query so when parameters are passed in, the results are returned immediately.

Task task = context.Manager.Task.GetByKey(1);
IQueryable<Task> tasks = context.Manager.Task.GetByAssignedId(1);

Query Extensions

Each of the query extension methods generated are composable meaning you can chain the methods together to build the desired functionality out of smaller parts and take full advantage of reuse.

task = context.Task.GetByKey(1);
tasks = context.Task.GetByAssignedId(1).GetByStatusId(1);
List<Task> taskList = tasks.ToList();

Organized, structured code

LINQ to SQL does not put much thought into the user experience when database changes or customization is needed. You have to create your own classes when changes are needed. PLINQO is smart enough to know that the generated code is not the entire solution, but the rock solid foundation. Tremendous thought has been put into project structure and file organization. The larger a project gets, the more important this becomes. PLINQO creates partial classes and 2 files for each entity, manager and query extension. One file is an actively generated file and the other is specifically for customizing and extending the PLINQO architecture.

Query Result Cache

A vital aspect to any framework is the option to cache data. Anytime the same data is retrieved over and over, precious time and cpu cycles are wasted. PLINQO is focused on saving that precious time and cpu by providing the option to cache any LINQ to SQL query result. PLINQO takes advantage of the System.Web.Caching.Cache class to store the results of the LINQ to SQL queries with the option to use a sliding or absolute expiration and set a priority on the items stored in cache.

To cache query results in PLINQO, use the FromCache extension method located in the CodeSmith.Data.Linq namespace. Below is a sample caching query results using all the different options provided by PLINQO. Simply, exeute the LINQ to SQL query as you normally would, then append the FromCache extension and tell PLINQO how the results should be cached.

//By default, the cached results use a one minute sliding expiration with
//no absolute expiration.
var tasks = context.Task.ByAssignedId(UserId).FromCache();

//query result is now cached 300 seconds
var approvedUsers = context.User.ByIsApproved(true).FromCache(300);

//A sliding expiration is used. Each time this line is executed, the time in
//cache is reset to 5 minutes from the time of the call.
var createdTasks = context.Task.ByCreatedId(UserId).FromCache(TimeSpan.FromMinutes(5));

//adding a priority to the cached item
var roles = context.Role.FromCache(TimeSpan.FromMinutes(5), CacheItemPriority.High);

//Uses an absolute expiration.  The results of this query will not stay in cache
//longer than 15 minutes
var statuses = context.Status.FromCache(DateTime.UtcNow.AddMinutes(15));

//The results of this query are cached with high priority and will not stay in
//cache longer than 15 minutes
var tasksDueToday = context.Task.ByDueDate(DateTime.Now.Date).FromCache(
    DateTime.UtcNow.AddMinutes(15), CacheItemPriority.High);

Start saving time and money today and give the database a rest by taking advantage of PLINQO's query result cache.

Entity Detach

Inability to detach is what coders have complained about most when working with LINQ to SQL. Well, complain no more. PLINQO makes it easy to work with entities independent from a datacontext and attach to another when you are ready to save your changes. The following code is now possible thanks to PLINQO.

Task task = null;
using (var context = new TrackerDataContext())
{
    task = context.Task.FirstOrDefault(t => t.Id == 1);
    task.Detach();
}

task.StatusId = 1;

using (var context2 = new TrackerDataContext())
{
    context2.Task.Attach(task, true);
    context2.SubmitChanges();
}

The code shown here works without changes if your database supports row versioning. If row versioning is not supported in your database, the Update Check policy on each attribute in the dbml must be set to Never for detach to work.

Entity Clone

PLINQO takes advantage of the DataContractSerializer in WCF to provide a quality cloning solution for all entity objects. Simply call the Clone method on any entity generated by PLINQO and what you get is an object copied to its own memory location. Below is a quick sample of the clone method in action. A user object is retrieved from the database, cloned, one property is changed, a new user is saved to the database.

using (var context = new TrackerDataContext())
{
    var u = context.Manager.User.GetByKey(1);
    User clonedUser = u.Clone();
    clonedUser.Id = 0;
    context.User.InsertOnSubmit(clonedUser);
    context.SubmitChanges();
}

Enum Generation

PLINQO's Enum Generation can easily turn a table full of data into an enum. Many times enum values are stored and maintained in the database and in code resulting in the task of updating both locations when changes are needed. Making the same change twice just to keep things in sync is definitely a frustrating process. PLINQO makes it simple to define the list of Enum tables to generate and which fields contain the enum constant text and description. The result is enums are maintained in one spot and life is good.

PLINQO Enum generation easily turned priority table into an enum.

C# [DataContract] public enum Priority : int { [EnumMember] High = 1, [EnumMember] Normal = 2, [EnumMember] Low = 3, }

Metadata Customization/Syncing

The System.ComponentModel.DataAnnotations assembly introduced in .NET 3.5 SP1 provides another opportunity for PLINQO to make life easier. PLINQO generates and maintains a metadata class inside each entity. This metadata class allows you to easily add extra knowledge about your domain objects and their properties. Each time PLINQO is re-generated, DBML and database changes are synced while preserving any custom changes using a new feature in CodeSmith called the insert class merge strategy. The process of maintaining metadata on each entity in the system now can be as simple as regenerating your metadata classes.

Here is a picture of a MetaData class that sits inside a User class.

[CodeSmith.Data.Audit.Audit]
private class Metadata
{
    // Only Attributes in the class will be preserved.

    public int Id { get; set; }

    [Required]
    public string UserName { get; set; }

    [Required]
    [DataType(System.ComponentModel.DataAnnotations.DataType.Password)]
    public string Password { get; set; }

    [DataType(System.ComponentModel.DataAnnotations.DataType.EmailAddress)]
    public string EmailAddress { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public System.Data.Linq.Binary Avatar { get; set; }

    [Now(EntityState.New)]
    [CodeSmith.Data.Audit.NotAudited]
    public System.DateTime CreatedDate { get; set; }

    [Now(EntityState.Dirty)]
    [CodeSmith.Data.Audit.NotAudited]
    public System.DateTime ModifiedDate { get; set; }

    public System.Data.Linq.Binary RowVersion { get; set; }

    public EntitySet<Task> AssignedTaskList { get; set; }

    public EntitySet<Task> CreatedTaskList { get; set; }

    public EntitySet<UserRole> UserRoleList { get; set; }

}

Having this pre-configured sure makes it obvious where to go when extra knowledge is needed about the model. ASP.NET Dynamic Data attributes such as what control should display the data, data types, how to validate fields and actions taken on properties are just a few of the attributes available for use within the metadata class.

Rules

PLINQO Rules! Yes, PLINQO provides several different ways to lay down the law. Rules can be added programmatically or declaratively through attributes. Constraints like property length and required field rules can be enforced. Regular Expression data validation as well as several built in rules including authorization rules are possible with PLINQO. Before any data is saved, the rules are automatically executed against any entities in your change set. If any rules are broken, a BrokenRulesException will be thrown with a list of the rules that were broken and the entity will not be updated.

The PLINQO rule manager generates rules based on the schema and any custom rules can be added to the rules collection. The rules are enforced when any attempt to save changes is made. Custom rules are a snap to add and the AddSharedRules partial method on each entity is the place to add them. Only a few lines of code and a custom rule can be added. Here is a look.

A rule for the minimum length of UserName is added.

static partial void AddSharedRules()
{
    RuleManager.AddShared<User>(new CustomRule<string>("UserName",
        "UserName must be 5 characters.", MinLengthUserName));
}

private static bool MinLengthUserName(string username)
{
    if (String.IsNullOrEmpty(username) || username.Length < 5)
        return false;
    else
        return true;
}

When any rules are broken, no data is updated and a nice list of the rules broken is returned.

using (var context = new TrackerDataContext())
{
    User user = new User();
    context.User.InsertOnSubmit(user);
    context.SubmitChanges();
}

Rules can be added declaratively as well by taking advantage of the metadata class mentioned previously. PLINQO supports the standard rules in the System.ComponentModel.DataAnnotations assembly as well as the rules defined by PLINQO

As you can see there are many ways to enforce that the data that gets saved is data that follows the rules.

Many to many Relationships

Almost every project requires the use of many to many relationships and LINQ to SQL does not support it. As you probably guessed, PLINQO does! Let's go through a quick sample of a many to many scencario in PLINQO. Below is a diagram of a many to many relationship between the User and Role entities.

The UserRole table before any many to many code is executed.

The code here adds a User to a Role.

using (var context = new TrackerDataContext())
{
    User u = context.User.GetByKey(1);
    Role r = context.Role.GetByKey(1);
    u.RoleList.Add(r);
    context.SubmitChanges();
}

The result in the database after the code is executed.

Auditing

What data changed, what was it and what is it now! Questions that are asked all the time. When these questions are asked, PLINQO is ready to help provide the answer. When AuditingEnabled is set to true on the data context, PLINQO will capture the change anytime changes are submitted to the database. PLINQO captures only the objects that are changed and only the properties in those objects that were changed everytime changes are submitted to the database. The before and after values are recorded. Context.LastAudit is where this information is held and there is a ToXml() method that makes it easy to turn the AuditLog into XML for easy storage. Here, we change the User and the Task objects, submit the changes and the XML for the audit is displayed. PLINQO auditing will log Inserts, Deletes and Updates and eliminates the work normally required when the decision is made that auditing of data changes is needed.

using (var context = new TrackerDataContext())
{
    Priority p = new Priority();
    p.Name = "High!";
    context.Priority.InsertOnSubmit(p);

    Task t = context.Task.GetByKey(1);
    t.Details = "Startup Counterstrike.  Used PLINQO.  Project is done";

    context.SubmitChanges();
    AuditLog audit = context.LastAudit;
}

<audit xmlns:xsi=<a href="%22http://www.w3.org/2001/XMLSchema-instance%22">http://www.w3.org/2001/XMLSchema-instance</a>
   xmlns:xsd=<a href="%22http://www.w3.org/2001/XMLSchema%22">http://www.w3.org/2001/XMLSchema</a>
   xmlns="http://schemas.codesmithtools.com/datacontext/audit/1.0">
  <entity action="Insert" type="Tracker.Data.Priority">
    <key name="Id" type="System.Int32">
      <value xsi:type="xsd:int">0</value>
    </key>
    <property name="Id" type="System.Int32">
      <current xsi:type="xsd:int">0</current>
    </property>
    <property name="Name" type="System.String">
      <current xsi:type="xsd:string">High!</current>
    </property>
    <property name="Order" type="System.Int32">
      <current xsi:type="xsd:int">0</current>
    </property>
  </entity>
  <entity action="Update" type="Tracker.Data.Task">
    <key name="Id" type="System.Int32">
      <value xsi:type="xsd:int">1</value>
    </key>
    <property name="Details" type="System.String">
      <current xsi:type="xsd:string">
          Startup CounterStrike.  Used PLINQO.  Project is done</current>
      <original xsi:type="xsd:string">
          Work overtime to get project started</original>
    </property>
  </entity>
</audit>

Data Services and WCF

Data Services

LINQ to SQL does not support Data Services by default. However, all it takes to use data services with PLINQO is setting the IncludeDataServices property on the Entities template to true and let PLINQO take care of setting up your project to support Data Services.

WCF

PLINQO takes care of all the tedious work of setting up the DataContract and DataMember attributes for Windows Communication Foundation (WCF). The IncludeDataContract option on the Entities template tells PLINQO to generate Data Contract and Data Member Attributes and makes using Windows Communication Foundation (WCF) with PLINQO painless by doing all the leg work.

Performance Improvements

With PLINQO, calls to the database will be greatly reduced. Here are a couple of ways:

Batch Updates and Deletes

Deleting and updating are major parts of any application. PLINQO now provides an optimal solution whether working with one entity LINQ to SQL requires a lot of work to delete or update an entity and no support to delete or update multiple entities at one time. For single deletes, the object must be retrieved and populated in order to delete and update. Two calls to the database to delete or update an entity when you already have the information you need! PLINQO eliminates the first call by sending deletes and updates directly eliminating the need to do the extra work. Thus, performance is easily improved, plus time is saved with fewer lines of code.

Multiple updates and deletes are also a downside for most ORMs and LINQ to SQL is no different. When deleting or updating multiple entities without PLINQO, multiple statements are issued when a single SQL statement will get the job done a lot more efficiently. PLINQO offers the ability to create multiple updates and deletes based on a filter. Below is a look at the different features available for deleting and updating.

Delete

//Manager classes delete method
context.Manager.User.Delete(1);
//Query extension delete method
context.User.Delete(1);

//works for managers and queries
context.User.Delete(u => u.FirstName == "firstname");

IQueryable<Task> tasks = context.Task.Where(t => t.StatusId == 2);
context.Task.Delete<Task>(tasks);

Update

context.Task.Update(t => t.StatusId == 1, t2 => new Task {StatusId = 2});

IQueryable<User> users = context.User.Where(u => u.FirstName == "firstname");
context.User.Update(users, u => new User {FirstName = "newfirstname"});

As you can see, there is no need for SubmitChanges(). The actions are taken immediately and much better performance will be seen thanks to PLINQO's batch deleting and updating capabilities.

Stored Procedures with Multiple Result Sets

There are times when it is more efficient to batch queries into one stored procedure in order to optimize the number of round trips to the database. During generation, PLINQO recognizes all stored procedures that return multiple results and provides a nice way to handle the results.

Here is a look at how this can be done. The following stored procedure is created.

Create Procedure [dbo].[ReturnMultiplResultSets]
As
Select * From [User]
Select * From [Task]

Now we have a stored procedure returning multiple result sets and here is how PLINQO will handle it.

IMultipleResults results = context.ReturnMultiplResultSets();
List<User> users = results.GetResult<User>().ToList();
List<Task> tasks = results.GetResult<Task>().ToList();

It doesn't get any easier than this.

Batch Queries

When heading to the video game store. money in hand, ready to purchase Call of Duty, Madden 09 and Tiger Woods 09, I am sure you do not make 3 trips to the store to buy the games, but take care of it in one purchase. Making three trips would would be incredibly inefficient. So, why is this deemed acceptable by LINQ to SQL when pulling back data from the database. PLINQO finds this unaccetpable! PLINQO supports batching queries making it possible to greatly optimize the time it takes completing operations. ExecuteQuery on the data context makes this possible. Here is an example that batches up Select * From User and Select * From Task into one trip to the database.

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();

Easier Serialization

Serialization of entities has had developers running in circles from the early days of LINQ to SQL until now. PLINQO eliminates this frustration by eliminating the possibility of circular references and taking advantage of WCF datacontract serialization. Here is a sample of serializing an object using PLINQO.

Task task = context.Task.GetByKey(1);
string xml = task.ToXml();

Conclusion

PLINQO is LINQ to SQL, just better! Check out PLINQO today let us know what you think.