LINQ to DataSet – Data Binding

With the introduction of LINQ to DataSet there finally exists a full featured query language for the DataSet. Now your ability to query your data is limited only by CLR, which is no small thing! For an introduction on LINQ to DataSet, please see this post.

This is great new functionality, but for most applications, query is only half of the story. Once you have sliced and diced your data into the form that you want, you need to do something with this data. This might be something as simple as spitting it out to the console, or as complex as binding it to a read/write hierarchical DataGrid. With this wide range of needs comes a wide range of capabilities.

This blog post will talk about how to do data binding with LINQ to DataSet from a high level perspective. Future posts will go into more details on each particular technology, and how to use it in a variety of situations.

Basic data binding

If you simply bind the results of a LINQ to DataSet query to a grid (or some other control), you will see the values from your query results. However, the results will be simple values – you will not get the benefits of the DataSet, such as change tracking, transparent updating when the DataSet is updated, etc. Some features may work some of the time, but your experience will be less than stellar.

Why is this? When a LINQ to DataSet query is executed, the result is an IEnumerable<T>. How data binding will work depends on the type of the enumeration (the T). There are a number of possibilities, each with its own problem. If the query is a projection of something, such as anonymous types, then the results are “torn off” from the source DataTable. This means that when you edit the values, then source never gets updated –in essence no data binding is occurring. If the projection is a DataRow, then you will get tracking for those DataRows, but because you are not bound to the DataTable, you end up not being able to add/delete DataRows!

Using your query results with DataSet – three options

If basic binding does not always work, what are the other options?

DataView

There already exists a way to do data binding on the DataSet – the DataView. The DataView provides a dynamic view of the data in your DataTable, with a filter and sort order. In versions prior to Visual Studio 2008, this filter used the string expression language, and the sorting was performed on a column. For details on this functionality, see the DataView documentation.

With Visual Studio 2008, you can now use the power of LINQ to DataSet to create more expressive filters and sorts with your DataView, and continue to get all the benefits of the DataView.

var query = from order in adventureWorksDS1.SalesOrderHeader

            where order.IsCreditCardIDNull() == false

            orderby order.OrderDate descending

            select order;

 

dataGridView1.DataSource = query.AsDataView();

However, this ability to create a DataView from a LINQ to DataSet query is not the end all be all of data binding. There are a strong set of restrictions as to what kind of queries can be converted into DataViews. I will go into these details, as well as ways to work around some of these restrictions, in the future LinqDataView post.

CopyToDataTable

LINQ has a whole host of operators that you can use to write queries. These queries can become quite complex, with grouping, projections, etc. Sometimes you need to bind to the results of these complex queries, but you still want the benefits of working with the DataSet. Features like change tracking are what makes the DataSet so useful. So if you want to use DataSets for binding, but have a more complex query than can be used with DataView, you can use CopyToDataTable.

var query1 = from product in adventureWorksDS1.Product

            where !product.IsColorNull() && product.Color == "Black"

            select product;

 

var query2 = from product in adventureWorksDS1.Product

             from subcat in adventureWorksDS1.ProductSubcategory

             where product.ProductSubcategoryID == subcat.ProductSubcategoryID

             where subcat.ProductCategoryRow.Name == "Bikes"

             select product;

 

var finalQuery = query1.Union(query2);

 

DataTable bindingTable = finalQuery.CopyToDataTable();

 

dataGridView1.DataSource = bindingTable;

CopyToDataTable’s only restriction is that you must project DataRows from the outer Select statement. This is because the way that it works is by loading the DataRows from your LINQ to DataSet into a DataTable. Once the query has been moved into the DataTable, you can bind it, update it, etc. In a future post, I’ll go over the exact scenarios where you want to use this approach.

BYOC

And finally, there is the old standby – Bring Your Own Code. Ultimately, a LINQ to DataSet query is an IEnumerable<T>, and so if you have an idea of how you need to map from a T into your DataTable, you can simply copy the results in!

var query = from employee in adventureWorksDS1.Employee

            where employee.BirthDate.DayOfYear == DateTime.Now.DayOfYear

            where employee.IsManagerIDNull() == false

            select new { ManagerEmail = employee.EmployeeRowParent.ContactRow.EmailAddress, employee.ContactRow.EmailAddress };

 

foreach (var result in query)

{

    bindingTable.Rows.Add(new object[] { result.ManagerEmail, result.EmailAddress });

}

There are some other neat things that can be done with a little bit of code, and I will show you some of them in a future post, including a nice little code snippet that will you to copy any IEnumerable<T> into a DataTable!

DataView

LINQ in general and LINQ to DataSet in particular are opening up a whole new world of query, giving you the ability to execute some really powerful queries. However, because they are so flexible, you lose some of the abilities to put together an end-to-end data binding story out the box. Part of the reason for this is that the flexibility of LINQ in query needs to be paired with flexibility in data binding.

This post gives you a little taste of how you can bind LINQ to DataSet query results, and I’ll be digging into more detail soon. Until then, please let me know what kind of questions you’d like to see answered, and I will do my best to answer them.

Thanks,

Erick

Comments

• Anonymous
  August 27, 2007
  Excellent post Erick. I look forward to seeing more specifics about the LinqDataView.

• Anonymous
  September 19, 2007
  Thanks a lot for the post. It was really great. I have started to learn and read about data binding and forms in .Net 2.0. I also jumped with excitement to learn about Linq and specially Linq-to-Dataset and the only reason I was hoping for great and seamless integration of the latter with forms and data binding. I will be waiting on more of your posts to around this topic.

• Anonymous
  October 05, 2007
  It's long overview, but I finally was able to finish a few more posts on this series. Let me know if

• Anonymous
  October 12, 2007
  Interested in hearing more? Check out this great post by Erick Thompson our DataSet PM on his blog .

• Anonymous
  October 12, 2007
  did you do any speed tests between the various ways to see which would be most optmal?

• Anonymous
  October 18, 2007
  Time for another weekly roundup of news that focuses on .NET, agile and general development content:

• Anonymous
  January 16, 2008
  CopyToDataTable or ToDataTable does not exist in the released version of VB9!!!

• Anonymous
  July 30, 2008
  This page also gives a useful alternative way to adapt LINQ query results to a DataTable: http://www.c-sharpcorner.com/UploadFile/VIMAL.LAKHERA/LINQResultsetToDatatable06242008042629AM/LINQResultsetToDatatable.aspx sample copied here: public static DataTable LINQToDataTable<T>(IEnumerable<T> varlist) {     DataTable dtReturn = new DataTable();     // column names     PropertyInfo[] oProps = null;     if (varlist == null) return dtReturn;     foreach (T rec in varlist)     {          // Use reflection to get property names, to create table, Only first time, others          will follow          if (oProps == null)          {               oProps = ((Type)rec.GetType()).GetProperties();               foreach (PropertyInfo pi in oProps)               {                    Type colType = pi.PropertyType;                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()                          ==typeof(Nullable<>)))                     {                         colType = colType.GetGenericArguments()[0];                     }                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));               }          }          DataRow dr = dtReturn.NewRow();          foreach (PropertyInfo pi in oProps)          {               dr[pi.Name] = pi.GetValue(rec, null) == null ?DBNull.Value :pi.GetValue               (rec,null);          }          dtReturn.Rows.Add(dr);     }     return dtReturn; }

---

Example: To use this method, just use the following code sample:

var vrCountry = from country in objEmpDataContext.CountryMaster                        select new {country.CountryID,country.CountryName}; DataTable dt = LINQToDataTable(vrCountry);