CODEWITHSUNDEEP
c#linq-to-sql
Solo
Solo

Reputation: 579

Linq to SQL in C# Unique data

List problem

I have a long list with ships that I get from a Linq to SQL query, but I only want one row per ImoNo. Today i have about 4 rows per ImoNo. I just need the one row that has been last updated (so in this example I need 2013-01-27).

This is my Linq To SQL query:

var res = from positions in context.Lloyds_ETAs
          join vessels in context.Lloyds_Vessels on positions.ImoNumber equals vessels.imo_no
           select new PositionData {
              ImoNo = positions.ImoNumber,
              PositionCordinates = positions.AIS_Latest_Position,
              CompassOverGround = positions.Compass_over_Ground_Heading,
              VesselId = positions.Vessel_ID,
              Equipment = vessels.vessel_type,
              Updated = positions.Last_Place_Location
           };

return res.ToList();

Upvotes: 0

Views: 111

Answers (4)

david.s
david.s

Reputation: 11403

var res = (from positions in context.Lloyds_ETAs
          join vessels in context.Lloyds_Vessels on positions.ImoNumber equals vessels.imo_no
          select new PositionData {
              ImoNo = positions.ImoNumber,
              PositionCordinates = positions.AIS_Latest_Position,
              CompassOverGround = positions.Compass_over_Ground_Heading,
              VesselId = positions.Vessel_ID,
              Equipment = vessels.vessel_type,
              Updated = positions.Last_Place_Location
           })
           .GroupBy(x => x.ImoNo)
           .Select(g => g.OrderByDescending(pd => pd.Updated).First());

Upvotes: 2

NominSim
NominSim

Reputation: 8511

There are several ways to get just one "row" as a result: 

res.OrderByDescending(x => x.Updated).Take(1);
res.OrderByDescending(x => x.Updated).First();
res.Order(x => x.Updated).Last();

It seems that you have some duplication though, so maybe doing a group by would be more appropriate.

Upvotes: 0

dead_ant
dead_ant

Reputation: 125

(yourQuery).OrderByDescending(pd=>pd.Updated).First()

Upvotes: 0

Roman Royter
Roman Royter

Reputation: 1665

If you want the last one, all you have to do is append .OrderBy(pd => pd.Updated).Last() after your select.

var res = (from positions in context.Lloyds_ETAs
          join vessels in context.Lloyds_Vessels on positions.ImoNumber equals vessels.imo_no
           select new PositionData {
              ImoNo = positions.ImoNumber,
              PositionCordinates = positions.AIS_Latest_Position,
              CompassOverGround = positions.Compass_over_Ground_Heading,
              VesselId = positions.Vessel_ID,
              Equipment = vessels.vessel_type,
              Updated = positions.Last_Place_Location
           }).OrderBy(pd => pd.Updated).Last();

return res.ToList();

Upvotes: 0

Related Questions