Playing with LINQ For SQL in a Windows Forms Application
Posted 18 September 2007, 14:45 | by Ben Duguid | Perma-link
Don’t look now, but I think this might actually be a technical posting!
So, I’ve finally had an excuse to start playing around with LINQ – some data clean up tasks that I could have done by hand, but it’s always more fun to throw together a little app or two than spend your life in Enterprise Manager (or SQL Studio).
The key issues I came up against were with DataGrids, and picking and populating columns.
A fairly simple setup for editing the Taxonomy (remember, this is a developer application, so it works for me):
We have a ComboBox providing the Category, and then the DataGrid allows us to edit the Subjects. The “Subject ID” is an auto-generated GUID that is occasionally useful for the developer to know, so I wanted to display it, the data model looks like this:
As you can see there’s an additional column in the database that I’m not interested in for the DataGrid: “TAX_CATEGORY_ID” this is always powered by the Category selected in the combo box, so I didn’t want to have to set it every time.
Part 1: Designer Support for LINQ Database Models (DataContext)
When wiring up the DataGrid, I initially tried the following:
editSubjects.DataSource = from ts in db.TaxonomySubjects
where ts.TAX_CATEGORY_ID == (Guid)chooseCategory.SelectedValue
orderby ts.SORT_ORDER
select new { ts.TAX_SUBJECT_ID, ts.SORT_ORDER, ts.DISPLAY_NAME, ts.METADATA_NAME };
This almost worked – I had the columns I wanted, but I couldn’t edit the values at all – it turns out that if you use an anonymous type you lose the ability to edit it – the DataSource doesn’t know how to safely update the object, so disables editing.
On top of that, you have no way to change the column properties of the DataGrid in the designer.
I’d read about the ASP.Net LinqDataSource control, and so (naively) assumed there’d be a similar one for WinForms, but apparently not, and after hunting around and reading the readme of the samples that come with VS 2008 (namely the WinFormsDataBinding sample), I found the follow “solution”:
- Add a BindingSource to your form
-
In the .Designer.cs class, add the following in the InitializeComponent() method:
this.taxonomySubjectsBinding.DataSource = typeof(MetadataManager.TaxonomySubject);
Replacing the binding name with your binding name, and the value intypeof()with the value from your “Linq To Sql” classes -
You can now assign that BindingSource to your DataGrid and get access to the column builder:
As you can just about see, the DataGrid doesn’t display the TAX_CATEGORY_ID column although it is there, and I’ve been able to supply nice names for the columns.
My data source wire up now looks like this:
editSubjects.DataSource = from ts in db.TaxonomySubjects
where ts.TAX_CATEGORY_ID == (Guid)chooseCategory.SelectedValue
orderby ts.SORT_ORDER
select ts
Which is just taking all the TaxonomySubject objects that match the query, and the DataSource is then filtering their properties into the correct columns without me having to worry about anything else.
Part 2: Populating hidden columns/Auto Generated Ids in Linq DataContexts
Having successfully cleaned up my DataGrid columns, I now had two remaining problems to solve around adding a new record:
- Generating the new GUID for the subject.
- Adding the TAX_CATEGORY_ID value to the hidden column.
In the database, the TAX_SUBJECT_ID field is defined as:
[TAX_SUBJECT_ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TAX_SUBJECTS_TAX_SUBJ_ID]
DEFAULT (newid()),
So, it’s the primary key column, with a default value of a new GUID. This is where our problems begin.
LINQ understands that SQL can auto-generate the values for columns on a database, provided:
Primary keys that are designated IsDbGenerated=true should also have a DBType with the IDENTITY modifier.
Note that my column here is not marked as an Identity column – to use the IsDbGenerated property, anything that goes in that column must be an integer.
I noticed that my DataContext class contained the following methods in a region called “Extensibility Method Definitions”:
partial void InsertTaxonomyCategory(TaxonomyCategory instance);
partial void UpdateTaxonomyCategory(TaxonomyCategory instance);
partial void DeleteTaxonomyCategory(TaxonomyCategory instance);
partial void InsertTaxonomySubject(TaxonomySubject instance);
partial void UpdateTaxonomySubject(TaxonomySubject instance);
partial void DeleteTaxonomySubject(TaxonomySubject instance);
So, let’s implement some of them in a new class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MetadataManager {
public partial class MetadataDatabaseDataContext {
partial void InsertTaxonomySubject(TaxonomySubject instance) {
if (Guid.Empty == instance.TAX_SUBJECT_ID) {
instance.TAX_SUBJECT_ID = Guid.NewGuid();
}
this.ExecuteDynamicInsert(instance);
}
}
}
A really key thing to note here – if you create these methods, even as empty methods, you must call this.ExecuteDynamicInsert(instance); otherwise any changes you try to submit will be lost.
Basically, before the changes are inserted into the database, this method fires for each one, and I’m creating a new GUID if the column is empty.
When adding just one new row this seems to work ok, but as soon as I try and add more rows, or two at a time, I get exceptions like:
"Cannot add an entity with a key that is already in use."
Hardly ideal – the key’s not in use, but I guess there’s something else I’m missing – any thoughts? On top of that, there’s no way to insert the Category ID into the foreign key column in this method.
So the final solution I came up with to resolve this was to hook into the RowEntered event on the DataGrid:
private void editSubjects_RowEnter(object sender, DataGridViewCellEventArgs e) {
int rowIndex = e.RowIndex;
if (null == editSubjects["taxSubjectId", rowIndex].Value) {
editSubjects["taxSubjectId", rowIndex].Value = Guid.NewGuid();
}
if (null == editSubjects["taxCategoryId", rowIndex].Value) {
editSubjects["taxCategoryId", rowIndex].Value = (Guid)chooseCategory.SelectedValue;
}
}
This lets me generate a new GUID for the row, and adds the Category’s ID to the hidden column.
Which feels to me like an awful cludgy hack (but then so does a lot of the LINQ stuff as well – it’s all inline SQL really isn’t it? Isn’t that supposed to be bad?), but then I read the rest of ScottGu’s blog post – to get a custom query hooked up to a LinqDataSource you have to hook into the Selecting event and replace the result property with your new query…
Any thoughts, let me know.
Filed under: .Net, LINQ, WinForms