Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Runtime.CompilerServices

Public NotInheritable Class CPais
  Public Property Codigo() As String
  Public Property Nombre() As String
End Class

Public NotInheritable Class CPersona
  Public Property Nombre() As String
  Public Property FechaNac() As DateTime
  Public Property PaisNac() As String
End Class

Module Test

  Sub Main()

    Dim listPais = New List(Of CPais)()
    listPais.AddRange(New CPais() { _
      New CPais With { _
        .Codigo = "ES", .Nombre = "España"}, _
      New CPais With { _
        .Codigo = "EN", .Nombre = "Inglaterra"}, _
      New CPais With { _
        .Codigo = "FR", .Nombre = "Francia"}, _
      New CPais With { _
        .Codigo = "US", .Nombre = "Estados Unidos"} _
     })

    Dim listPersona = New List(Of CPersona)()
    listPersona.AddRange(New CPersona() { _
      New CPersona With { _
        .Nombre = "Isabella", .FechaNac = New DateTime(2011, 11, 7), .PaisNac = "US"}, _
      New CPersona With { _
        .Nombre = "Manuel", .FechaNac = New DateTime(1991, 9, 21), .PaisNac = "ES"}, _
      New CPersona With { _
        .Nombre = "Javier", .FechaNac = New DateTime(1990, 7, 2), .PaisNac = "ES"}, _
      New CPersona With { _
        .Nombre = "María", .FechaNac = New DateTime(1991, 9, 1), .PaisNac = "EN"} _
     })

    'Dim personas1990 = From p In listPersona _
    '    Where p.FechaNac.Year = 1990 _
    '    Order By p.Nombre _
    '    Select p.Nombre
    'For Each persona In personas1990
    '  Console.WriteLine(persona)
    'Next

    Dim personas1990 = From p In listPersona _
        Where p.FechaNac.Year = 1990 _
        Order By p.Nombre _
        Select New With {.Nombre = p.Nombre}

    'Dim personas1990 = listPersona. _
    '    Where(Function(p) p.FechaNac.Year = 1990). _
    '    OrderBy(Function(p) p.Nombre). _
    '    Select(Function(p) New With {.Nombre = p.Nombre})

    For Each persona In personas1990
      Console.WriteLine(persona.Nombre)
    Next

    ' Cláusula group
    Console.WriteLine(Environment.NewLine & "Group:")
    Dim personasPorAño = From p In listPersona _
        Group p By p.FechaNac.Year Into gr = Group

    For Each grupoPersonas In personasPorAño
      Console.WriteLine(grupoPersonas.Year)
      For Each persona In grupoPersonas.gr
        Console.WriteLine(" {0}", persona.Nombre)
      Next
    Next

    ' Cláusula group
    Console.WriteLine(Environment.NewLine & "Group:")
    Dim personasPais = From pais In listPais _
        Join pers In listPersona On pais.Codigo Equals pers.PaisNac _
        Group New With {.Nombre = pers.Nombre} By pais.Nombre Into gr = Group

    For Each grupoPerPais In personasPais
      Console.WriteLine(grupoPerPais.Nombre) ' nombre país
      For Each persona In grupoPerPais.gr
        Console.WriteLine(" {0}", persona.Nombre)
      Next
    Next

    ' Producto cartesiano:
    'Console.WriteLine(Environment.NewLine & "Producto cartesiano:")
    'Dim productoCartesiano = From pais In listPais _
    '    From pers In listPersona _
    '    Select New With {.NomPais = pais.Nombre, .NomPers = pers.Nombre}

    ' Producto cartesiano:
    Console.WriteLine(Environment.NewLine & "Producto cartesiano:")
    Dim productoCartesiano = From pais In listPais _
        From pers In listPersona _
        Where pais.Codigo = pers.PaisNac _
        Select New With {.NomPers = pers.Nombre, .NomPais = pais.Nombre}

    For Each elem In productoCartesiano
      Console.WriteLine("{0} {1}", elem.NomPers, elem.NomPais)
    Next

    ' Cláusula Join
    Console.WriteLine(Environment.NewLine & "Join:")
    Dim combinacion = From pais In listPais _
        Join pers In listPersona On pais.Codigo Equals pers.PaisNac _
        Select New With {.NomPers = pers.Nombre, .NomPais = pais.Nombre}

    For Each elem In combinacion
      Console.WriteLine("{0} {1}", elem.NomPers, elem.NomPais)
    Next

    ' Cláusula Into
    Console.WriteLine(Environment.NewLine & "Into:")
    Dim persNacidasPorAño = From p In listPersona _
        Group p By p.FechaNac.Year Into grupoPersAño = Group _
        Where grupoPersAño.Count() >= 2 _
        Select New With {.Año = Year, .PorAño = grupoPersAño.Count()}

    For Each persona In persNacidasPorAño
      Console.WriteLine("En {0} nacieron {1} o más personas.", persona.Año, persona.PorAño)
    Next

    ' Cláusula Into con Group
    Console.WriteLine(Environment.NewLine & "Into con Group:")
    Dim persPais = From pais In listPais _
        Join pers In listPersona On pais.Codigo Equals pers.PaisNac _
        Group New With {.Nombre = pers.Nombre} By pais.Nombre Into persPaisOrd = Group _
        Order By Nombre _
        Select persPaisOrd

    For Each grupoPerPais In personasPais
      Console.WriteLine(grupoPerPais.Nombre) ' nombre país
      For Each persona In grupoPerPais.gr
        Console.WriteLine(" {0}", persona.Nombre)
      Next
    Next

    ' Cláusula Into con Group
    Console.WriteLine(Environment.NewLine & "Into con Group:")
    Dim PaisNumPers = From pais In listPais _
        Order By pais.Nombre _
        Join pers In listPersona On pais.Codigo Equals pers.PaisNac _
        Group By pais.Nombre Into grupoPais = Group _
        Select New With {.NomPais = Nombre, .NumPers = grupoPais.Count()}

    For Each pais In PaisNumPers
      Console.WriteLine("En {0} hay {1} personas.", pais.NomPais, pais.NumPers)
    Next

    ' Cláusula Let
    Console.WriteLine(Environment.NewLine & "Let:")
    Dim unMes As Integer = 9, unAño As Integer = 1991
    Dim persPaisAñoMes = From pais In listPais _
        Order By pais.Nombre _
        Join pers In listPersona On pais.Codigo Equals pers.PaisNac _
        Let mesNac = pers.FechaNac.Month _
        Let añoNac = pers.FechaNac.Year _
        Where añoNac = unAño AndAlso mesNac = unMes _
        Group New With {.Nombre = pers.Nombre} By pais.Nombre Into perPais = Group

    Console.WriteLine("Nacidos en el mes {0} del año {1}:", unMes, unAño)
    For Each grupoPerPais In persPaisAñoMes
      Console.WriteLine(grupoPerPais.Nombre)
      For Each persona In grupoPerPais.perPais
        Console.WriteLine(" {0}", persona.Nombre)
      Next
    Next
  End Sub
End Module
