1

I have created 3 tables relation (users, projects, products)

one user has many projects and one project has many products (one to many)

I need to show all the projects and contained products on user login

I have done it using the following code but I don't think this is best way to deal with it. I need to do it better

public ActionResult Index()
    {

        ModulesViewModel mvm = new ModulesViewModel();
        List<Modules> modules = new List<Modules>();
        var userId = User.Identity.GetUserId();
        var projects = _adsDbContext.Project.Where(x=>x.UserID == userId).ToList();
        foreach (var pro in projects)
        {

            var productData = _adsDbContext.Product.Where(x => x.ProjectID == pro.ProjectID); 
            modules.AddRange(productData);

        }
        modules = modules.OrderBy(x => x.ProjectID).OrderBy(x=>x.ModuleNumber).ToList();
        mvm.Modules = modules;
        return View(mvm);
    }

public class Project
{
    public int ProjectID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<ProductData> Products { get; set; }


    public string UserID { get; set; }
    public virtual ApplicationUser ApplicationUser { get; set; }
}

public class ProductData : Modules
{
    public int ProductDataID { get; set; }
    public float ConversionRate { get; set; }
    public float Price { get; set; }
    public float TotalSales { get; set; }
    public float GrossSales { get; set; }
    public float NetProfit { get; set; }
    public float ProfitPerLead { get; set; }
}


public abstract class Modules
{

    public int ProjectID { get; set; }
    public virtual Project Project { get; set; }
}

This works fine but I need to do it in better way rather to create relation from scratch or make the query better.

Engr Umair
  • 130
  • 10

1 Answers1

2

Your model contains navigation property for each end of the project to product one-to-many relationship.

This allows you to start the query from the project, apply the filter and then "navigate" down using the collection navigation property and SelectMany:

var modules = _adsDbContext.Project
    .Where(x => x.UserID == userId)
    .SelectMany(x => x.Products) // <--
    .OrderBy(x => x.ProjectID).ThenBy(x => x.ModuleNumber)
    .ToList<Modules>();

or you can start the query from the product and use the reference navigation property to "navigate" up for applying the filter:

var modules = _adsDbContext.Product
    .Where(x => x.Project.UserID == userId) // <--
    .OrderBy(x => x.ProjectID).ThenBy(x => x.ModuleNumber)
    .ToList<Modules>();
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • Thanks for the reply, one more problem I am stuck is following : I use modules as base class to product and other types so I get all different modules one by one and add it to modules list, then at frontend I iterate on modules and need to to explicit cast to child and then show the details, can I make it better? – Engr Umair May 12 '19 at 09:02
  • I also need to save project and product at once if project isn't created, if project was created then only update the product – Engr Umair May 12 '19 at 09:06
  • I'm afraid we can't cover multiple issues per single post. The above answer is based on the use case and code from your post. If you have other issues / use cases, create separate question(s) and clearly explain them along with the goals. – Ivan Stoev May 12 '19 at 10:22