Displaying data from multiple tables in a view as a list - ASP.Net MVC

Question

Hi, I have the following two tables (basic outline):

Tbl_CategoryType

ID LevelID Description

Tbl_Levels ID Name

Basically, I want to present all of the information in the Tbl_CategoryType table while referencing the Tbl_Levels.Name data based on the Tbl_CategoryType.LevelID number.

I have tried using a join in my repository as below;

public IQueryable GetAllTypesInCategory(int CatID)
{
     return (from x in DBEntities.LU_LST_CategoryTypeSet
             where x.CategoryID == CatID && x.Enabled == 1
             join y in DBEntities.LU_LST_LevelSet on x.LevelID equals y.ID
             select new {x, y});
}

However, when I call that method there is no type I can assign it to as it doesn't fit into the type of either the Category or Level.

I'm assuming I need to do this through a custom viewmodel but can't figure out the steps.

Thanks in advance

Answer 1

By using this line in your linq statement:

select new {x, y}

you are creating a new anonymous type, which is a different type from your Entity types.

I'm guessing you're not using EntityFramework or some other heavy framework that will automatically resolve foreign key relationships to create linked entities. If true, then yes, you will need to create a ViewModel.

Just create a simple wrapper class that contains one of each entity as a property.

public class MyViewModel
{
    public MyViewModel(LU_LST_CategoryTypeSet x, LU_LST_LevelSet y)
    {
        Category = x;
        Level = y;
    }

    public LU_LST_CategoryTypeSet Category { get; set;}
    public LU_LST_LevelSet Level { get; set; }
}

Then in your Linq statement, instead of creating anonymous types, create MyViewModel types:

public IQueryable GetAllTypesInCategory(int CatID)
{
     return (from x in DBEntities.LU_LST_CategoryTypeSet
             where x.CategoryID == CatID && x.Enabled == 1
             join y in DBEntities.LU_LST_LevelSet on x.LevelID equals y.ID
             select new {x, y});
}

Then copy the results into your model classes:

   var listOfTypes = GetAllTypesInCategory(catID);
   foreach (var item in listOfTypes)
   {
      var model = new MyViewModel(item.x, item.y);

      //Do whatever with the model to get it to the view.
   }

Make your View inherit from MyViewModel.

Answer 2

If there is an association between the two entities you can access the second type using it. The only thing you need to do in that case is use the Include() method to load the association data.

       public List<LU_LST_CategoryType> GetAllTypesInCategory(int CatID)  
         { 
             return (from x in DBEntities.LU_LST_CategoryTypeSet.Include("LU_LST_LevelSet") 
                     where x.CategoryID == CatID && x.Enabled == 1  
                     select x).ToList(); 
         }

Than for every LU_LST_CategoryTypeSet category you can call category.LU_LST_Level