Filter Data based on priority in LINQ

Question

The fields in my Rate Table are Route, VehicleMasterId, VehicleType, Material, Client, UnitRate etc.

The priority order on which I have to fetch a single row is : VehicleNo > Route > Client > VehicleType, Material

Suppose I have 2 rows with same data except 1 has Client and Vehicle Type and the other one has VehicleNo.Then based on my priority, I should pick the rate of the row with VehicleNo.

To excute this In linq I have first picked all the rows with matching data. Here is my code.

public RateMasterDataModel GetRateMasterforCn(Consignment cn){

// I will always pass all the above fields in cn

var rateMaster = (from rate in Context.RateMaster
                  where rate.FromDate <= cn.Cndate
                  && rate.ToDate >= cn.Cndate
                  && (rate.VehicleTypeId != null ? rate.VehicleTypeId == cn.VehicleTypeId : true)
                  && (rate.VehicleMasterId != null ? rate.VehicleMasterId == cn.VehicleMasterId : true)
                  && (rate.ClientId != null ? rate.ClientId == cn.ClientId : true)
                  && (rate.RouteId != null ? rate.RouteId == cn.RouteId : true)
                  && (rate.MaterialMasterId != null ? rate.MaterialMasterId == cn.MaterialMasterId : true)
                          select new RateMasterDataModel
                          {
                              RateMasterId = rate.RateMasterId,
                              FromDate = rate.FromDate,
                              ToDate = rate.ToDate,
                              ClientId = rate.ClientId ,
                              VehicleMasterId = rate.VehicleMasterId,
                              VehicleTypeId = rate.VehicleTypeId,
                              MaterialMasterId = rate.MaterialMasterId,
                              UnitRate = rate.UnitRate,
                              LoadTypeId = rate.LoadTypeId,
                              LoadingPointId = rate.RouteId,
                              CalculationMasterId = rate.CalculationMasterId
                          }).ToList();
}

Please suggest how to filter after this.

Do you wanna use .OrderBy()? [orderBy](https://stackoverflow.com/questions/298725/multiple-order-by-in-linq?rq=1) Not clear enough. — hastrb, Mar 15 '18 at 12:26
I need to fetch only 1 row, the one which has higher priority field — Adrita Sharma, Mar 15 '18 at 12:31
Then use [First, FirstOrDefault](https://stackoverflow.com/questions/11817882/select-only-first-object-in-linq). How do you work with your priority? I tend to think you probably want to filter some rows in each sub-set like when we are working with groups .GroupBy() [GroupBy](https://stackoverflow.com/questions/847066/group-by-multiple-columns) — hastrb, Mar 15 '18 at 12:33
FirstOrDefault is picking randomly. Priority won't work with FirstOrDefault. To filter, I have tried using multiple if else, I am sure there must be a better way of doing this. — Adrita Sharma, Mar 15 '18 at 12:38
What's the problem to use both of them: .First() and multiple .OrderBy to sort dataset according to your priorities? The first .OrderBy() will be intended for the highest priority field. — hastrb, Mar 15 '18 at 12:51
I have not tried it. Will try using both order by. And FirstOrDefault. Thanks for the suggestion. — Adrita Sharma, Mar 15 '18 at 12:57
Assuming you are pulling this from a database, you should add the `order`...`by` clause and the `FirstOrDefault` (or `LastOrDefault`) to the query (before `ToList`) so the SQL server will do the work. Note whether `NULL` is first or last can depend on your SQL Server. — NetMage, Mar 15 '18 at 23:45

score 1 · Accepted Answer · answered Mar 17 '18 at 07:40

1

You can use below code to get records ordered by VehicleNo > Route

.OrderBy(v=>v.VehicleNo).ThenBy(r=>r.RouteId)

Add multiple .ThenBy() clause as per your column requirement for sorting the data.

answered Mar 17 '18 at 07:40

Chetan

66
9

.OrderByDescending(v=>v.VehicleNo).ThenByDescending(r=>r.RouteId)....FirstOrDefault() worked – Adrita Sharma Mar 19 '18 at 14:42

score 0 · Answer 2 · answered Mar 15 '18 at 12:43

0

You mean to say if the row which doesn't have the vehicalno. filld-up then the row having Route must be selected.is it correct?

answered Mar 15 '18 at 12:43

krunal patel

41
7

Yes. If a row has vehicle no as well as route, then that would be selected. If 1 row has vehicle no and another row has route, then the one with vehicle no should be selected – Adrita Sharma Mar 15 '18 at 12:52

Filter Data based on priority in LINQ

2 Answers2