Realizar combinaciones internasPerform inner joins

En términos de la base de datos relacional, una combinación interna genera un conjunto de resultados en el que cada elemento de la primera colección aparece una vez para cada elemento coincidente en la segunda colección.In relational database terms, an inner join produces a result set in which each element of the first collection appears one time for every matching element in the second collection. Si un elemento de la primera colección no tiene ningún elemento coincidente, no aparece en el conjunto de resultados.If an element in the first collection has no matching elements, it does not appear in the result set. El método Join, que se llama mediante la cláusula join de C#, implementa una combinación interna.The Join method, which is called by the join clause in C#, implements an inner join.

En este artículo se muestra cómo realizar cuatro variaciones de una combinación interna:This article shows you how to perform four variations of an inner join:

  • Una combinación interna simple que correlaciona elementos de dos orígenes de datos según una clave simple.A simple inner join that correlates elements from two data sources based on a simple key.

  • Una combinación interna que correlaciona elementos de dos orígenes de datos según una clave compuesta.An inner join that correlates elements from two data sources based on a composite key. Una clave compuesta, que es una clave formada por más de un valor, permite correlacionar elementos en función de más de una propiedad.A composite key, which is a key that consists of more than one value, enables you to correlate elements based on more than one property.

  • Una combinación múltiple en la que las sucesivas operaciones de combinación se anexan entre sí.A multiple join in which successive join operations are appended to each other.

  • Una combinación interna que se implementa mediante una combinación agrupada.An inner join that is implemented by using a group join.

Ejemplo: Combinación de clave simpleExample - Simple key join

En el ejemplo siguiente se crean dos colecciones que contienen objetos de dos tipos definidos por el usuario, Person y Pet.The following example creates two collections that contain objects of two user-defined types, Person and Pet. La consulta usa la cláusula join de C# para emparejar objetos Person con objetos Pet cuyo Owner sea ese Person.The query uses the join clause in C# to match Person objects with Pet objects whose Owner is that Person. La cláusula select de C# define el aspecto que tendrán los objetos resultantes.The select clause in C# defines how the resulting objects will look. En este ejemplo, los objetos resultantes son tipos anónimos que consisten en el nombre de propietario y el nombre de mascota.In this example the resulting objects are anonymous types that consist of the owner's first name and the pet's name.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

/// <summary>
/// Simple inner join.
/// </summary>
public static void InnerJoinExample()
{
    Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
    Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
    Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
    Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
    Person rui = new Person { FirstName = "Rui", LastName = "Raposo" };

    Pet barley = new Pet { Name = "Barley", Owner = terry };
    Pet boots = new Pet { Name = "Boots", Owner = terry };
    Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
    Pet bluemoon = new Pet { Name = "Blue Moon", Owner = rui };
    Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

    // Create two lists.
    List<Person> people = new List<Person> { magnus, terry, charlotte, arlene, rui };
    List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

    // Create a collection of person-pet pairs. Each element in the collection
    // is an anonymous type containing both the person's name and their pet's name.
    var query = from person in people
                join pet in pets on person equals pet.Owner
                select new { OwnerName = person.FirstName, PetName = pet.Name };

    foreach (var ownerAndPet in query)
    {
        Console.WriteLine($"\"{ownerAndPet.PetName}\" is owned by {ownerAndPet.OwnerName}");
    }
}

// This code produces the following output:
//
// "Daisy" is owned by Magnus
// "Barley" is owned by Terry
// "Boots" is owned by Terry
// "Whiskers" is owned by Charlotte
// "Blue Moon" is owned by Rui

Tenga en cuenta que el objeto Person cuyo LastName es "Huff" no aparece en el conjunto de resultados porque no hay ningún objeto Pet que tenga Pet.Owner igual que Person.Note that the Person object whose LastName is "Huff" does not appear in the result set because there is no Pet object that has Pet.Owner equal to that Person.

Ejemplo: Combinación de clave compuestaExample - Composite key join

En lugar de correlacionar elementos en función de una sola propiedad, puede usar una clave compuesta para comparar elementos según varias propiedades.Instead of correlating elements based on just one property, you can use a composite key to compare elements based on multiple properties. Para ello, especifique la función del selector de claves de cada colección para que devuelva un tipo anónimo que conste de las propiedades que quiere comparar.To do this, specify the key selector function for each collection to return an anonymous type that consists of the properties you want to compare. Si etiqueta las propiedades, deben tener la misma etiqueta de tipo anónimo en cada clave.If you label the properties, they must have the same label in each key's anonymous type. Las propiedades también deben aparecer en el mismo orden.The properties must also appear in the same order.

