Get related array from another table by foreign key

Question

I'm using SQL Server 2012, MVC WebAPI, AutoMapper and Entity Framework. In the database I have two tables with one-to-many relation, for example Categories and Products; a category can have more products but a product can have only one category. What I want is a json that for each category contains also an array of related products by Products.CategoryId field.

This is what I made after some search in internet:

public class CategoriesViewModel
{
    public string CategoryName { get; set; }
    public IEnumerable<ProductsViewModel> Products { get; set; }
}

public class ProductsViewModel
{
    public string Name { get; set; }
}

public static class ViewModelMapper
{
    public static TDestination Map<TDestination>(object source)
    {
        return Mapper.Map<TDestination>(source);
    }

    public static void RegisterMaps()
    {
        AutoMapper.Mapper.Initialize(config =>
        {
            config.CreateMap<Products, ProductsViewModel>().ReverseMap();
            config.CreateMap<Categories, CategoriesViewModel>()
                .ForMember(dest => dest.Products, opt => opt.MapFrom(src => src.Products))
                .ReverseMap();
        }
    }
}

// Repository
public IEnumerable<CategoriesViewModel> GetCategories()
{
    return ViewModelMapper.Map<IEnumerable<CategoriesViewModel>>(Context.Categories);
}

//Business Logic
public IEnumerable<CategoriesViewModel> GetCategories()
{
    return Repository.GetCategories();
}

[Route("Categories"), HttpGet]
public IHttpActionResult GetCategories()
{
    return Ok(BL.GetCategories());
}

My result is a list of Categories with and empty or null Products array and I cannot find a solution.

How can I reach my result? Thank you

EDIT:

Entity Framework Models

public partial class Category
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Category()
    {
        this.Products = new HashSet<Product>();
    }

    public int CategoryId { get; set; }
    public string CategoryName { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Product> Products { get; set; }
}

public partial class Product
{
    public int ProductId { get; set; }
    public int CategoryId { get; set; }
    public string ProductName { get; set; }

    public virtual Category Category { get; set; }
}

Category and Product are only examples, ignore if the view model property names are not the same.

Answer 1

I suspect you have Lazy Loading off.

So one solution would be to turn it on, but I would not suggest it, because it would execute many database queries.

A better option would be to use Eager Loading:

return ViewModelMapper.Map<IEnumerable<CategoriesViewModel>>(
    Context.Categories.Include(c => c.Products));

or

return ViewModelMapper.Map<IEnumerable<CategoriesViewModel>>(
    Context.Categories.Include("Products"));

But the best option with AutoMapper is to use ProjectTo method from QueryableExtensions:

retirn Context.Categories.ProjectTo<CategoriesViewModel>();

for the reasons explained in the link.