Hace unos años leí un artículo de C. L. Moffatt (link), donde se explicaba de forma clara y concisa los diferentes tipos de joins en SQL, y tomé la idea de escribir un artículo similar pero enfocado a LinQ, ya que había visto en muchas ocasiones muchas preguntas en foros dirigidas a este tema, pero no he encontrado ninguna en la que aune todos y cada uno de los casos.
Espero dar algo de luz con las líneas que siguen.
Índice
- · Instalación
- · Usando el código
- · Inner Join
- · Left Join
- · Right Join
- · Full Outer Join
- · Left Excluding Join
- · Right Excluding Join
- · Full Outer Excluding Join
- · La mejor solución (para mí)
- · Aplicación de testing
Installation
Tiene una instalación muy simple, añadiendo un paquete nuget.
Usando el código
Usaremos las dos clases siguientes para realizer los
ejemplos:
public class Person { public string ID { get; set; } public string Name { get; set; } public int Age { get; set; } public double Salary { get; set; } public DateTime Born { get; set; } public int IdAddress { get; set; } } public class Address { public int IdAddress { get; set; } public string Street { get; set; } public int Num { get; set; } public string City { get; set; } }
Estos son los valores para la clase Person:
Estos son los valores para la clase Addess:
La librería de métodos de extensión que he realizado tiene seis métodos, no contempla el método inicial INNER JOIN, ya que este ya está incluido dentro de la clase Enumerable de System.Linq y fue realizado por el equipo de LinQ de Microsof.
Estos son los métodos que explicaremos:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
LEFT JOIN EXCLUDING INNER JOIN
RIGHT JOIN EXCLUDING INNER JOIN
FULL OUTER JOIN EXCLUDING
INNER JOIN
INNER
JOIN
Es el método inicial y no mostraré su método extensor ya que como hemos comentado está dentro del Framework.
var result = from p in Person.BuiltPersons() join a in Address.BuiltAddresses() on p.IdAddress equals a.IdAddress select new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street };
Lambda Expression:
var resultJoint = Person.BuiltPersons().Join( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street });
Como se puede observer el método tiene 5 partes
que serán compartidas por todos los demás que iremos viendo:
Is the main Collection.
Is the inner Collection.
Is the PK.
Is the FK.
Is the type for the result
collection.
Resultado de la consulta anterior:
Como se puede apreciar los valores de
PersonIdAddresses machean con los de AddressIdAddesses.
LEFT
JOIN
Extension
Method:
public static IEnumerable<TResult> LeftJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) where TSource : class where TInner : class { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from s in source join i in inner on pk(s) equals fk(i) into joinData from left in joinData.DefaultIfEmpty() select result(s, left); return _result; }
Lambda
Expression:
var resultJoint = Person.BuiltPersons().LeftJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });
Tenemos que
prestar atención aquí, en el momento de llamar al método select y construir
nuestro nuevo tipo de resultado debemos controlar los valores devueltos por la
clase Address, porque el objeto devuelto puede ser nulo, y en ese caso, la
lectura de cualquiera de Sus propiedades lanzaría una excepción
NullReferenceException
Resultado de la consulta anterior:
RIGHT
JOIN
Extension
Method:
public static IEnumerable<TResult> RightJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) where TSource : class where TInner : class { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from i in inner join s in source on fk(i) equals pk(s) into joinData from right in joinData.DefaultIfEmpty() select result(right, i); return _result; }
Lambda
Expression:
var resultJoint = Person.BuiltPersons().RightJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street });
Fijaros que debemos controlar los valores null en la
clase Person para evitar excepciones.
Resultado de la consulta anterior:
FULL OUTER
JOIN
Extension
Method:
public static IEnumerable<TResult> FullOuterJoinJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) where TSource : class where TInner : class { var left = source.LeftJoin(inner, pk, fk, result).ToList(); var right = source.RightJoin(inner, pk, fk, result).ToList(); return left.Union(right); }
Lambda
Expression:
var resultJoint = Person.BuiltPersons().FullOuterJoinJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });
Fijaros en el control de los valores null en ambos casos.
Resultados de la consulta anterior:
LEFT
EXCLUDING JOIN
Extension
Method:
public static IEnumerable<TResult> LeftExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) where TSource : class where TInner : class { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from s in source join i in inner on pk(s) equals fk(i) into joinData from left in joinData.DefaultIfEmpty() where left == null select result(s, left); return _result; }
Lambda
Expression:
var resultJoint = Person.BuiltPersons().LeftExcludingJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = a.MyPerson.Name, Age = a.MyPerson.Age, PersonIdAddress = a.MyPerson.IdAddress, AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });
Fijaros que hemos controlado el valor null en la clase
Address.
Resultado de la consulta anterior:
RIGHT
EXCLUDING JOIN
Extension
Method:
public static IEnumerable<TResult> RightExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) where TSource : class where TInner : class { IEnumerable<TResult> _result = Enumerable.Empty<TResult>(); _result = from i in inner join s in source on fk(i) equals pk(s) into joinData from right in joinData.DefaultIfEmpty() where right == null select result(right, i); return _result; }
Lambda
Expression:
var resultJoint = Person.BuiltPersons().RightExcludingJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = a.MyAddress.IdAddress, Street = a.MyAddress.Street });
En esta ocasión controlamos los valores null de la
clase Person.
Resultado de la consulta anterior:
FULL OUTER EXCLUDING JOIN
Extension
Method:
public static IEnumerable<TResult> FulltExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> inner, Func<TSource, TKey> pk, Func<TInner, TKey> fk, Func<TSource, TInner, TResult> result) where TSource : class where TInner : class { var left = source.LeftExcludingJoin(inner, pk, fk, result).ToList(); var right = source.RightExcludingJoin(inner, pk, fk, result).ToList(); return left.Union(right); }
Lambda Expression:
var resultJoint = Person.BuiltPersons().FulltExcludingJoin( /// Source Collection Address.BuiltAddresses(), /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection .Select(a => new { Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"), Age = (a.MyPerson != null ? a.MyPerson.Age : -1), PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1), AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1), Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value") });
Controlamos los valores null en ambos casos:
Resultado de la consulta anterior:
La mejor solución
Este creo que es la major opción para un programador
orientado a objetos.
var GroupPersons = this.Persons.GroupJoin(this.Addresses, /// Inner Collection p => p.IdAddress, /// PK a => a.IdAddress, /// FK (p, a) => /// Result Collection new { MyPerson = p, Addresses = a.Select(ad => ad).ToList() }).ToList();
o
var GroupAddresses = this.Addresses.GroupJoin(this.Persons, /// Inner Collection a => a.IdAddress, /// PK p => p.IdAddress, /// FK (a, p) => /// Result Collection new { MyAddress = a, Persons = p.Select(ps => ps).ToList() }).ToList();
Este código rellena el treveew:
foreach (var data in GroupPersons) { TreeViewItem tbi = new TreeViewItem{ Header = data.MyPerson }; this.treePersons.Items.Add(tbi); foreach (var d in data.Addresses) { TreeViewItem tbiChild = new TreeViewItem { Header = d , Background = Brushes.Gainsboro }; this.treePersons.Items.OfType<TreeViewItem>().Last().Items.Add(tbiChild); } }
o
foreach (var data in GroupAddresses) { TreeViewItem tbi = new TreeViewItem{ Header = data.MyAddress }; this.treeAddresses.Items.Add(tbi); foreach (var d in data.Persons) { TreeViewItem tbiChild = new TreeViewItem { Header = d , Background = Brushes.Gainsboro }; this.treeAddresses.Items.OfType<TreeViewItem>().Last().Items.Add(tbiChild); } }
Resultado:
Cambiamos los valores de IdAddress para ver el
resultado más claramente .
Resultado:
Aplicación de Testing
En la aplicación de prueba, podemos cambiar los
valores de las colecciones Person y Address y elegir la combinación para
aplicar los cambios que se aplicarán en las colecciones de resultados.
Link to download de app testing
Wynn Hotel, Las Vegas - The TITanium Arts Group
ResponderEliminarThis Forbes Travel titanium mig 170 Guide titanium nitride Five Star hotel features luxurious rooms, five signature keith titanium restaurants, a trekz titanium pairing full-service spa, titanium nipple bars and a world-class spa. All guests