Comparing different Linq Providers

I’ve been playing with Linq and different "back ends" (not using "providers" since Linq to DataSets probably doesn’t qualify).

In all three examples below, I create the automatic default model with Northwind’s Products and Categories tables. The Linq queries require a join and a filter.

Linq to DataSet

Over the past year or so, I’ve been working fairly heavily what I call Linq To DataSets, you know, Linq on top of classic ADO.NET. I have written several blog entries and made a couple of Code Camp presentations on this subject.

What makes Linq to DataSets is that it uses a disconnected data model (data is eagerly loaded).

public static NorthwindDS CreateContext()
{
    NorthwindDS ds = new NorthwindDS();
    using (var catda = new CategoriesTableAdapter())
    {
        catda.Fill(ds.Categories);
    }
    using (var proda = new ProductsTableAdapter())
    {
        proda.Fill(ds.Products);
    }
    return ds;
}

public static void DoDemo()
{
    var ds = CreateContext();
    var list = from p in ds.Products
             join c in ds.Categories on p.CategoryID equals c.CategoryID
        where c.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            c.CategoryName
        };

        Console.WriteLine("DataSet 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));
        }
    }
}

This one requires real code to load. And everything is loaded. If I need to, I can filter at the DataAdapter level, but that requires fancy Designer work.

Linq to SQL

On my last project, we used Linq to SQL. It is easy to develop and capable, but limited. You will use SQL Server, You will not use Stored Procedures to read and write data (it does have some stored procedure support, but not even to the level of Classic ADO.NET).

public static NorthwindL2SDataContext CreateContext()
{
    return new NorthwindL2SDataContext();
}

public static void DoDemo()
{
    var context = CreateContext();
    var list = from p in context.Products
            join c in context.Categories on p.CategoryID equals c.CategoryID
        where c.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            c.CategoryName
        };

    Console.WriteLine("Linq2Sql 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));
    }
}

Creating the context in Linq to Sql is trivial, just new it up. The Linq is really similar to the Linq to DataSets that I’ve been working with for the last year.

The cool thing is that the data isn’t gotten until I ask for it in the Linq query and only the records I ask for.

Linq to Sql’s model is really close to the table structure of the underlining database. It is less capable than the Entity Framework (below), but it is also less complicated.

Entity Framework

I have also played with the Entity Framework. It is more powerful than the others but more difficult to use than the others.

public static NorthwindEntities CreateContext()
{
    return new NorthwindEntities();
}

public static void DoDemo()
{
    var ds = CreateContext();
    var list = from p in ds.Products
        where p.Categories.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            p.Categories.CategoryName
        };

    Console.WriteLine("EntityFramework 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));
    }
}

Again it is simple to make a new context.

The Entity Framework supports lazy loading and using stored procedures for Insert, Update and Delete operations.

Notice that there is no join in this query, the Category is imbedded in to the Products. The Entity Framework can work with data models that differ from the underlining data store.

The same treatment is applied to Mongo DB