I have an SQL query that I created for a tool a while ago, and I am updating the tool in MVC and using LINQ to Entities.
I can’t understand how to sort my list of brands by weighing my cars according to a person’s hours and their test value.
Here's the SQL query that I had in the old tool:
SELECT Brand.ID, SUM(Car.EstManHours) - SUM(Car.EstManHours) * CAST(AVG(1.00 * TestingStatus.Value) AS DECIMAL(9 , 2)) / 100 AS Weighting FROM TestingStatus INNER JOIN Car ON TestingStatus.ID = Car.StatusID INNER JOIN Team ON Car.TeamID = Team.TeamID RIGHT OUTER JOIN Brand LEFT OUTER JOIN SubCategory ON Brand.ID = SubCategory.BrandID ON Car.SubCategoryID = SubCategory.ID WHERE (Car.IsPunted == 'False') GROUP BY Brand.YearID, Brand.FeatID HAVING (Brand.YearID = @BrandYearID) ORDER BY Weighting DESC
I tried this, but if I add a descending or ascending order, it doesn't actually change in the list, it keeps sorting by Id:
var brands = (from b in _context.Brands join s in _context.SubCategorys on f.Id equals s.BrandId join c in _context.Cars on s.Id equals c.SubCategoryId where (f.YearId == yearId && c.IsPunted == false) orderby (c.ManHoursEst - (c.ManHoursEst * c.TestingStatu.Value / 100)) descending select b).Distinct().ToList();
Thank you for your help in this conversion!
Thanks.
EDIT:
Now I'm trying to get order and group to work properly. The next query is to list tons of duplicates and not sort them properly, because I don't think my weighing is done correctly.
var brands = (from b in _context.Brands join s in _context.SubCategorys on f.Id equals s.BrandId join c in _context.Cars on s.Id equals c.SubCategoryId where (f.YearId == yearId && c.IsPunted == false) let weighting = c.ManHoursEst - (c.ManHoursEst * c.TestingStatu.Value / 100) orderby weighting descending group b by b.Id).SelectMany(x=>x).ToList();
Any ideas?
source share