none
Searching/filtering the details of a master/details Many to Many relation (Students , classes example) RRS feed

  • Question

  • I have a WPF / entity framework application, my model have student , class entities there is a many to many association/relation between student and class (student can have many classes and class have many students )

    student [ Id ,Name , Classes(Navigation property) ] classes [ Id ,Title, Students(Navigation property ]

    in this case the entity framework don't show the join/relation table.

    i have a 2 DataGirds as master details, student grid is the master and classes is details

    the girds are connected to data throw CollectionViewSource

    <CollectionViewSource x:Key="studentViewSource"
                                  d:DesignSource="{d:DesignInstance {x:Type local:Student}, CreateList=True}" />
    <CollectionViewSource x:Key="studentClassesViewSource"
                                  Source="{Binding Classes, Source={StaticResource studentViewSource}}" />



    how can I use LINK query to search the selected student classes  , for example , I need to fine all the classes that the selected student registered in that start with A or that have a duration more than 3 hours?

    My UI XAML:

      <StackPanel DataContext="{StaticResource studentViewSource}"
                            Orientation="Horizontal"
                            Height="200">
                    <DataGrid x:Name="StudentsDataGrid"
                              AutoGenerateColumns="False"
                              EnableRowVirtualization="True"
                              Height="200"
                              ItemsSource="{Binding}"
                              RowDetailsVisibilityMode="VisibleWhenSelected"
                              Width="452">
                        <DataGrid.Columns>
                            <DataGridTextColumn x:Name="idColumn"
                                                Binding="{Binding Id}"
                                                Header="Id"
                                                Width="SizeToHeader" />
                            <DataGridTextColumn x:Name="nameColumn"
                                                Binding="{Binding Name}"
                                                Header="Student Name"
                                                Width="*" />
                            <DataGridTextColumn x:Name="phonesColumn"
                                                Binding="{Binding Phones}"
                                                Header="Phones"
                                                Width="200" />
                        </DataGrid.Columns>
                    </DataGrid>
    
                    <DataGrid x:Name="StudentclassesDataGrid"
                              AutoGenerateColumns="False"
                              EnableRowVirtualization="True"
                              Height="200"
                              ItemsSource="{Binding Source={StaticResource studentClassesViewSource}}"
                              RowDetailsVisibilityMode="VisibleWhenSelected"
                              Width="380">
                        <DataGrid.Columns>
    
                            <DataGridTextColumn x:Name="nameColumn1"
                                                Binding="{Binding Name}"
                                                Header="Class Name"
                                                Width="*" />
    
                            <!--<DataGridComboBoxColumn Header="Class Name From Combo"
                                                    Width="*"
                                                    ItemsSource="{Binding Source={StaticResource classViewSource}}"
                                                    DisplayMemberPath="Name"
                                                    SelectedItemBinding="{Binding Classes}" />-->
                            <DataGridTextColumn x:Name="durationColumn"
                                                Binding="{Binding Duration}"
                                                Header="Duration"
                                                Width="SizeToHeader" />
    
                        </DataGrid.Columns>
                    </DataGrid>
                   
                </StackPanel>

    My Code:

    System.Windows.Data.CollectionViewSource studentViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("studentViewSource")));
          // Load data by setting the CollectionViewSource.Source property:
          dc.StudentSet.Load();
          studentViewSource.Source = dc.StudentSet.Local;

    Friday, September 13, 2013 4:23 PM

Answers

  • Hello,

    Thanks for visiting this forum.

    I am confused that which entity framework model you have used(DB First, Model First or Code First?) and the Duration is a column of the class table or not.

    And for this query “find all the classes that the selected student registered in that start with A or that have a duration more than 3 hours”, I made a sample with database first like below:

    Table Structure:

    CREATE TABLE [dbo].[Students] (
    
        [Id]   INT            IDENTITY (1, 1) NOT NULL,
    
        [Name] NVARCHAR (MAX) NOT NULL,
    
        CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([Id] ASC)
    
    );
    
    CREATE TABLE [dbo].[Classes] (
    
        [Id]       INT            IDENTITY (1, 1) NOT NULL,
    
        [Title]    NVARCHAR (MAX) NOT NULL,
    
        [Duration] TIME (7)       NOT NULL,
    
        CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED ([Id] ASC)
    
    );
    
    CREATE TABLE [dbo].[StudentClass] (
    
        [Students_Id] INT NOT NULL,
    
        [Classes_Id]  INT NOT NULL,
    
        CONSTRAINT [PK_StudentClass] PRIMARY KEY NONCLUSTERED ([Students_Id] ASC, [Classes_Id] ASC),
    
        CONSTRAINT [FK_StudentClass_Student] FOREIGN KEY ([Students_Id]) REFERENCES [dbo].[Students] ([Id]),
    
        CONSTRAINT [FK_StudentClass_Class] FOREIGN KEY ([Classes_Id]) REFERENCES [dbo].[Classes] ([Id])
    
    );
    
    The Linq Query:
    
    using (S09Container db = new S09Container())
    
                {
    
                    var result = (from student in db.Students.Include("Classes")
    
                                  select new
    
                                  {
    
                                      classes = from c in student.Classes
    
                                                where c.Duration.Hours > 3
    
                                                select new { c.Id, c.Title, c.Duration }
    
                                  }).ToList().Union(from student in db.Students.Include("Classes")
    
                                                    where student.Name.StartsWith("A")
    
                                                    select new
    
                                                    {
    
                                                        classes = from c in student.Classes
    
                                                                  select new { c.Id, c.Title, c.Duration }
    
                                                    }).ToList()
    
                                ;
    
                    Console.WriteLine(result);
    
    
                    Console.ReadLine();
    
                }
    

    There is a link that shows how to query with linq below:

    http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    If I have understood you incorrectly, could you share more something about the program?

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Hani Safa Monday, September 16, 2013 11:37 AM
    Monday, September 16, 2013 4:42 AM
    Moderator
  • Hi,

    Sorry for being late.

    For converting the result to list of classes, we can do a loop to collect the list of classes:

    List<Class> listClass = new List<Class>();
    
                    foreach (var r in result)
                    {
                        foreach (Class c in r.classes)
                        {
                            Class newc = new Class() { Id = c.Id, Title = c.Title, Duration = c.Duration };
                            listClass.Add(newc);
                        }
                    }

    Maybe it is not a good solution.

    If you have better idea, please share it.

    Thanks &Regrads.

     

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Hani Safa Tuesday, September 17, 2013 5:47 PM
    Tuesday, September 17, 2013 12:16 PM
    Moderator

All replies

  • Hello,

    Thanks for visiting this forum.

    I am confused that which entity framework model you have used(DB First, Model First or Code First?) and the Duration is a column of the class table or not.

    And for this query “find all the classes that the selected student registered in that start with A or that have a duration more than 3 hours”, I made a sample with database first like below:

    Table Structure:

    CREATE TABLE [dbo].[Students] (
    
        [Id]   INT            IDENTITY (1, 1) NOT NULL,
    
        [Name] NVARCHAR (MAX) NOT NULL,
    
        CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([Id] ASC)
    
    );
    
    CREATE TABLE [dbo].[Classes] (
    
        [Id]       INT            IDENTITY (1, 1) NOT NULL,
    
        [Title]    NVARCHAR (MAX) NOT NULL,
    
        [Duration] TIME (7)       NOT NULL,
    
        CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED ([Id] ASC)
    
    );
    
    CREATE TABLE [dbo].[StudentClass] (
    
        [Students_Id] INT NOT NULL,
    
        [Classes_Id]  INT NOT NULL,
    
        CONSTRAINT [PK_StudentClass] PRIMARY KEY NONCLUSTERED ([Students_Id] ASC, [Classes_Id] ASC),
    
        CONSTRAINT [FK_StudentClass_Student] FOREIGN KEY ([Students_Id]) REFERENCES [dbo].[Students] ([Id]),
    
        CONSTRAINT [FK_StudentClass_Class] FOREIGN KEY ([Classes_Id]) REFERENCES [dbo].[Classes] ([Id])
    
    );
    
    The Linq Query:
    
    using (S09Container db = new S09Container())
    
                {
    
                    var result = (from student in db.Students.Include("Classes")
    
                                  select new
    
                                  {
    
                                      classes = from c in student.Classes
    
                                                where c.Duration.Hours > 3
    
                                                select new { c.Id, c.Title, c.Duration }
    
                                  }).ToList().Union(from student in db.Students.Include("Classes")
    
                                                    where student.Name.StartsWith("A")
    
                                                    select new
    
                                                    {
    
                                                        classes = from c in student.Classes
    
                                                                  select new { c.Id, c.Title, c.Duration }
    
                                                    }).ToList()
    
                                ;
    
                    Console.WriteLine(result);
    
    
                    Console.ReadLine();
    
                }
    

    There is a link that shows how to query with linq below:

    http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    If I have understood you incorrectly, could you share more something about the program?

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Hani Safa Monday, September 16, 2013 11:37 AM
    Monday, September 16, 2013 4:42 AM
    Moderator
  • Thanks a lot for your help ,

    I'm using Model first ,And the LINQ query you wrote answered my question.

    (i meant the class start with A and has a duration more than 3 hours, your query answered more than enough)

    but now i have another question , how can i convert the result to list of classes  ? the result is anonymous type.

    i want to assign the result of the query to DataGrid.ItemSource so i have a search in the class of the selected student

    thanks again


    Monday, September 16, 2013 11:50 AM
  • Hi,

    Sorry for being late.

    For converting the result to list of classes, we can do a loop to collect the list of classes:

    List<Class> listClass = new List<Class>();
    
                    foreach (var r in result)
                    {
                        foreach (Class c in r.classes)
                        {
                            Class newc = new Class() { Id = c.Id, Title = c.Title, Duration = c.Duration };
                            listClass.Add(newc);
                        }
                    }

    Maybe it is not a good solution.

    If you have better idea, please share it.

    Thanks &Regrads.

     

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Hani Safa Tuesday, September 17, 2013 5:47 PM
    Tuesday, September 17, 2013 12:16 PM
    Moderator
  • thanks a lot for your help
    Tuesday, September 17, 2013 5:49 PM
  • Hi Fred Bao,

    I've being testing with LINQ and i found another solution to search with in navigation properties

     var q2 = from cla in dc.ClassSet
                  where cla.Students.Contains((from stu in dc.StudentSet
                                                where stu.Name == "Jim"
                                                select stu).FirstOrDefault()) &&
                         cla.Name.Contains("m")
                   select cla;
    this will give us a list (if we call to list()) of all the clases that Jim rigester in and contain m litter , the result is Math , Chemistry

    please let me know that you think

    regards

    hani

    Tuesday, October 22, 2013 9:22 PM
  • Wow, it is excellent and it can be converted to List directly like:

    List<Class> result = (from classes in db.Classes
                                           where db.Students.Contains((from stu in db.Students
                                                                       where stu.Name == "BMX"
                                                                       select stu).FirstOrDefault()) &&
                                              classes.Title.Contains("M")
                                           select classes).ToList();

    Please forget what I have post, by contrast, mine is so cumbersome.

    Thanks for sharing it.

      

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 23, 2013 3:41 AM
    Moderator