Combining data from multiple tables to display in the MVC View

Question

I have two separate models in my MVC project

[Table("Table1")]   
 public class Companies : IEnumerable
 {      
     [Key]
    public double ID { get; set; }
    public System.Guid Key { get; set; }
    public string Name { get; set; }
    public string Company { get; set; }
    public string Industry { get; set; }
    public string Rank{ get; set; }


    public IEnumerator GetEnumerator()
    {
        yield return this.ID;
        yield return this.Key;
        yield return this.Name;
        yield return this.Company;
        yield return this.Industry;
        yield return this.Rank;

    }
 }

and

[Table("Table2")]
 public class Registration : IEnumerable
 {
    [Key]
    public System.Guid Key { get; set; }
    public string FieldValue{ get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }


    public IEnumerator GetEnumerator()
    {
        yield return this.Key;
        yield return this.FieldValue;
        yield return this.StartDate;
        yield return this.EndDate;

     }
  }

I need to display a combination of data in my view:

SELECT t2.FieldValue, t1.Name, t1.Company, t1.Industry, t1.Rank 
FROM Table1 as t1 INNER JOIN Table2 as t2 ON t1.Key=t2.Key

I created the following Context class:

public class TablesContext: DbContext
{
    public DbSet<Companies> companies { get; set; }
    public DbSet<Registration> registration { get; set; }
}

Now I can pass the data for companies from the controller to the view using the following code:

private TablesContext tc = new TablesContext();
 var comps = (from c in tc.companies                                 
                    join r in tc.registry
                    on c.Key equals r.Key
      select new { FieldValue=r.FieldValue, Name=c.Name, Company=c.Company,Industry=c.Industry, Rank=c.Rank});
 return View(comps);

Now I am a bit stuck trying to make TablesContext class enumerable and loop through the result set in the View. Can anyone point me in the right direction?

Mikayil Abdullayev · Accepted Answer · 2015-10-08T04:31:12.650

The way you'd normally (probably) go in ASP.NET MVC is you create a ViewModel something like this:

public class CompanyRegViewModel{
  public string FieldValue {get;set;};
  public string Name {get;set;};
  public string Company {get;set;};
  public string Industry {get;set;};
  public string Rank {get;set;};
}

Then instead of creating an anonymous object you can create an instance of the CompanyRegViewModel class like this:

var comps= ..... select new CompanyRegViewModel{ FieldValue=r.FieldValue, Name=c.Name....};

On the other side you want to create a View whose model is a list of CompanyRegViewModel. (Doesn't have to be List<> though. It can be anything that implements IEnumerable<>). Here's an example part of your possible View:

......
<table>
foreach(var item in Model){
  <tr>
    <td>item.Name</td>
    <td>item.Company</td>
    <td>item.Industry</td>
    @*and so on*@
  </tr>
}
</table>

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

Create a VIEW MODEL, i.e. another class that contains the fields that you need from both the tables. Then load it up with the data, make it into a LIST and pass that to the View. Then you can use a foreach to loop through it quite easily.

It is nicely explained here

https://stackoverflow.com/a/10020862/3777098

Good luck

edited May 23 '17 at 12:15

Community

1
1

answered Oct 06 '15 at 18:13

alikuli

526
6
18

score 1 · Answer 3 · answered Oct 06 '15 at 18:16

You need to create a view model - see example below:
Plus try to use using statement to dispose the context after you are done using it.

public class CompanyRegistrationModel
{
    public string FieldValue { get; set; }
    public string Name { get; set; }
    public string Company { get; set; }
    public string Industry { get; set; }
    public string Rank { get; set; }

    public CompanyRegistrationModel Get()
    {
        using (TablesContext tc = new TablesContext())
        {

        var comps = (from c in tc.companies
            join r in tc.registry
                on c.Key equals r.Key
            select
                new CompanyRegistrationModel
                {
                    FieldValue = r.FieldValue,
                    Name = c.Name,
                    Company = c.Company,
                    Industry = c.Industry,
                    Rank = c.Rank
                }).ToList();
        return comps;
        }
    }
}

Combining data from multiple tables to display in the MVC View

3 Answers3