We love customers at DevExpress, so when one of you guys writes me from the trenches I drop what I am doing and try to get a response out as soon as possible.
Today, while working on a blog post for the ASP.NET Pivot Grid Control’s (XtraPivotGrid) CustomFilterPopupItems event for 10.2 a customer sent me a message through the newsgroups. He wanted to know if it was possible to use LINQ not as a datasource for an XtraGrid but to manipulate data in an XtraGrid. As is customary I said if you send me a trimmed down demo then I will tweak it for you. This approach is mostly to ensure that I understand what the customer is trying to do. In this case I had a pretty good idea of what he was trying to accomplish so I whipped up a solution and some explanatory text that gives one an idea of how to approach a solution using LINQ.
LINQ uses deferred execution. This means LINQ won’t actually touch the data in the query until you start accessing it. A LINQ query itself runs when the data is accessed not at the time (or line) where the query exists unless you touch it in the query, like calling an extension method like First, or Take on the tail end of the query. It is also worth noting that LINQ is for querying not updating data necessarily, so updating something else in a LINQ query requires using side effects.
For the solution I demonstrated the side effects approach in a compiler directive #if and a preferable, more reader friendly solution in the #else condition. Both approaches are explained along with some sidebar discussions about the code in general—see Listing 1.
Listing 1: Touching data in an XtraGrid with a LINQ query.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace QueryXtraGridWithLINQ
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'northwindDataSet.Orders' table.
// You can move, or remove it, as needed.
this.ordersTableAdapter.Fill(this.northwindDataSet.Orders);
}
private void gridControl1_Click(object sender, EventArgs e)
{
#if TOGGLE
// do something with LINQ on side effects but not a good idea
Func<DataRow, DateTime> SideEffects = row =>
{
DateTime newDateTime = Convert.ToDateTime(row["OrderDate"]).AddYears(1);
object[] data = row.ItemArray;
data[3] = newDateTime;
northwindDataSet.Orders.LoadDataRow(data, false);
return newDateTime;
};
// you could try this but because of deferred execution side effects
// won't actually happen until you touch the data
var orderDates = from DataRow row in northwindDataSet.Orders.Rows
select new {OrderID=row["OrderID"], OrderDate=SideEffects(row)};
// this is mumbo jumbo - esoterrorism
orderDates.ToArray();
int rowsUpdated = ordersTableAdapter.Update(northwindDataSet.Orders);
gridControl1.Refresh();
MessageBox.Show(string.Format("rows {0} updated", rowsUpdated));
#else
// a better solution
var orderDates = from DataRow row in northwindDataSet.Orders.Rows
select new {OrderID=row["OrderID"],
OrderDate=Convert.ToDateTime(row["OrderDate"]).AddYears(1),
RowData=row};
foreach(var r in orderDates)
{
object[] data = r.RowData.ItemArray;
// get desired data element to change - this is the least reliable way
data[3] = r.OrderDate;
northwindDataSet.Orders.LoadDataRow(data, false);
}
Validate();
ordersBindingSource.EndEdit();
int rowsUpdated = ordersTableAdapter.Update(northwindDataSet.Orders);
gridControl1.Refresh();
MessageBox.Show(string.Format("rows {0} updated", rowsUpdated));
#endif
}
}
}
In the #if TOGGLE part of the code a generic delegate (Func) and a Lambda Expressions are used to update an arbitrary column’s value, in this instance OrderDate. The DataRow comes in, the new data is set, and data is updated with LoadDataRow. The Lambda Expression returns the new data and time value, so it can be assigned to the anonymous type’s projection value OrderDate in the select clause of the LINQ query. Using data[3] is risky so in general a better approach to identifying the correct column and table should be used. But this approach has bigger problems.
Func SideEffects has side effects, that is, it changes data. Doing unexpected things is generally considered a no no. Worse still the LINQ query (because LINQ uses deferred execution) won’t even run unless we touch it. In this case touching it is accomplished by calling orderDates.ToArray. ToArray actually causes the query to execute. This approach while functional is esoteric to say the least. So, it is possible to use LINQ directly to change data, but I wouldn’t use this approach.
The #else block contains a better approach.
In the #else block LINQ is used to get the data to be changed, but a for each is still used to actually effect the change. This is a better solution because we need to touch the data to execute the query anyway, and in the LINQ query itself updates were not intended. of course, as is shown in the projection—select new—you can update the results in the new, projected data.
Next, the foreach statement iterates over all of the data and updates the OrderDate value. Storing the DataRow in the projection and using LoadDataRow is a nice, convenient way to update the desired rows. If you wanted to update just some rows then use a where clause in the LINQ query to just get the data you wanted. TableAdapter.Update and Refresh will ensure that the database and the GridControl has the new values.
Using the smart tags menu for the ordersBindingSource an Update method was added to the XSD for the solution. Refer to the code attachment for the complete solution.