CODEWITHSUNDEEP
c#asp.netsqllinqlambda
techV
techV

Reputation: 935

Join Tables in Linq using lambda expression

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();

Upvotes: 0

Views: 892

Answers (2)

Ashish Rajput
Ashish Rajput

Reputation: 1529

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();

Upvotes: 1

Christos
Christos

Reputation: 53958

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();

Upvotes: 2

Related Questions