Answered by:
Can't do count(distinct x) from LINQ and Group By ?
Question

Hi,
Here is my code.
Dim query = From C In Commands
Group By Commands = C.command Into G = Group,
Late = Count(C.Status.Equals("Late")), Warning = Count(C.Status.Equals("Warning")), InTime = Count(C.Status.Equals("In Time"))
Select Commands, Late, Warning, InTimeInfo table looks like :
Commands  Command detail  Status  Transport
123  1  In Time  Train
123  2  Late  truck
123  3  In Time  Train
456   Warning  Train
Result of code below is
commands 123  Late 1  Warning 0  intime 2
commands 456  Late 0  Warning 1  intime 0
now I'd like to count distinct, for a command, how many transport type are used. to say transport type = "train" or "multiple"
in SQL this could look like this
select command, status, count (distinct transport) as used_transport from commands group by commands
How do I write my linq query to do that, included in the one I wrote at beginning ?
Thx
Answers

The "straightforward" LINQ is as follows. I put straightfoward in quotes because this is a situation where SQL is more convenient. This is true of LINQ and SQL: each has cases where it is easier to use than the other.
Dim query = From C In Commands _ Group By Commands = C.command Into G = Group _ Select New With { _ .Commands = Commands, _ .Late = (From x In G Where x.Status = "Late" Select x Distinct).Count(), _ .Warning = (From x In G Where x.Status = "Warning" Select x Distinct).Count(), _ .InTime = (From x In G Where x.Status = "In Time" Select x Distinct).Count()}

Hi, Thx for answer and knowledge,
My final working LINQ query :
Dim query3 = From Q In query Group By rafale = Q.Num_Rafale Into G = Group Select New With {.rafale = rafale, .late = (From x In G Where x.Statut = "Late" Select x).Count(), .warning = (From x In G Where x.Statut = "Warning" Select x).Count(), .Intime = (From x In G Where x.Statut = "In Time" Select x).Count(), .trs = (From x In G Select x.Code_Trs Distinct).Count(), .commande = (From x In G Select x.Commande Distinct).Count() }
I count how many warning/late/intime for a "rafale" that is a group of "commande"
how many "commande" in a "rafale", and of course, how many distinct "transport" as .trs are used.
Thanks for help and ... teach ?
 Marked as answer by Nemric Tuesday, June 15, 2010 8:00 AM
All replies

The "straightforward" LINQ is as follows. I put straightfoward in quotes because this is a situation where SQL is more convenient. This is true of LINQ and SQL: each has cases where it is easier to use than the other.
Dim query = From C In Commands _ Group By Commands = C.command Into G = Group _ Select New With { _ .Commands = Commands, _ .Late = (From x In G Where x.Status = "Late" Select x Distinct).Count(), _ .Warning = (From x In G Where x.Status = "Warning" Select x Distinct).Count(), _ .InTime = (From x In G Where x.Status = "In Time" Select x Distinct).Count()}

Hi, Thx for answer and knowledge,
My final working LINQ query :
Dim query3 = From Q In query Group By rafale = Q.Num_Rafale Into G = Group Select New With {.rafale = rafale, .late = (From x In G Where x.Statut = "Late" Select x).Count(), .warning = (From x In G Where x.Statut = "Warning" Select x).Count(), .Intime = (From x In G Where x.Statut = "In Time" Select x).Count(), .trs = (From x In G Select x.Code_Trs Distinct).Count(), .commande = (From x In G Select x.Commande Distinct).Count() }
I count how many warning/late/intime for a "rafale" that is a group of "commande"
how many "commande" in a "rafale", and of course, how many distinct "transport" as .trs are used.
Thanks for help and ... teach ?
 Marked as answer by Nemric Tuesday, June 15, 2010 8:00 AM