En el ejemplo siguiente se usa una lista de objetos Employee y una lista de objetos Student para determinar qué empleados son también alumnos.The following example uses a list of Employee objects and a list of Student objects to determine which employees are also students. Estos dos tipos tienen una propiedad FirstName y una propiedad LastName de tipo String.Both of these types have a FirstName and a LastName property of type String. Las funciones que crean las claves de combinación de los elementos de cada lista devuelven un tipo anónimo formado por las propiedades FirstName y LastName de cada elemento.The functions that create the join keys from each list's elements return an anonymous type that consists of the FirstName and LastName properties of each element. La operación de combinación compara la igualdad de estas claves compuestas y devuelve pares de objetos de cada lista en los que el nombre y el apellido coinciden.The join operation compares these composite keys for equality and returns pairs of objects from each list where both the first name and the last name match.

class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int EmployeeID { get; set; }
}

class Student
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int StudentID { get; set; }
}

/// <summary>
/// Performs a join operation using a composite key.
/// </summary>
public static void CompositeKeyJoinExample()
{
    // Create a list of employees.
    List<Employee> employees = new List<Employee> {
        new Employee { FirstName = "Terry", LastName = "Adams", EmployeeID = 522459 },
         new Employee { FirstName = "Charlotte", LastName = "Weiss", EmployeeID = 204467 },
         new Employee { FirstName = "Magnus", LastName = "Hedland", EmployeeID = 866200 },
         new Employee { FirstName = "Vernette", LastName = "Price", EmployeeID = 437139 } };

    // Create a list of students.
    List<Student> students = new List<Student> {
        new Student { FirstName = "Vernette", LastName = "Price", StudentID = 9562 },
        new Student { FirstName = "Terry", LastName = "Earls", StudentID = 9870 },
        new Student { FirstName = "Terry", LastName = "Adams", StudentID = 9913 } };

    // Join the two data sources based on a composite key consisting of first and last name,
    // to determine which employees are also students.
    IEnumerable<string> query = from employee in employees
                                join student in students
                                on new { employee.FirstName, employee.LastName }
                                equals new { student.FirstName, student.LastName }
                                select employee.FirstName + " " + employee.LastName;

    Console.WriteLine("The following people are both employees and students:");
    foreach (string name in query)
        Console.WriteLine(name);
}

// This code produces the following output:
//
// The following people are both employees and students:
// Terry Adams
// Vernette Price

Ejemplo: Combinación múltipleExample - Multiple join

Se puede anexar cualquier número de operaciones de combinación entre sí para realizar una combinación múltiple.Any number of join operations can be appended to each other to perform a multiple join. Cada cláusula join de C# correlaciona un origen de datos especificado con los resultados de la combinación anterior.Each join clause in C# correlates a specified data source with the results of the previous join.

En el ejemplo siguiente se crean tres colecciones: una lista de objetos Person, una lista de objetos Cat y una lista de objetos Dog.The following example creates three collections: a list of Person objects, a list of Cat objects, and a list of Dog objects.

La primera cláusula join de C# empareja personas y gatos según un objeto Person que coincida con Cat.Owner.The first join clause in C# matches people and cats based on a Person object matching Cat.Owner. Devuelve una secuencia de tipos anónimos que contienen el objeto Person y Cat.Name.It returns a sequence of anonymous types that contain the Person object and Cat.Name.

La segunda cláusula join de C# correlaciona los tipos anónimos devueltos por la primera combinación con objetos Dog de la lista de perros proporcionada, según una clave compuesta formada por la propiedad Owner de tipo Person y la primera letra del nombre del animal.The second join clause in C# correlates the anonymous types returned by the first join with Dog objects in the supplied list of dogs, based on a composite key that consists of the Owner property of type Person, and the first letter of the animal's name. Devuelve una secuencia de tipos anónimos que contienen las propiedades Cat.Name y Dog.Name de cada par coincidente.It returns a sequence of anonymous types that contain the Cat.Name and Dog.Name properties from each matching pair. Como se trata de una combinación interna, solo se devuelven los objetos del primer origen de datos que tienen una correspondencia en el segundo origen de datos.Because this is an inner join, only those objects from the first data source that have a match in the second data source are returned.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

class Cat : Pet
{ }

class Dog : Pet
{ }

