Click here to Skip to main content
15,888,283 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two table :

Table 1 : Items
SQL
ItemsId  Name_En   
   1      Cream
   2      Oil
   3      Detergent
   4      Soap

Table 2 : ItemsCommon

SQL
ItemsCommonId  ItemsId    Name_En         CategoryId
  1                1        Face Cream          3
  2                2        Head Oil            3
  3                1        Foot Cream          4
  4                2        Body Oil            4


Now what I want if user search items by categoryid = 3 then it will show result like :
SQL
ItemsId   Name_En   
   1      Face Cream
   2      Head Oil
   3      Detergent
   4      Soap


or search items by categoryid = 4 then result will be :

SQL
ItemsId   Name_En
   1      Foot Cream
   2      Body Oil
   3      Detergent
   4      Soap


but if user search by other categoryid except 3, 4 then result will be same as Items table :
SQL
ItemsId  Name_En   
   1      Cream
   2      Oil
   3      Detergent
   4      Soap


I try this method..

C#
public List<Items> SearchItemsByCategory(int categoryid )
      {
          var items= (from i in db.Items
                      join ic in db.ItemsCommon on i.ItemsId equals ic.ItemsCommonId
                      into tempJoin
                      from t2 in tempJoin.DefaultIfEmpty()
                      select new { t2.Id,t2.Name,t2.ItemsId,t2.CategoryId }).ToList();
          return items;

         // var result = casePartyRoles.Where(c=>c.)
      }


Any suggestion really appreaciated !
Posted
Updated 6-Aug-14 23:32pm
v2

1 solution

I solve it by self !<br />

C#
public List<items> SearchItemsByCategory(int categoryid )
    {
        var items= (from i in db.Items
                    join ic in db.ItemsCommon.Where(x=>x.CategoryId == categoryid )
                     on i.ItemsId equals ic.ItemsId 
                    into tempJoin
                    from t2 in tempJoin.DefaultIfEmpty()
                    select new { i.ItemsId,Name_En  = t2==null?i.Name_En: t2.Name_En }).ToList();
        return items;

       // var result = casePartyRoles.Where(c=>c.)
    }</items>
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900