Filtering data in LINQ

Question

I have an observable collection which would be bound to the silverlight datagrid, where i need to display a particular row based on the data present in the OC

ID   Name   Status   Desc   Role
--------------------------------
1    ABC    500      des     50
1    ABC    500      des     55
2    XYZ    502      des     57

in the above table there are duplicate values, i need to filter them in such a way that when (status = 500) i need to pick the row which has role as 50. or if the (status = 501) i need to pick the row which has role as 55. In any instant the status would remain same for a particular ID. My final data should look like the one below.

ID   Name   Status   Desc    Role
---------------------------------
1    ABC    500      des     50
2    XYZ    502      des     57

Answer 1

It's not a fun query by any means. There may be a better answer, but this should get you started. The trick here is that you'll need to change your orderby clause to meet your needs. I couldn't tell from your question whether you were trying to pick the min Role value, or were trying to convey something else, but that orderby clause is where your custom logic for picking the right record goes.

var results =
   from a in DataVals
   group a by new {a.ID, a.Name, a.Status, a.Desc} into g
   select new {
      g.Key.ID,
      g.Key.Name,
      g.Key.Status,
      g.Key.Desc,
      Role = (
         from b in DataVals
         where b.ID == g.Key.ID
         && b.Name == g.Key.Name
         && b.Status == g.Key.Status
         && b.Desc == g.Key.Desc
         orderby b.Role
         select b.Role
      ).Take(1).FirstOrDefault()
   };