I am having problem in selecting distinct values with linked tables.[Answered] RSS

• • Reply
  human2x

  Member

  30 Points

  160 Posts

  I am having problem in selecting distinct values with linked tables.

  Feb 22, 2021 07:03 PM|human2x|LINK

  I have a table called Car_Engines it contains (CatNo, Category, SubCatNo, SubCatName) . This table contains thousands of records. The SubCatNo column contains many duplicate records. I want to select distinct records from this table using (SubCatNo) column and join it with other tables (Categories & Sub_Categories).

  I used the following sql statement : 

  select distinct(ce.SubCatNo),ce.[Sub Category] ,
  isc.CatNo, ic.Category
  from Car_Engines ce
  left join [Sub Categories] isc on isc.SubCatNo = ce.SubCatNo
  left join [Categories] ic on isc.CatNo = ic.CatNo
  
  where YearId = 2015 and BrandName = 'Ford' and CarMarkName = 'Edge' and MarkTypeName = 'SEL' 
  and CarEngineId=730
  order by isc.catno,ce.SubCatNo

  The above sql statement is very OK.

  I need to write it in LINQ . I tried the following code but I still need to modify it to use the distinct records of the SubCatNo

          public ActionResult Index()
          {
  
              using (CatalogDB DB = new CatalogDB())
              {
                  List<Car_Engines> ce = DB.Car_Engines.Where(x => x.YearId==2015 && x.CarManufacturerID==1 && x.CarMarkId ==20 
                  && x.CarMarkTypeId ==148 && x.CarEngineId==730).ToList();
  
                  List<Sub_Categories> isc = DB.Sub_Categories.ToList();
                  List<Categories> ic = DB.Categories.ToList();
  
                  var Cats = (from e in ce
                              join s in isc on e.SubCatNo equals s.SubCatNo
                              join c in ic on s.CatNo equals c.CatNo
  
                              //where e.YearId == 2015 && e.CarManufacturerID == 1 && e.CarMarkId == 20 && e.CarMarkTypeId == 148 && e.CarEngineId == 730
  
                              select new CatTreeVM
                              {
                                  CatNo = c.CatNo,
                                  Category = c.Category,
                                  SubCatNo = s.SubCatNo,
                                  Sub_Category = s.Sub_Category
                              }).ToList();
                  return View(Cats);
              }
          }
  

  OR 

  Is there any way to use the above sql statement with the view Model to do the same ??

  Thanks in advance

• • Reply
  bruce (sqlwo...

  All-Star

  58164 Points

  15647 Posts

  Re: I am having problem in selecting distinct values with linked tables.

  Feb 22, 2021 09:14 PM|bruce (sqlwork.com)|LINK

  you should learn sql:

  select distinct(ce.SubCatNo),ce.[Sub Category],isc.CatNo, ic.Category
  from Car_Engines ce
  

  is the same as 

    select distinct ce.SubCatNo,ce.[Sub Category] ,isc.CatNo, ic.Category
    from Car_Engines ce

  or

   

    select distinct (ce.SubCatNo),(ce.[Sub Category]) ,(isc.CatNo), (ic.Category) 
    from Car_Engines ce

  that is it returns distinct rows. to get distinct rows in linq its:

                              select new CatTreeVM
                              {
                                  CatNo = c.CatNo,
                                  Category = c.Category,
                                  SubCatNo = s.SubCatNo,
                                  Sub_Category = s.Sub_Category
                              }).Distinct().ToList();

  bruce (sqlwork.com)
• • Reply
  YihuiSun

  Contributor

  2690 Points

  774 Posts

  Re: I am having problem in selecting distinct values with linked tables.

  Feb 23, 2021 05:41 AM|YihuiSun|LINK

  Hi human2x, 

  You can use the DistinctBy method to find different objects by property.

  • You need to use the Microsoft.Ajax.Utilities package.

  • using Microsoft.Ajax.Utilities;

  I modified it based on the code you provided, you can refer to the following code:

  List<Car_Engines> ce =db.Car_Engines.Where(x => x.YearId == 2015 && x.CarManufacturerID == 1 && x.CarMarkId == 20
      && x.CarMarkTypeId == 148 && x.CarEngineId == 730).ToList();
  List<Sub_Categories> isc = db.Sub_Categories.ToList();
  List<Categories> ic = db.Categories.ToList();
  var Cats1 = ce.Join(isc, e => e.SubCatNo, s => s.SubCatNo, (e, s) => new { e, s })
         .Join(ic, a => a.e.CatNo, c => c.CatNo, (a, c) => new { a, c })
         .Select(m => new CatTreeVM
         {
            CatNo = m.c.CatNo,
            Category = m.c.Category,
            SubCatNo = m.a.s.SubCatNo,
            Sub_Category = m.a.s.SubCatName
         })
         .DistinctBy(m => new { m.SubCatNo, m.CatNo })
         .OrderByDescending(m=>m.CatNo&m.SubCatNo).ToList();
  var Cats2 = ce.Join(isc, e => e.SubCatNo, s => s.SubCatNo, (e, s) => new { e, s })
         .Join(ic, a => a.e.CatNo, c => c.CatNo, (a, c) => new { a, c })
         .Select(m => new CatTreeVM
         {
             CatNo = m.c.CatNo,
             Category = m.c.Category,
             SubCatNo = m.a.s.SubCatNo,
             Sub_Category = m.a.s.SubCatName
         })
         .DistinctBy(m => m.SubCatNo)
         .OrderByDescending(m => m.CatNo & m.SubCatNo).ToList();

  Here is the result. 

  

  Best Regards,

  YihuiSun

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  human2x

  Member

  30 Points

  160 Posts

  Re: I am having problem in selecting distinct values with linked tables.

  Feb 23, 2021 04:41 PM|human2x|LINK

  Great Man

  Thank you too much. Your code solved my issue. It selected the distinct records exactly as I wanted.

  Thanks Thanks YihuiSun