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

  1. Jonathan Carter says:

    Excellent post Erick. I look forward to seeing more specifics about the LinqDataView.

  2. Ashraf ElSwify says:

    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.

  3. It’s long overview, but I finally was able to finish a few more posts on this series. Let me know if

  4. Interested in hearing more? Check out this great post by Erick Thompson our DataSet PM on his blog .

  5. speed says:

    did you do any speed tests between the various ways to see which would be most optmal?

  6. Time for another weekly roundup of news that focuses on .NET, agile and general development content:

  7. GMA Bill says:

    CopyToDataTable or ToDataTable does not exist in the released version of VB9!!!

  8. Howie says:

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

Skip to main content