locked
DataGridView + LINQ: foreign key relationship RRS feed

  • Question

  • Is there a simple way to display data from another table (connected with foreign key relationship), without adding extra properties to the OR model?

     

    Suppose I have a table like Product in Northwind with a CategoryID field and a related Category table with CategoryID and CategoryName.  I would like to have a DataGridView on a Windows Form with list of Products and display CategoryName (instead of CategoryID) as a simple TextBoxColumn.

     

    It can apparently be done very simply in ASP by changing boundColumn with TemplateColumn where expression Eval("Category.CategoryName) is evaluated.  See ScottGu's Blog:

    http://weblogs.asp.net/scottgu/archive/2007/07/16/linq-to-sql-part-5-binding-ui-using-the-asp-linqdatasource-control.aspx

     

    OR model and LINQ makes the info in Category table easily accessible, and I am wondering if something as simple can be done on Windows Forms with DataGridView.

    Wednesday, November 28, 2007 8:14 PM

All replies

  • If you make an association between Product & Category in LINQ to SQL data diagram, you have Category property in your Product data class,   When you use product entity as datasource  of datagrid, in your grid you can have a column which is binded to Category property of your Product class,  DataGridView use ToString() method of objects to get  their display string, and ToString() of data classes shows it’s type which looks something like ‘YourNameSpace.Category’, if you add a partial class for category data class and override the ‘ToString()’  and return the Name of category, data grid view shows category name in Category column, actually this is not the best way to doing  this. But it is the easiest one. Better way is to add your custom property to your partial data class which reflects “this.Category.Name” in get accessor and use this column in DataGridView.  

    For Example:


     

    Code Block

    public partial class Category
        {
            public override string ToString()
            {
                return this.Name
            }
        }



    Thursday, November 29, 2007 5:24 AM
  • I just created a sample WinForms app that does what you requested (i.e. a grid with two columns -- Product Name, and Category Name).

     

    I wrote the following query:

     

    var q =

        (from p in db.Products

         select new { p.ProductName, p.Category.CategoryName }

        ).Take(25);

     

    And then used it as the data source:

     

    productBindingSource.DataSource = q;

     

    The trick seemed to be initially setting the productBindingSource.DataSource property through the designer to the Northwind.Product type from the DBML.  But then I just deleted all the columns from the grid (except for ProductName) and added a new one called Category with a DataPropertyName of CategoryName (which maps to the p.Category.CategoryName in the above query).

     

    Anyway, hopefully you get the idea.  I'm not a WinForms guy, although this 2-tier approach with LINQ is nice!  If only my app didn't have to run across the wire.  :-)

     

    -Larry

     

    Thursday, November 29, 2007 1:00 PM
  • Alimardani,

     

    I am familiar with that approach (extending the Category class), but it is not the most flexible approach.  I may want to display 2 different fields from the Category table in 2 different columns.

     

    The approach you describe makes up for shortcomings of the DataGridView - well, not exactly DataGridView controll, but the associated column classes available in the VS 2008 package.  I think there should be a flexible DataGridColumn available that would be able to evaluate any expression I chose.

     

    The need for such a column is more urgent now, since LINQ and the language(s) themselves offer lambda expressions.  So an enhanced column class could pass the value of the column to the (user specified) lambda expression instead of calling ToString function on the value itself.

     

    The way I envision is that the column will have (in addition to DataPropertyName) a property called Expression (or Lambda).  For the Category name the expression would be (in VB.net dialect):

    Function (tbCategory as Category) tbCategory.CategoryName

     

    For SomeOtherField it would be:

    Function (tbCategory as Category) tbCategory.SomeOtherField

     

    Joe

    Thursday, November 29, 2007 9:03 PM
  •  

    Using a custom DataGridView column which can evaluate expressions is very good solution,  but you  need to make  your own custom column.

    To show two or more properties of Category class in DataGridView  you can project needed properties to your Product partial class, and use them in DataGridView, here is the Sample Code:

    Code Block

     public partial class Product
        {
             public string CategoryTitle
            {
                get
                {
                    return this.Category.Title;
                }
            }

            public string CategoryCode
            {
                get
                {
                    return this.Category.Code;
                }
            }
        }

     

     

    Monday, December 3, 2007 9:24 AM
  • Good. However, it still sounds more as a work around than any other thing.

     

    Joe 212,

     

    I'm facing the same problem using the VS Studio 2008 combined with LINQ functionalities, where I need to label foreign tables onto my datagridview. In fact, studying more about this issue, I realized that: DataGridView doesn't work so good displaying relational datas. It happens in order to keep the general rule of this control that is: to manager only one entity. Thus, this control doesn't know how to update related datas nor show that one's.

    I believe will be fixed on further versions. However, untill there, work arounds are the only pratical way to solve this.

     

    Regards,

     

    Rafael Melo

    rafaelmelo007@hotmail.com

    HP Brazil R&D - .NET Developer

     

    Sunday, January 6, 2008 8:29 PM
  •  

    Sure, just create a column tha is a DataGridViewComboBox column.

     

    Then update the DataSource property to point to a datasource associated with the category, like a CategoryDataSource and set the DisplayMember and ValueMember properties to the category name property and the category id property, respectively.

    Sunday, January 6, 2008 9:02 PM
  • I have this problem too...was it solved or is this still a tricky thing to do?

    I would like to view in my datagridview some foreign key values, and have some calculated column as well, what's the best way to do that?

     It can be done with the partial class properties, but how do I update the values then?
    Friday, March 7, 2008 12:11 AM