Sunday, October 24, 2010

Adding MongoDB samus / mongodb-csharp to the Linq Comparison

Last month I compared several Linq providers. Lately I’ve been playing with MongoDB. MongoDB is a Document (read NoSQL) database that runs on windows and has a .NET provider.

To do this, I am going to set up an environment with the same data as I used in the previous examples (the infamous Northwind database from SQL Server).

Create the Data

To recreate the data from Northwind, I wrote the following query to create insert statements.

SELECT 'db.Product.insert({' 
    + 'ProductID: ' + CAST(p.ProductID AS VARCHAR(10)) + ', '
    + 'ProductName: "' + p.ProductName + '", '
    + 'SupplierID: ' + CAST(p.SupplierID AS VARCHAR(10)) + ', '
    + 'CategoryID: ' + CAST(p.CategoryID AS VARCHAR(10)) + ', '
    + 'QuantityPerUnit: "' + p.QuantityPerUnit + '", '
    + 'UnitPrice: ' + CAST(p.UnitPrice AS VARCHAR(10)) + ', '
    + 'UnitsInStock: ' + CAST(p.UnitsInStock AS VARCHAR(10)) + ', '
    + 'UnitsOnOrder: ' + CAST(p.UnitsOnOrder AS VARCHAR(10)) + ', '
    + 'ReorderLevel: ' + CAST(p.ReorderLevel AS VARCHAR(10)) + ', '
    + 'Discontinued: ' + CASE WHEN p.Discontinued = 1 THEN '"true"' ELSE '"false"' END + ', '
    + 'CategoryName: "' + c.CategoryName + '"'
    + '})'
FROM Northwind.dbo.Products p
    JOIN Northwind.dbo.Categories c ON p.CategoryID = c.CategoryID
ORDER BY p.ProductID

I run the above query against a Northwind Database that I have lying around on my home computer and I pasted the result set into a Mongo Shell window.

Notice that I added CategoryName to Product. From a relational point of view, this is criminal. However, Mongo is a DOCUMENT database and repeating data isn’t quite sin; I am also leaving this in the CategoryName because repeating data is in the spirit of document databases as I understand them. (Besides the version of MongoDB.DLL I’m using doesn’t appear to support joins very well.)

The POCO Class

To run straightforward Linq queries using MongoDB.DLL, I need a POCO class to hydrate the data into. Since MongoDB is a document database, the data can be unstructured, but to work in Linq, structured data makes things easier.

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int SupplierID { get; set; }
    public int CategoryID { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal UnitPrice { get; set; }
    public int UnitsInStock { get; set; }
    public int UnitsOnOrder { get; set; }
    public int ReorderLevel { get; set; }
    public bool Discontinued { get; set; }
    public string CategoryName { get; set; }
}

The Code

Now that I have copied the data that I need to “simulate” the Northwind Product and Category data into a MongoDB collection, I can use this code to do the same thing as I did in the last post:

public static IMongoDatabase GetContext()
{
    var mongo = new Mongo();
    mongo.Connect();
    return mongo.GetDatabase("Northwind");
}

public static void DoDemo()
{
    var mdb = GetContext();
    var list = from p in mdb.GetCollection().Linq()
               where p.CategoryName == "Seafood"
               orderby p.ProductName
               select new
               {
                   p.ProductID,
                   p.ProductName,
                   p.UnitPrice,
                   p.CategoryName
               };

    Console.WriteLine("MongoDB Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

GetContext() is straight forward: You new-up a Mongo, connect and get a database.

The most interesting statement is mdb.GetCollection().Linq(). The GetCollection method links the content with the name of T to an IMongoCollection of Ts. LinqExtensions.Linq() is an extension method that converts the IMongoCollection to IQueryable.

Last month I compared several Linq providers. Lately I’ve been playing with MongoDB. MongoDB is a Document (read NoSQL) database that runs on windows and has a .NET provider.

To do this, I am going to set up an environment with the same data as I used in the previous examples (the infamous Northwind database from SQL Server).

Create the Data

To recreate the data from Northwind, I wrote the following query to create insert statements.

SELECT 'db.Product.insert({' 
    + 'ProductID: ' + CAST(p.ProductID AS VARCHAR(10)) + ', '
    + 'ProductName: "' + p.ProductName + '", '
    + 'SupplierID: ' + CAST(p.SupplierID AS VARCHAR(10)) + ', '
    + 'CategoryID: ' + CAST(p.CategoryID AS VARCHAR(10)) + ', '
    + 'QuantityPerUnit: "' + p.QuantityPerUnit + '", '
    + 'UnitPrice: ' + CAST(p.UnitPrice AS VARCHAR(10)) + ', '
    + 'UnitsInStock: ' + CAST(p.UnitsInStock AS VARCHAR(10)) + ', '
    + 'UnitsOnOrder: ' + CAST(p.UnitsOnOrder AS VARCHAR(10)) + ', '
    + 'ReorderLevel: ' + CAST(p.ReorderLevel AS VARCHAR(10)) + ', '
    + 'Discontinued: ' + CASE WHEN p.Discontinued = 1 THEN '"true"' ELSE '"false"' END + ', '
    + 'CategoryName: "' + c.CategoryName + '"'
    + '})'
FROM Northwind.dbo.Products p
    JOIN Northwind.dbo.Categories c ON p.CategoryID = c.CategoryID
ORDER BY p.ProductID

I run the above query against a Northwind Database that I have lying around on my home computer and I pasted the result set into a Mongo Shell window.

Notice that I added CategoryName to Product. From a relational point of view, this is criminal. However, Mongo is a DOCUMENT database and repeating data isn’t quite sin; I am also leaving this in the CategoryName because repeating data is in the spirit of document databases as I understand them. (Besides the version of MongoDB.DLL I’m using doesn’t appear to support joins very well.)

The POCO Class

To run straightforward Linq queries using MongoDB.DLL, I need a POCO class to hydrate the data into. Since MongoDB is a document database, the data can be unstructured, but to work in Linq, structured data makes things easier.

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int SupplierID { get; set; }
    public int CategoryID { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal UnitPrice { get; set; }
    public int UnitsInStock { get; set; }
    public int UnitsOnOrder { get; set; }
    public int ReorderLevel { get; set; }
    public bool Discontinued { get; set; }
    public string CategoryName { get; set; }
}

The Code

Now that I have copied the data that I need to “simulate” the Northwind Product and Category data into a MongoDB collection, I can use this code to do the same thing as I did in the last post:

public static IMongoDatabase GetContext()
{
    var mongo = new Mongo();
    mongo.Connect();
    return mongo.GetDatabase("Northwind");
}

public static void DoDemo()
{
    var mdb = GetContext();
    var list = from p in mdb.GetCollection().Linq()
               where p.CategoryName == "Seafood"
               orderby p.ProductName
               select new
               {
                   p.ProductID,
                   p.ProductName,
                   p.UnitPrice,
                   p.CategoryName
               };

    Console.WriteLine("MongoDB Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

GetContext() is straight forward: You new-up a Mongo, connect and get a database.

The most interesting statement is mdb.GetCollection().Linq(). The GetCollection method links the content with the name of T to an IMongoCollection of Ts. LinqExtensions.Linq() is an extension method that converts the IMongoCollection to IQueryable.

No comments: