none
How to show data from bridge table in the index view? RRS feed

  • Question

  • Hello!

    I have the following tables:

    1. Case(Id, CaseNo, CourtId)
    2. Party(Id, Name)
    3. Petitioner(CaseId, PartyId)...................Bridge Table
    4. Hearing(Id, Date, CaseId)
    5. Court(Id, Name)

    I want to display data as under:

    Court Name: High Court

    Case                  Hearing                      Petitioner

    Case 1                 10/10/16                  1. ABC, 2. XYZ

    Case 2                 15/10/16                  1. BBC

    Court Name: District Court

    Case 5                 10/10/16                 1. CCD

    Case 6                 20/10/16                  1. VVV, 2. KKK

    In short, I want to group data by Court and show petitioners as well in the list.

    So please show how to write a LINQ to Entity query to retrieve the data in the order mentioned above.

    Saturday, October 15, 2016 2:32 PM

All replies

  • Hi ArunKhatri,

    According to your description, it seems that Court and Case are a many-to-many relationship, and I create a simple as below for your reference.

    #Models

    [Table("Party")]
        public partial class Party
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public Party()
            {
                Case = new HashSet<Case>();
            }
    
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            public int Id { get; set; }
    
            [StringLength(50)]
            public string Name { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Case> Case { get; set; }
        }


     [Table("Case")]
        public partial class Case
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public Case()
            {
                Case1 = new HashSet<Case>();
                Hearings = new HashSet<Hearings>();
                Party = new HashSet<Party>();
            }
    
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            public int CaseId { get; set; }
    
            [StringLength(50)]
            public string CaseNo { get; set; }
    
            public int CourtId { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Case> Case1 { get; set; }
    
            public virtual Case Case2 { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Hearings> Hearings { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Party> Party { get; set; }
        }

    #DbContext and Fluent API

    public partial class Model1 : DbContext
        {
            public Model1()
                : base("name=Model1")
            {
            }
    
            public virtual DbSet<Case> Case { get; set; }
            public virtual DbSet<Court> Court { get; set; }
            public virtual DbSet<Hearings> Hearings { get; set; }
            public virtual DbSet<Party> Party { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Case>()
                    .Property(e => e.CaseNo)
                    .IsUnicode(false);
    
                modelBuilder.Entity<Case>()
                    .HasMany(e => e.Case1)
                    .WithRequired(e => e.Case2)
                    .HasForeignKey(e => e.CourtId);
    
                modelBuilder.Entity<Case>()
                    .HasMany(e => e.Hearings)
                    .WithRequired(e => e.Case)
                    .WillCascadeOnDelete(false);
    
                modelBuilder.Entity<Case>()
                    .HasMany(e => e.Party)
                    .WithMany(e => e.Case)
                    .Map(m => m.ToTable("Petitioner").MapLeftKey("CaseId").MapRightKey("PartyId"));
    
                modelBuilder.Entity<Court>()
                    .Property(e => e.Name)
                    .IsUnicode(false);
    
                modelBuilder.Entity<Party>()
                    .Property(e => e.Name)
                    .IsUnicode(false);
            }
        }

    #Usage:

    using (var db = new Model1())
                {
                    var result = db.Court.Select(c => new
                    {
                        CourtName = c.Name,
                        Cases = db.Case.Where(ca=> ca.CourtId.Equals(c.CourtId)).Select(cc=> new {
                            CaseName = cc.CaseNo,
                            Hearing = db.Hearings.Where(h=>h.CaseId.Equals(cc.CaseId)).OrderBy(h=>h.Date).FirstOrDefault().Date,
                            Petitioner = cc.Party.Select(p=>p.Name)
                        })
                    });
    
                    foreach (var item in result)
                    {
                        Console.WriteLine("{0}", item.CourtName);
                        foreach (var sub in item.Cases)
                        {
                            Console.WriteLine("{0} -- {1}  -- {2}", sub.CaseName, sub.Hearing, string.Join(",",sub.Petitioner));
                        }
                    }
                    Console.ReadKey();
                }

    #Result

    Best regards,

    Cole Wu


    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.

    Monday, October 17, 2016 5:27 AM
    Moderator
  • Hello Cole Wu,

    Instead of writing it to the console could you please show how pass the query result stored in var result to the View using viewmodel. I tried doing it but could not succeed as a single record may have multiple petitioners since there is many to many relationship between Case and Party.

    Warm regards,

    Arun

    Tuesday, October 25, 2016 12:07 PM
  • Hi ArunKhatri,

    I'm not quite sure what kind of application you are developing. WPF or Asp.Net?

    Since it is not a Data issue, I would suggest that you could post a new thread on related forum for support.

    Thanks for your understanding.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 26, 2016 1:38 AM
    Moderator
  • Dear Cole Wu,

    I am extremely sorry for not mentioning the kind of application I am developing. In the past, you have been very kind in extending help to solve the problems. I hope you won't disappoint me this time by not providing the solution. As you need to know, the kind of application I am working on is ASP.Net with MVC Razor view using Entity Framework Database first approach.

    In the query that you have provided, there is nesting for which I am not able to pass the data stored in different variables to the ViewModel. I also tried removing the nesting but still found it difficult to pass petitioner to the ViewModel.

    I hope you would definitely help me solve the issue.

    Warm regards,

    Arun

     

    Wednesday, October 26, 2016 8:51 AM
  • Hi ArunKhatri,

    If you asp.net with MVC, I suggest you could custom a DTOs like this:

    namespace BookService.Models { public class CourtDTO { public int Id { get; set; } public string CourtName { get; set; } public List<CaseDTO> Cases { get; set; } } public class CaseDTO { public int CaseId { get; set; } public string CaseName { get; set; }

    public string Hearing { get; set; }

    public string Petitioner { get; set; } } }

    and change your code like this:

    using (var db = new Model1()) { var result = db.Court.Select(c => new CourtDTO {

    Id = c.Id CourtName = c.Name, Cases = db.Case.Where(ca=> ca.CourtId.Equals(c.CourtId)).Select(cc=> new CaseDTO{ CaseName = cc.CaseNo, Hearing = db.Hearings.Where(h=>h.CaseId.Equals(cc.CaseId)).OrderBy(h=>h.Date).FirstOrDefault().Date, Petitioner = cc.Party.Select(p=>p.Name) }).ToList() });


    https://www.asp.net/web-api/overview/data/using-web-api-with-entity-framework/part-5

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 31, 2016 7:31 AM
    Moderator