none
Can't do count(distinct x) from LINQ and Group By ? RRS feed

  • 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, InTime

    Info 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

    Monday, June 14, 2010 4:43 PM

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()}
    • Marked as answer by Nemric Tuesday, June 15, 2010 7:56 AM
    • Unmarked as answer by Nemric Tuesday, June 15, 2010 7:56 AM
    • Marked as answer by Nemric Tuesday, June 15, 2010 1:49 PM
    Tuesday, June 15, 2010 12:22 AM
  • 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
    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()}
    • Marked as answer by Nemric Tuesday, June 15, 2010 7:56 AM
    • Unmarked as answer by Nemric Tuesday, June 15, 2010 7:56 AM
    • Marked as answer by Nemric Tuesday, June 15, 2010 1:49 PM
    Tuesday, June 15, 2010 12:22 AM
  • 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
    Tuesday, June 15, 2010 8:00 AM