如何在索引视图中显示桥表中的数据？ [英] How to show data from bridge table in the index view?

问题描述

您好！

我有以下表格：

1. 案例（Id，CaseNo，CourtId ）
2. 派对（身份证，姓名）
3. 申请人（CaseId，PartyId）...................桥牌表
4. 听证会（Id，Date，CaseId）
5. Court（Id，Name）

我想在下面显示数据：

法院名称：高等法院

案例                 听证会                     申请人

案例1                10/10/16                  1。 ABC，2。XYZ

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

案例2                2016年10月15日                  1。 BBC

Case 2                 15/10/16                  1. BBC

法院名称：地方法院

案例5                10/10/16                1. CCD

Case 5                 10/10/16                 1. CCD

案例6                20/10/16                  1。 VVV，2。KKK

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.

所以请说明如何编写LINQ to Entity查询以按上述顺序检索数据。

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

推荐答案

嗨ArunKhatri，

Hi ArunKhatri,

根据你的描述，似乎Court和Case是一个多对多的关系，我创建了一个简单的，如下所示。

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和Fluent API

#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

#Result

祝你好运，

Cole Wu

这篇关于如何在索引视图中显示桥表中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！