public static void MultipleJoinExample()
{
    Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
    Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
    Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
    Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
    Person rui = new Person { FirstName = "Rui", LastName = "Raposo" };
    Person phyllis = new Person { FirstName = "Phyllis", LastName = "Harris" };

    Cat barley = new Cat { Name = "Barley", Owner = terry };
    Cat boots = new Cat { Name = "Boots", Owner = terry };
    Cat whiskers = new Cat { Name = "Whiskers", Owner = charlotte };
    Cat bluemoon = new Cat { Name = "Blue Moon", Owner = rui };
    Cat daisy = new Cat { Name = "Daisy", Owner = magnus };

    Dog fourwheeldrive = new Dog { Name = "Four Wheel Drive", Owner = phyllis };
    Dog duke = new Dog { Name = "Duke", Owner = magnus };
    Dog denim = new Dog { Name = "Denim", Owner = terry };
    Dog wiley = new Dog { Name = "Wiley", Owner = charlotte };
    Dog snoopy = new Dog { Name = "Snoopy", Owner = rui };
    Dog snickers = new Dog { Name = "Snickers", Owner = arlene };

    // Create three lists.
    List<Person> people =
        new List<Person> { magnus, terry, charlotte, arlene, rui, phyllis };
    List<Cat> cats =
        new List<Cat> { barley, boots, whiskers, bluemoon, daisy };
    List<Dog> dogs =
        new List<Dog> { fourwheeldrive, duke, denim, wiley, snoopy, snickers };

    // The first join matches Person and Cat.Owner from the list of people and
    // cats, based on a common Person. The second join matches dogs whose names start
    // with the same letter as the cats that have the same owner.
    var query = from person in people
                join cat in cats on person equals cat.Owner
                join dog in dogs on 
                new { Owner = person, Letter = cat.Name.Substring(0, 1) }
                equals new { dog.Owner, Letter = dog.Name.Substring(0, 1) }
                select new { CatName = cat.Name, DogName = dog.Name };

    foreach (var obj in query)
    {
        Console.WriteLine( 
            $"The cat \"{obj.CatName}\" shares a house, and the first letter of their name,
            with \"{obj.DogName}\".");  
    }
}

// This code produces the following output:
//
// The cat "Daisy" shares a house, and the first letter of their name, with "Duke".
// The cat "Whiskers" shares a house, and the first letter of their name, with "Wiley".

Ejemplo: Combinación interna mediante combinación agrupadaExample - Inner join by using grouped join

El ejemplo siguiente muestra cómo implementar una combinación interna mediante una combinación agrupada.The following example shows you how to implement an inner join by using a group join.

En query1, la lista de objetos Person forma una combinación agrupada con la lista de objetos Pet según el Person que coincide con la propiedad Pet.Owner.In query1, the list of Person objects is group-joined to the list of Pet objects based on the Person matching the Pet.Owner property. La combinación agrupada crea una colección de grupos intermedios, donde cada grupo consta de un objeto Person y una secuencia de objetos Pet coincidentes.The group join creates a collection of intermediate groups, where each group consists of a Person object and a sequence of matching Pet objects.

Al agregar una segunda cláusula from a la consulta, esta secuencia de secuencias se combina (se acopla) en una secuencia mayor.By adding a second from clause to the query, this sequence of sequences is combined (or flattened) into one longer sequence. El tipo de los elementos de la secuencia final se especifica con la cláusula select.The type of the elements of the final sequence is specified by the select clause. En este ejemplo, ese tipo es un tipo anónimo que consta de las propiedades Person.FirstName y Pet.Name de cada par coincidente.In this example, that type is an anonymous type that consists of the Person.FirstName and Pet.Name properties for each matching pair.

El resultado de query1 es equivalente al conjunto de resultados que se habría obtenido con la cláusula join sin la cláusula into para realizar una combinación interna.The result of query1 is equivalent to the result set that would have been obtained by using the join clause without the into clause to perform an inner join. La variable query2 muestra esta consulta equivalente.The query2 variable demonstrates this equivalent query.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

/// <summary>
/// Performs an inner join by using GroupJoin().
/// </summary>
public static void InnerGroupJoinExample()
{
    Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
    Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
    Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
    Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

    Pet barley = new Pet { Name = "Barley", Owner = terry };
    Pet boots = new Pet { Name = "Boots", Owner = terry };
    Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
    Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
    Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

    // Create two lists.
    List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
    List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

    var query1 = from person in people
                 join pet in pets on person equals pet.Owner into gj
                 from subpet in gj
                 select new { OwnerName = person.FirstName, PetName = subpet.Name };

    Console.WriteLine("Inner join using GroupJoin():");
    foreach (var v in query1)
    {
        Console.WriteLine($"{v.OwnerName} - {v.PetName}"));
    }

    var query2 = from person in people
                 join pet in pets on person equals pet.Owner
                 select new { OwnerName = person.FirstName, PetName = pet.Name };
        
    Console.WriteLine("\nThe equivalent operation using Join():");
    foreach (var v in query2)
        Console.WriteLine($"{v.OwnerName} - {v.PetName}"));
}

// This code produces the following output:
//
// Inner join using GroupJoin():
// Magnus - Daisy
// Terry - Barley
// Terry - Boots
// Terry - Blue Moon
// Charlotte - Whiskers
//
// The equivalent operation using Join():
// Magnus - Daisy
// Terry - Barley
// Terry - Boots
// Terry - Blue Moon
// Charlotte - Whiskers

Vea tambiénSee also