CODEWITHSUNDEEP
linq
subash
subash

Reputation: 4137

get distinct with min and maximum in linq query

i have a table like below

Name Entrytime    Exittime
A    10:30 AM     6:30PM    
A    7:30 AM      5:30PM
B    10:30 AM     2:30PM
A    5:30 AM      3:30PM
B    11:30 AM     4:30PM
A    8:30 AM     7:30PM
C    9:30 AM      1:30PM
C    10:30 AM     9:30PM

i need to get a record minimum entry time for each Name and maximum exittime for eachname using linq query

so i need resultset like below

Name Entrytime    Exittime
A    5:30 AM      7:30PM    
B    10:30 AM     4:30PM
C    9:30 AM      9:30PM

Upvotes: 0

Views: 448

Answers (2)

Robert McKee
Robert McKee

Reputation: 21487

Table
    .GroupBy(x=>x.Name)
    .Select(x=>new {Name=x.Key,
            Entrytime=x.Min(m=>m.Entrytime),
            Exittime=x.Max(m=>m.Exittime)})

Here's a LinqPad query showing how to do it on an IEnumerable when Entrytime and Exittime are strings instead of a datetime:

var list= new[]{
new {Name="A",Entrytime="10:30 AM",Exittime="6:30PM"},
new {Name="A",Entrytime="7:30 AM",Exittime="5:30PM"},
new {Name="B",Entrytime="10:30 AM",Exittime="2:30PM"},
new {Name="A",Entrytime="5:30 AM",Exittime="3:30PM"},
new {Name="B",Entrytime="11:30 AM",Exittime="4:30PM"},
new {Name="A",Entrytime="8:30 AM",Exittime="7:30PM"},
new {Name="C",Entrytime="9:30 AM",Exittime="1:30PM"},
new {Name="C",Entrytime="10:30 AM",Exittime="9:30PM"}};

var result=list.GroupBy(x=>x.Name)
    .Select(x=>new {Name=x.Key,
            Entrytime=x.Min(m=>DateTime.Parse(m.Entrytime)).ToString("h:mm tt"),
            Exittime=x.Max(m=>DateTime.Parse(m.Exittime)).ToString("h:mm tt")});

result.Dump();

Upvotes: 3

Kevin Nacios
Kevin Nacios

Reputation: 2853

from t in table
group t by t.name into tgroup
select new 
{ 
    name = tgroup.Key, 
    entrytime = tgroup.Min(x => x.entrytime), 
    exittime = tgroup.Max(x => x.exittime)
}

Upvotes: 0

Related Questions