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

  • Question

  • User-1123701243 posted

    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

    Monday, February 22, 2021 7:03 PM

Answers

  • User1686398519 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 23, 2021 5:41 AM

All replies

  • User-474980206 posted

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

    Monday, February 22, 2021 9:14 PM
  • User1686398519 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 23, 2021 5:41 AM
  • User-1123701243 posted

    Great Man

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

    Thanks Thanks YihuiSun 

    Tuesday, February 23, 2021 4:41 PM