If you're an Entity Spaces user you might wonder why this is necessary.  Well for the most part, it may not be.  But occasionally I build applications for clients that use SQL Server 2000 databases, not 2005 so I can't use the esDataSource control.

First off, to use this extension method, you must either have the Trial version of Entity Spaces, or be a registered user.  If you want to find out more about Entity Spaces, click here.

What I really hated having to do if I wanted or needed a DataSet, was to generate my complex SQL statement, and paste it into my Custom Collections partial class in order to return a DataSet.  If it was a simple, single table query, that really isn't a problem.  But many times I have a complex query with many table joins in order to present the data in a GridView control.

Such as the "GetProductsAll()" method.

So why not just load up the Entity Spaces collections and send it to the GridView control.  Because it doesn't support Sorting and Paging inherently.

You will get the error:

The data source 'sourceProducts' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet.

This is why the good folks at Entity Spaces created the esDataSource control, but again it's only good for SQL Server 2005, not 2000.

So in order to get around this little seemingly insignificant roadblock, I created a reusable Extension Method for the Entity Spaces Collection base class that will return a DataSet.  And so far, I haven't had any issues with it.  (That sound is me knocking on wood.)

Enter the DataSet Extension Method

If you don't know what an Extension Method is and what it's good for, Scott Guthrie has a nice blog example.

This is what the extension method looks like.  It's a static method (in a static class, a requirement for extension methods) and the first parameter is the "this" keyword which is followed by the Type this extension method will process.  In this case an esEntityCollection.  The isQuery parameter tells the method whether the incoming collection is based on an Entity Spaces Query Collection, or simply a coll.LoadAll().

It's fairly simple as long as you figure out the Entity Spaces Object Model.  The extension method takes a single boolean parameter, whether the incoming collection is based on an Entity Spaces Query Collection, or a simple single entity collection.  NOTE: there is probably an easier and possibly a better way of handling the columns discovery, but this is how I did it.

The method first checks whether the collection is based on a Query Collection, or a single entity collection.  Then, if it's a single entity collection, it will get the first entity from the collection and then loop through all the columns and add them to the DataTable object for the column references.

A Query Collection (as I call it) would be something like this:

            CustomersQuery customers = new CustomersQuery("c");
            OrdersQuery orders = new OrdersQuery("o");
            OrderDetailsQuery details = new OrderDetailsQuery("d");
            ProductsQuery products = new ProductsQuery("p");

            customers.Select(orders.OrderID, details.UnitPrice, details.Quantity, products.ProductName);
            customers.InnerJoin(orders).On(customers.CustomerID == orders.CustomerID);
            customers.InnerJoin(details).On(orders.OrderID == details.OrderID);
            customers.InnerJoin(products).On(details.ProductID == products.ProductID);
            customers.Where(customers.CustomerID.Equal(valuesArray[0]));
            customers.Where(orders.OrderID.Equal(valuesArray[1]));

            CustomersCollection customerColl = new CustomersCollection();
            customerColl.Load(customers);

A single entity collection would be something like this:

            ProductsCollection prodColl = new ProductsCollection();
            prodColl.LoadAll();

If it's a Query Collection, then (and this is the kludgy part), I created a little GetSelectParams method that actually extracts the SQL statement from the collection using the syntax, coll.es.Query.es.LastQuery.  Then I parse the statement to extract the SELECT part of the statement.  And I add the column names to the DataTable object and return it to the extension method. 

Then I use these columns to generate the values for the columns and plug them into the DataTable.  Then it all gets added to the DataSet object.

 And here's how you can use it.  In this example, I'm using it in a ProductController class (using the infamous Northwind database).

Look how sweet that is!  You simply press the Dot (.) key after the collection and intellisense will show you the ToDataSet(false) extension method.  So in a single line, you are creating a DataSet from an Entity Spaces collection.

I've decorated the GetProducts() method with the DataObjectMethod attribute so it can be easily used with an ObjectDataSource control, which works swimmingly with DataSets!

Add a GridView control to your page, connect it to an ObjectDataSource control, point the control to this class and method and display the page and viola!  (Isn't a Viola an instrument?)

Anyway, it will look like this...

And the great thing about this is that it will fully support the built-in paging and sorting offered by the ObjectDataSource control.

I also ran some other Unit Tests and they all worked for both single entity collections and complex collections.

Here is some of the Unit Tests...

 So that's basically it.  You can find the Visual Studio 2008 solution on my Downloads page.

I hope you find this useful.

PREREQUISITES

• 3.5 .Net Framework
• Entity Spaces Trial or Registered version
• Northwind database

Update!!

If you downloaded this prior to November 30, 2008, you should download this new version.

Fixes:

• The sql string parser now parses out the modified column name, not the original name, in case it was renamed.