locked
LINQ, Lookups, ForeignKeyReferenceAlreadyHasValueException

    Question

  • I have a number of database tables in a SQL Server database linked with referential integrity.
    My application is written in C# using Visual Studio 2008.
    The application has a form with a DataGridView showing data in a table, and has a number of columns on that grid that are ComboBoxes with data in the Combo coming from lookup tables.

    In the days of Pre-LINQ implementing lookups was straightforward. You just EditColumn, set ColumnType = DataGridViewComboBoxColumn, then set DataSource, DisplayMember and ValueMember on the column, etc.

    When using LINQ, unless I am missing something, using lookups in this manner is a bit of a nightmare.
                                                                                                        
    After a period of confusion with the ForeignKeyReferenceAlreadyHasValueException exception, and searching the Internet without success, I eventually found out how implement lookups from an example that comes with VS2008.
    i.e. the "LinqSamples\WinFormsDataBinding" example in  "<Visual Studio 2008>\Samples\1033\CSharpSamples.zip".

    Although the solution works I don't think it is particularly satisfactory. In my opinion it is too complex for something that you often want to do.
    i.e. based on the example, to implement lookups you do the following:
    --------------------------------------------
    1) when you populate the combo dataSource in code:
       a) use a var
       b) use the ToList()

    2) put code in the DataGrid_CellParsing event to convert the string to an object. (Messy)
      
    3)
    in the form designer, edit the columns on the grid and for the link field:
         set ColumnType = DataGridViewComboBoxColumn
         set DataPropertyName = <the reference property, not the ID or code>
         set DataSource = <your datasource>
         set DisplayMember = (none)
         set ValueMember = (none)

    4) you need to over-ride the ToString() method on the table type because it us used to display the text in the combo. If you don't over-ride it you get the classname.
    --------------------------------------------

    The problems with this method:
    a) it is overly complex.
    b) it is inflexible. I don't like having to over-ride the ToString() method on the table data type.
    c) sorting on the lookup column does not work.
    d) if you set "AutoSize Mode = AllCells" of the column you get an error saying value in gridCombo is not valid.

    Finally my question... Is there an easier way to do lookup columns in a DataGridView when you are using LINQ with database tables with referential integrity? If not, is Microsoft going to enhance the interaction between LINQ and DataGridView and DataGridViewComboBoxColumn so that doing lookups are far more straightforward and flexible?

    Monday, December 08, 2008 11:08 AM