PLINQO Query Extension Updates

The Problem

Unfortunately the previous query extensions, while great at querying by a particular value, did not support simple operations such as getting by a nullable value type (where you know it can be null or just a value).

Example: If you want to query by a nullable column, you had to use a a lambda expression.

context.Product.Where(p => !p.OwnerId.HasValue || p.OwnerId.Value == 5);

Our Solution

Add an overload that for each "By" extension method that accepts a params list of values.

Example: Now you can look for null OR a certain value without using where statement.

context.Product.ByOwnerId(null, 5);

New PLINQO Feature

Here at CodeSmith we love generic solutions. So, forget just supporting ByValueOrNull scenarios, now all "By" query extensions have an overload to support or statements!

Example: Ummm, every single "By" query extension method generated by PLINQO?

context.Task.ByPriority(Priority.High, null, Priority.Medium);
context.Person.ByFirstName("Eric", null, "Shannon", String.Empty);
context.Product.ByName("Scribblenauts", "Bowser's Inside Story").ByRating(Rating.Excellent, Rating.AboveAverage);

Implementation Details

To see how the overloads are implemented you need only take a look at the template, QueryExtension.Generated.cst. If you are curious to see how all of this comes together under the hood, where we build up the parametrized query string and then parse the lambda, this code is located in DynamicQuery.cs

Fun Little FYI Facts: Unfortunately Linq to SQL does not support doing a IEnumerable.Contains against a nullable value type collection to identify null values. Also, when building a string expression, you cant just say where a primitive is equal to a null, that will cause a parsing exception. So once we jumped those hurdles, the rest was relatively easy; just check for null params arrays in the overloads and know when to treat them as a null vs an empty param, and you're all set!

In Conclusion

Linq rocks. PLINQO just keeps getting better. Check out our new nightly builds, or wait for PLINQO 3.2, coming soon to our Google Code repository near you!

PLINQO @ Fort Worth DNUG

The CodeSmith Tools (Shannon and Tom) are continuing their PLINQO presentation tour! Second stop: Fort Worth

If you or any developers that you know will be in the Forth Worth area on October 20th, you should come see us and learn more about PLINQO, the replace and enhance alternative for LINQ to SQL!

Forth Worth DNUG
Topic: PLINQO - Supercharged LINQ To SQL
Speaker: Shannon Davidson & Tom DuPont
Date: October 20th @ 6:00 PM
Where: 610 W. Daggett - Fort Worth, TX 76104

PLINQO @ North Houston DNUG

The CodeSmith Tools (Shannon and Tom) are taking their PLINQO presentation on the road! First stop: North Houston

If you or any developers that you know will be in the Houston area on October 15th, you should come see us and learn more about PLINQO, the replace and enhance alternative for LINQ to SQL!

North Houston Dot Net User Group
Topic: PLINQO - Supercharged LINQ To SQL
Speaker: Shannon Davidson & Tom DuPont
Date: October 15th @ 6:30 PM
Where: Lone Star College - Montgomery

MVC JSON Model Binder Attribute

Here it is folks: the Ultimate Ultimate* ASP.NET MVC JSON Model Binder!

Moving complex data structures from client to server used to be difficult, but not anymore! Just add the JsonBinder attribute to your action parameters, and this Custom ModelBinder will automatically detect the type and parameter name, and deserialize your complex JSON object to the data structure of your choice.

No configuration required, it works every time, it's PFM!**

* Yes, I said Ultimate twice.
** Pure Friendly Magic

JsonBinderAttribute

public class JsonBinderAttribute : CustomModelBinderAttribute

{

    public override IModelBinder GetBinder()

    {

        return new JsonModelBinder();

    }

    public class JsonModelBinder : IModelBinder

    {

        public object BindModel(

            ControllerContext controllerContext, 

            ModelBindingContext bindingContext)

        {

            try

            {

                var json = controllerContext.HttpContext.Request

                           .Params[bindingContext.ModelName];

                if (String.IsNullOrWhitespace(json))

                    return null;

                // Swap this out with whichever Json deserializer you prefer.

                return Newtonsoft.Json.JsonConvert

                       .DeserializeObject(json, bindingContext.ModelType);

            }

            catch

            {

                return null;

            }

        }

    }

}

Controller Action

public class PersonController : Controller

{

    // Note: The JsonBinder attribute has been added to the person parameter.

    [HttpPost]

    public ActionResult Update([JsonBinder]Person person)

    {

        // Both the person and its internal pet object have been populated!

        ViewData["PetName"] = person.Pet.Name;

        return View();

    }

}

Sample Models

public class Person

{

    public string Name { get; set; }

    public int Age { get; set; }

    // Note: This property is not a primitive!

    public DomesticAnimal Pet { get; set; }

}

public class DomesticAnimal

{

    public string Name { get; set; }

    public int Age { get; set; }

    public string Species { get; set; }

}

jQuery Method

function sendToServer() {

    var person = {

        Name : 'Tom',

        Age : 27,

        Pet : {

            Name : 'Taboo',

            Age : 2,

            Species : 'Shiba Inu'

        }

    };

    // {"Name":"Tom","Age":27,Pet:{"Name":"Taboo",Age:2,Species:"Shiba Inu"}}

    var personJson = JSON.stringify(person);

    $.ajax({

        url: 'Person/Update', 

        type: 'POST',

        data: { 

            person: personJson 

        }

    });

}

Enjoy,
Tom

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