how read 2 table data having one to many relation form SQL in Entity Framework

Question

i am new in ASP.NET its my first application in ASP.NET MVC how to get data of product and category in Product Object please check my controller.

Category Class

 public partial class Category
    {
        public Category()
        {
            Products = new HashSet<Product>();
        }
        public int categoryId { get; set; }

        [StringLength(50)]
        public string categoryTitle { get; set; }

        public int? categoryIndex { get; set; }           

        public virtual ICollection<Product> Products { get; set; }
    }

Product Class

public partial class Product
    {
        public int Id { get; set; }

        public int categoryId { get; set; }

        [StringLength(50)]
        public string productTitle { get; set; }

        [UIHint("tinymce_jquery_full"),  AllowHtml ]
        public string productDescription { get; set; }

        [StringLength(1)]
        public string status { get; set; }

      public virtual Category Category { get; set; }
    }

Controller

public ActionResult Index()
        {
             DataContext db = new DataContext();
              // var products = db.Products.Where(a =>a.productImage !=   // here is some code for getting list of products with category details  ) 
              // want to save value in product so that i can read like this 

              // products.productTitle;
              // products.productDescription ;
              // products.Category.categoryId ;
              // products.Category.categoryTitle ;

            return View(products);
        }

its easy to get all product data in "var product" variable but i need all producta with category id and category name like this products.Category.categoryTitle ;

i am using "code first from database" approach.

What is the problem? Apparently you're doing it the right way... — JotaBe, Jul 08 '14 at 09:27
in "var products" variable i want to save both product and category data. so that i can read like products.productTitle; products.Category.categoryId ; — Yasir Aslam, Jul 08 '14 at 09:42
how to save data in "public virtual Category Category { get; set; }" in Product Class. its easy to get all product but how get all product with category name — Yasir Aslam, Jul 08 '14 at 09:44
Possible duplicate of http://stackoverflow.com/questions/13047845/how-to-include-a-child-objects-child-object-in-entity-framework-5 — SBirthare, Jul 08 '14 at 10:28
Please refer [this stack overflow question](http://stackoverflow.com/questions/24583396/how-to-get-data-from-multiple-related-tables-and-pass-it-to-the-view-mvc/24583549#24583549) — Hiren Kagrana, Jul 08 '14 at 10:29
Hiren Kagrana i see link there mention "The product table will automatically retrieve the related category and subcategory data for the specific product.No need to Join or Inclue the related tables explicitly." but in my case only product information get product.Category = NULL display — Yasir Aslam, Jul 08 '14 at 10:53
You have to tell Entity Framework to load entities i.e. category linked with the product. And the way to do that is using Include explicitly. e.g. db.Products.Include(o => o.Category).Single(o => o.Id == id); See the link I shared in my earlier comment. — SBirthare, Jul 08 '14 at 11:12
SBirthare see my error [link](http://greenwoodpaintingswfl.com/error.png) — Yasir Aslam, Jul 09 '14 at 05:43
I am using "code first from database" approach. That's why category not load. now i am using "Database First" now every thing working fine Thanks — Yasir Aslam, Jul 13 '14 at 10:00

Manoj · Answer 1 · 2014-07-10T07:22:38.137

public ActionResult Index()
        {
             DataContext db = new DataContext();
         var products =(from u in db.products
                         select u).ToList(); // this will return all products
            return View(products);
        }

I would create a context reference for my database shadow that is Entity and I have initiated it with new instance DataContext db= new DataContext();

Now from the source of entity designer where tables are shown in diagrams will appear in intellisense.

This is simple query -

 var products =(from u in db.products
                             select u).ToList();

**Note- ** Now I can access table products.

If you access two tables than this will help you..

var products =(from u in db.products
              join v on u.primarykeycolumn equals v.foreignkeycolumn
                                 select new{ u,v}).ToList();

Here u.primarykeycolumn equals v.foreignkeycolumn is condition that is connect both tables

You should provide some context for your answer and explain how it solves the OP's problem. Otherwise, there is a chance that it will be deleted, as it was automatically flagged as "low quality". — Patrick Q, Jul 08 '14 at 14:15
yes you can Now access table products. but i want category with product table. — Yasir Aslam, Jul 08 '14 at 16:42

score 0 · Answer 2 · answered Jul 09 '14 at 05:48

0

Try this

var graph = db.Products.Include("Category").toList();

I would suggest you read about Lazy Loading, Explicit Loading and Eager Loading. http://www.entityframeworktutorial.net/EntityFramework4.3/eager-loading-with-dbcontext.aspx

answered Jul 09 '14 at 05:48

Adnan Ahmed

844
6
19

how read 2 table data having one to many relation form SQL in Entity Framework

2 Answers2