Join Tables in Linq using lambda expression

Question

I am working with Entity Framework. I have to apply join of two tables but what I want if there is one table Category with a column categoryid as foreign key which is of integer data type and another table Products with column id as primary key which is also of integer data type.

Now I need to select only those records from Products table which have id which is contained in Category table.

This is my code:

string categoryid= "10,11,12";

datalist.DataSource = (from p in objCategory
                             join q in objProducts on p.categoryid.contains(q.id)
                             select new
                             {
                                 p.FilePath,
                                 p.ItemName,
                                 p.Description,
                                 q.image_url,
                                 q.name,
                                 q.price
                             }).ToList();

Christos · Accepted Answer · 2014-07-07 09:40:44Z

2

You could something like this:

List<int> categoryIds = new List<int>(){ 10, 11, 12 };

datalist.DataSource = (from c in objCategory
                       join p in objProducts 
                       on c.categoryid equals p.categoryid
                       where categoryIds.Contains(c.categoryid)
                       select new
                       {
                           c.FilePath,
                           c.ItemName,
                           c.Description,
                           p.image_url,
                           p.name,
                           p.price
                       }).ToList();

edited Jul 7, 2014 at 9:40

answered Jul 7, 2014 at 8:37

Christos

54.1k9 gold badges82 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashish Rajput · Accepted Answer · 2014-07-07 09:47:18Z

Instead of fetching all the data into memory and then join it's better to make navigation properties and fetch the data by these properties. In this scenario all the join would work inside database and you will get only filtered data from database. Solution would work like

class Category
{
  public int CategoryId { get; set; }
  public string Description{ get; set; }
  public string FilePath {get;set;}
  public string ItemName {get;set;}

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

class Product
{
   public int ProductId { get; set; }
   public string name{ get; set; }
   public int CategoryId { get; set; }
   public string Product.image_url {get;set;}
   public int price {get;set;}

   public virtual Category Category{ get; set; }
}

Now you just need to call the following query

datalist.DataSource = (from p in objCategory.Include("Product")                            
                         select new
                         {
                             p.FilePath,
                             p.ItemName,
                             p.Description,
                             p.Product.image_url,
                             p.Product.name,
                             p.Product.price
                         }).ToList();

Collectives™ on Stack Overflow

Join Tables in Linq using lambda expression

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related