none
ordered views or LINQ statement for a master-detail form ? RRS feed

  • Question

  • Hello,

    I dropped two tables (named silos and silosLevels) on the O/R designer.
    These two tables are related by a master-details relation (silos-siloslevels).
    I dropped these two datasourses on a form to get two dataGridViews.
    The two dataGridViews behaved as expected, illustrating the master-detail relation.
    Unfortunately, the data in the two tables are not ordered.

    My question is this:
    How would you suggest me to solve this problem in the best way ?
    Should I create ordered views on the server ?
    (which I don't like as I prefer to keep the database as it is and work in C# only)
    Or would it be possible to do that somewhere within the C# code?

    I already tried a first idea, without full success.
    I simply used this statement in the form load event method to get the silos in an ordered list:

          this.siloBindingSource.DataSource = from s in db.silos orderby s.siloID select s;

    The silo data grid was then properly ordered. No surprise!
    I then tried to do the same for the siloLevels detail grid:

          this.silosLevelsBindingSource.DataSource = from f in db.silosLevels orderby f.week select f;

    Unfortunately, by doing so, the master-detail relation between the two grid is lost.
    No surprise again! The relation between these two data sources in not declared anywhere in the code.
    What would be the cleanest method to get both table ordered without adding new views on the sql server?
    Is that possible?

    Thanks,

    Michel

     

     

    Monday, May 31, 2010 8:41 PM

Answers

  • Hello Michel,

     

    Welcome to LINQ to SQL forum!

     

    If I understand your request correctly, you want to order both the master and detail collection in LINQ to SQL databinding.    I recommend order the master collection via LINQ to SQL query, so the collection is actually ordered at the database side via ORDERBY T-SQL.   Then for the detail collection, we can use BindingSource.Sort property to order the collection at the client side.   Here are some sample codes for your references:

    ===================================================================================

                DataClasses1DataContext db = new DataClasses1DataContext();

                            

                BindingSource bs1 = new BindingSource();

                BindingSource bs2 = new BindingSource();

     

                bs1.DataSource = db.ParentTables.OrderBy(p => p.Name);

                bs2.DataSource = bs1;

                bs2.DataMember = "ChildTables";

                bs2.Sort = "Name";

     

                dataGridView1.DataSource = bs1;

                dataGridView2.DataSource = bs2;

    ===================================================================================

    Both the ParentTable and ChildTable are sorted by Name column.  

     

    The above codes use LINQ to SQL’s lazy loading feature.   When we select each row in the master DataGridView, LINQ to SQL queries the corresponding children entities.   If you want to load all the related entities in one call to the database, please use the DataLoadOptions. 

    ===================================================================================

                DataLoadOptions dlo = new DataLoadOptions();

                dlo.LoadWith<ParentTable>(p => p.ChildTables);

                db.LoadOptions = dlo;

    ===================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 1, 2010 2:23 AM
    Moderator
  • Hi Michel,

     

    I think it is complicated to make the EntitySet collection to support multiple column sorting here.   I have another workaround for your references.   First we create a custom property in the partial class of the master entity to store the ordered detail entities.  Then we set the DataMember of the BindingSource on this custom property instead of the original EntitySet collection.  Here are some sample codes:

    ==========================================================================

        public partial class ParentTable

        {

            private List<ChildTable> _orderedChildTables;

            public List<ChildTable> OrderedChildTables

            {

                get

                {

                    if (_orderedChildTables == null)

                    {

                        _orderedChildTables = this.ChildTables.OrderBy(c => c.Name).ThenByDescending(c => c.id).ToList();

                       

                    }

                    return _orderedChildTables;

                }

            }

        }

    ==========================================================================

    The custom property is ordered by Name and then by id. 

     

    Binding the source:

    ==========================================================================

                DataClasses3DataContext db = new DataClasses3DataContext();

                BindingSource bs1 = new BindingSource();

                BindingSource bs2 = new BindingSource();

     

                bs1.DataSource = db.ParentTables.OrderBy(p => p.Name);

                bs2.DataSource = bs1;

                bs2.DataMember = "OrderedChildTables";

     

                dataGridView1.DataSource = bs1;

                dataGridView2.DataSource = bs2;

    ==========================================================================

     

    If you have any questions, please feel free to let me know.


    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, June 3, 2010 6:33 AM
    Moderator
  • Hi Michel,

     

    Sorry for the delay!   Can you use the .Take extension method and pass 10 into it?  

    ===========================================================================

    public partial class ParentTable

        {

            private List<ChildTable> _orderedChildTables;

            public List<ChildTable> OrderedChildTables

            {

                get

                {

                    if (_orderedChildTables == null)

                    {

                        _orderedChildTables = this.ChildTables.OrderBy(c => c.Name).ThenByDescending(c => c.id).Take(10).ToList();

                       

                    }

                    return _orderedChildTables;

                }

            }

        }

     

    ===========================================================================

     

    For the BindingSource.Sort for multiple columns, the MSDN document mentioned that the data source supports multiple column sorting when SupportsAdvancedSorting is True.   I recommend you check these additional references:

    http://windowsclient.net/blogs/faqs/archive/2006/07/10/how-do-i-sort-on-multiple-columns.aspx

    http://www.codeproject.com/KB/grid/Multiple_columns_sorting.aspx

     

    Also, I suggest you consult this question at Windows Data Controls and Databinding forum, http://social.msdn.microsoft.com/Forums/en/winformsdatacontrols/threads, for the multiple column sorting issue.  

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 8, 2010 7:51 AM
    Moderator
  • Hi Michel,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Lalbatros Thursday, June 10, 2010 12:57 PM
    Thursday, June 10, 2010 2:00 AM
    Moderator

All replies

  • Hello Michel,

     

    Welcome to LINQ to SQL forum!

     

    If I understand your request correctly, you want to order both the master and detail collection in LINQ to SQL databinding.    I recommend order the master collection via LINQ to SQL query, so the collection is actually ordered at the database side via ORDERBY T-SQL.   Then for the detail collection, we can use BindingSource.Sort property to order the collection at the client side.   Here are some sample codes for your references:

    ===================================================================================

                DataClasses1DataContext db = new DataClasses1DataContext();

                            

                BindingSource bs1 = new BindingSource();

                BindingSource bs2 = new BindingSource();

     

                bs1.DataSource = db.ParentTables.OrderBy(p => p.Name);

                bs2.DataSource = bs1;

                bs2.DataMember = "ChildTables";

                bs2.Sort = "Name";

     

                dataGridView1.DataSource = bs1;

                dataGridView2.DataSource = bs2;

    ===================================================================================

    Both the ParentTable and ChildTable are sorted by Name column.  

     

    The above codes use LINQ to SQL’s lazy loading feature.   When we select each row in the master DataGridView, LINQ to SQL queries the corresponding children entities.   If you want to load all the related entities in one call to the database, please use the DataLoadOptions. 

    ===================================================================================

                DataLoadOptions dlo = new DataLoadOptions();

                dlo.LoadWith<ParentTable>(p => p.ChildTables);

                db.LoadOptions = dlo;

    ===================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 1, 2010 2:23 AM
    Moderator
  • Thanks Lingzhi,

    As for all beginners, a short suggestion can lead to many more questions.

    I tested your suggestion based on defining two new BindingSources.
    It didn't work completely since I need to sort on two columns in the child table.
    When two columns are used such as in  the statement "bs2.Sort = "col1,col2";", nothing happens.
    With one column, it worked as expected.

    I also tried sorting on the dataGridViews, and even tried Sort(IComparer comparer).
    This was not successful since this method doesn't work when the dataGridView is connected to an external dataSource.

    My guess now is that I should use something like Sort(IComparer comparer) on the BindingSource side.
    But, since I am a beginner, I absolutely don't know how to do that.

    Would you have some suggestion for another attempt?

    Thanks,

    Michel

    Wednesday, June 2, 2010 9:07 AM
  • Hi Michel,

     

    I think it is complicated to make the EntitySet collection to support multiple column sorting here.   I have another workaround for your references.   First we create a custom property in the partial class of the master entity to store the ordered detail entities.  Then we set the DataMember of the BindingSource on this custom property instead of the original EntitySet collection.  Here are some sample codes:

    ==========================================================================

        public partial class ParentTable

        {

            private List<ChildTable> _orderedChildTables;

            public List<ChildTable> OrderedChildTables

            {

                get

                {

                    if (_orderedChildTables == null)

                    {

                        _orderedChildTables = this.ChildTables.OrderBy(c => c.Name).ThenByDescending(c => c.id).ToList();

                       

                    }

                    return _orderedChildTables;

                }

            }

        }

    ==========================================================================

    The custom property is ordered by Name and then by id. 

     

    Binding the source:

    ==========================================================================

                DataClasses3DataContext db = new DataClasses3DataContext();

                BindingSource bs1 = new BindingSource();

                BindingSource bs2 = new BindingSource();

     

                bs1.DataSource = db.ParentTables.OrderBy(p => p.Name);

                bs2.DataSource = bs1;

                bs2.DataMember = "OrderedChildTables";

     

                dataGridView1.DataSource = bs1;

                dataGridView2.DataSource = bs2;

    ==========================================================================

     

    If you have any questions, please feel free to let me know.


    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, June 3, 2010 6:33 AM
    Moderator
  • Thanks a lot Lingzhi, your post was very useful.

    I realised that the data classes are partial classes and can be extended.
    Therefore, for my current need, I created an additional read-only property.
    It combines the year (yyyy) and the week (ww) from my table:

     partial class silosFilling
     {
     public int yyyyww
     {
     get {return this._yyyy*100+this._ww;}
     }
     }
    

    Using this new sort field was then very easy: bs2.Sort = "yyyyww".
    That did what I wanted to do.

    Your suggestion also uses the extensibility, I just begin to understand it better now that I am writing here.
    I have not used any List<> object yet, since I am switching rather late from VB6 to C#.
    If I understand well, you create a list based on records from the child table.
    This list is ordered and used in place of the original.
    Therefore, thanks to your suggestion, I have a better idea of what the DataClasses.Designer contains and how it can be extended in different ways. But I have plenty of other things to get familiar with.

    Maybe an overview based on class diagrams could be very useful.
    Some light-code tutorial would be useful too.
    Would you know where I could find that for data-related classes?

    Thanks,

    Michel

    PS

    I do not understand why the BindingSource.Sort documentation talks about "Multiple columns can be separated by commas". http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.sort.aspx

    • Edited by Lalbatros Thursday, June 3, 2010 11:23 AM
    Thursday, June 3, 2010 9:56 AM
  • Lingzhi, I have now a related question: How could I restrict the number of records to the last 10 records, for example, based on the sort order just defined? Thanks, Michel
    Thursday, June 3, 2010 11:09 AM
  • Hi Michel,

     

    Sorry for the delay!   Can you use the .Take extension method and pass 10 into it?  

    ===========================================================================

    public partial class ParentTable

        {

            private List<ChildTable> _orderedChildTables;

            public List<ChildTable> OrderedChildTables

            {

                get

                {

                    if (_orderedChildTables == null)

                    {

                        _orderedChildTables = this.ChildTables.OrderBy(c => c.Name).ThenByDescending(c => c.id).Take(10).ToList();

                       

                    }

                    return _orderedChildTables;

                }

            }

        }

     

    ===========================================================================

     

    For the BindingSource.Sort for multiple columns, the MSDN document mentioned that the data source supports multiple column sorting when SupportsAdvancedSorting is True.   I recommend you check these additional references:

    http://windowsclient.net/blogs/faqs/archive/2006/07/10/how-do-i-sort-on-multiple-columns.aspx

    http://www.codeproject.com/KB/grid/Multiple_columns_sorting.aspx

     

    Also, I suggest you consult this question at Windows Data Controls and Databinding forum, http://social.msdn.microsoft.com/Forums/en/winformsdatacontrols/threads, for the multiple column sorting issue.  

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 8, 2010 7:51 AM
    Moderator
  • Hi Michel,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Lalbatros Thursday, June 10, 2010 12:57 PM
    Thursday, June 10, 2010 2:00 AM
    Moderator