Show records from different tables in a single view using joins

Question

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 = c,

});

i have joint 4 tables with candidate table.

Cert is a ICollection<Cert> field in the Candidate class

how to show records from tables using a candidate view

of course these records from different tables are candidate Id based records

Jignesh Kumar · Answer

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();

Prasad Raveendran · Answer

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();

Jayraj Chhaya · Answer

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();