LINQ to SQL supports binding to common controls, such as grid controls. Specifically, LINQ to SQL defines the basic patterns for binding to a data grid and handling master-detail binding, both with regard to display and updating.
LINQ to SQL translates LINQ queries to SQL for execution on a database. The results are strongly typed
IEnumerable. Because these objects are ordinary common language runtime (CLR) objects, ordinary object data binding can be used to display the results. On the other hand, change operations (inserts, updates, and deletes) require additional steps.
Implicitly binding to Windows Forms controls is accomplished by implementing IListSource. Data sources generic Table<TEntity> (
Table<T> in C# or
Table(Of T) in Visual Basic) and generic
DataQuery have been updated to implement IListSource. User interface (UI) data-binding engines (Windows Forms and Windows Presentation Foundation) both test whether their data source implements IListSource. Therefore, writing a direct affectation of a query to a data source of a control implicitly calls LINQ to SQL collection generation, as in the following example:
DataGrid dataGrid1 = new DataGrid(); DataGrid dataGrid2 = new DataGrid(); DataGrid dataGrid3 = new DataGrid(); var custQuery = from cust in db.Customers select cust; dataGrid1.DataSource = custQuery; dataGrid2.DataSource = custQuery; dataGrid2.DataMember = "Orders"; BindingSource bs = new BindingSource(); bs.DataSource = custQuery; dataGrid3.DataSource = bs;
Dim dataGrid1 As New DataGrid() Dim dataGrid2 As New DataGrid() Dim dataGrid3 As New DataGrid() Dim custQuery = _ From cust In db.Customers _ Select cust dataGrid1.DataSource = custQuery dataGrid2.DataSource = custQuery dataGrid2.DataMember = "Orders" Dim bs = _ New BindingSource() bs.DataSource = custQuery dataGrid3.DataSource = bs
The same occurs with Windows Presentation Foundation:
ListView listView1 = new ListView(); var custQuery2 = from cust in db.Customers select cust; ListViewItem ItemsSource = new ListViewItem(); ItemsSource = (ListViewItem)custQuery2;
Dim listView1 As New ListView() Dim custQuery2 = _ From cust In db.Customers _ Select cust Dim ItemsSource As New ListViewItem ItemsSource = custQuery2
LINQ to SQL implements IListSource in two locations:
The data source is a Table<TEntity>: LINQ to SQL browses the table to fill a
DataBindingListcollection that keeps a reference on the table.
The data source is an IQueryable<T>. There are two scenarios:
If LINQ to SQL cannot find the underlying Table<TEntity>, the source does not allow for edition (for example,
groupby). LINQ to SQL browses the query to fill a generic
SortableBindingList, which is a simple BindingList<T> that implements the sorting feature for T entities for a given property.
For many features described earlier in this document, BindingList<T> has been specialized to some different classes. These classes are generic
SortableBindingList and generic
DataBindingList. Both are declared as internal.
This class inherits from BindingList<T>, and is a sortable version of BindingList<T>. Sorting is an in-memory solution and never contacts the database itself. BindingList<T> implements IBindingList but does not support sorting by default. However, BindingList<T> implements IBindingList with virtual core methods. You can easily override these methods. Generic
SortableBindingList overrides SupportsSortingCore, SortPropertyCore, SortDirectionCore, and ApplySortCore.
ApplySortCore is called by ApplySort and sorts the list of T items for a given property.
An exception is raised if the property does not belong to T.
To achieve sorting, LINQ to SQL creates a generic
SortableBindingList.PropertyComparer class that inherits from generic IComparer.Compare and implements a default comparer for a given type T, a
PropertyDescriptor, and a direction. This class dynamically creates a
Comparer of T where T is the
PropertyType of the
PropertyDescriptor. Then, the default comparer is retrieved from the static generic
Comparer. A default instance is obtained by using reflection.
SortableBindingList is also the base class for
SortableBindingList offers two virtual methods for suspending or resuming items add/remove tracking. Those two methods can be used for base features such as sorting, but will really be implemented by upper classes like generic
This class inherits from generic
DataBindingList keeps a reference on the underlying generic
Table of the generic
IQueryable used for the initial filling of the collection. Generic
DatabindingList adds tracking for item add/remove to the collection by overriding
RemoveItem(). It also implements the abstract suspend/resume tracking feature to make tracking conditional. This feature makes generic
DataBindingList take advantage of all the polymorphic usage of the tracking feature of the parent classes.
Binding to EntitySets
EntitySet is a special case because
EntitySet is already a collection that implements IBindingList. LINQ to SQL adds sorting and canceling (ICancelAddNew) support. An
EntitySet class uses an internal list to store entities. This list is a low-level collection based on a generic array, the generic
Adding a Sorting Feature
Arrays offer a sort method (
Array.Sort()) that you can be used with a
Comparer of T. LINQ to SQL uses the generic
SortableBindingList.PropertyComparer class described earlier in this topic to obtain this
Comparer for the property and the direction to be sorted on. An
ApplySort method is added to generic
ItemList to call this feature.
EntitySet side, you now have to declare sorting support:
When you use a System.Windows.Forms.BindingSource and bind an EntitySet<TEntity> to the System.Windows.Forms.BindingSource.DataSource, you must call EntitySet<TEntity>.GetNewBindingList to update BindingSource.List.
If you use a System.Windows.Forms.BindingSource and set the BindingSource.DataMember property and set BindingSource.DataSource to a class that has a property named in the BindingSource.DataMember that exposes the EntitySet<TEntity>, you don’t have to call EntitySet<TEntity>.GetNewBindingList to update the BindingSource.List but you lose Sorting capability.
LINQ to SQL queries implement GetList. When the Windows Forms BindingSource class meets this interface, it calls GetList() threes time for a single connection. To work around this situation, LINQ to SQL implements a cache per instance to store and always return the same generated collection.
IBindingList defines an AddNew method that is used by controls to create a new item from a bound collection. The
DataGridView control shows this feature very well when the last visible row contains a star in its header. The star shows you that you can add a new item.
In addition to this feature, a collection can also implement ICancelAddNew. This feature allows for the controls to cancel or validate that the new edited item has been validated or not.
ICancelAddNew is implemented in all LINQ to SQL databound collections (generic
SortableBindingList and generic
EntitySet). In both implementations the code performs as follows:
Lets items be inserted and then removed from the collection.
Does not track changes as long as the UI does not commit the edition.
Does not track changes as long as the edition is canceled (CancelNew).
Allows tracking when the edition is committed (EndNew).
Lets the collection behave normally if the new item does not come from AddNew.
This section calls out several items that might help troubleshoot your LINQ to SQL data binding applications.
You must use properties; using only fields is not sufficient. Windows Forms require this usage.
timestampdatabase types map to byte array. Because
ToString()is not supported in this scenario, these objects cannot be displayed.
A class member mapped to a primary key has a setter, but LINQ to SQL does not support object identity change. Therefore, the primary/unique key that is used in mapping cannot be updated in the database. A change in the grid causes an exception when you call SubmitChanges.
If an entity is bound in two separate grids (for example, one master and another detail), a
Deletein the master grid is not propagated to the detail grid.