Show records from different tables in a single view using joins

Hi,

To display records from multiple tables in a single view using joins in a .NET application, you can utilize LINQ queries to join the tables and retrieve the desired data. In the provided code snippet, the user has already performed joins on four tables with the Candidate table based on the Candidate Id.

To show records from these tables using a Candidate view, you can modify the LINQ query to include the fields from the joined tables in the select statement. For example, if you want to display the Cert field from the ICollection in the Candidate class, you can include it in the select statement like this:

var records = (from a in context.GetModel()
               join cCon in cContext.GetModel() on a.CanId equals cCon.CanId
               join aCon in aContext.GetModel() on a.CanId equals aCon.CanId
               join sCon in slContext.GetModel() on a.CanId equals sCon.CanId
               join eCon in eContext.GetModel() on a.CanId equals eCon.CanId
               where a.Id == id
               select new {
                   CandidateId = a.CanId,
                   Certificates = a.Cert // Assuming Cert is the field from the Candidate class
                   // Include other fields from joined tables as needed
               }).ToList();

Hello Umair,

Please refer,

var candidateWithCerts = (from a in context.GetModel()
                          join cCon in cContext.GetModel() on a.CanId equals cCon.CanId
                          join aCon in aContext.GetModel() on a.CanId equals aCon.CanId
                          join sCon in slContext.GetModel() on a.CanId equals sCon.CanId
                          join eCon in eContext.GetModel() on a.CanId equals eCon.CanId
                          where a.Id == id
                          select new Candidate  
                          {
                              // Populate Candidate properties as needed from above tables
                              Id = a.Id,
                              Name = a.Name,
                              // Populate Cert collection based on a candidateId matches from cert
                              Cert = (from cert in context.Certificates
                                      where cert.CandidateId == a.Id
                                      select cert).ToList()
                          }).FirstOrDefault();

try this

var rec = (from a in context.GetModel()
           join cCon in cContext.GetModel() on a.CanId equals cCon.CanId
           join aCon in aContext.GetModel() on a.CanId equals aCon.CanId
           join sCon in slContext.GetModel() on a.CanId equals sCon.CanId
           join eCon in eContext.GetModel() on a.CanId equals eCon.CanId
           where a.Id == id
           select new Candidate  
           {
               Cert = (from cert in certContext.GetModel()
                       where cert.CanId == a.CanId // Assuming CanId is the foreign key linking certificates to candidates
                       select cert).ToList(),
               // Assuming other properties of Candidate are populated here as well
           }).FirstOrDefault();