Dapper inner joins deel 2
Dapper inner joins deel 2
Inleiding
In dit hoofdstuk leren we hoe we met Dapper een inner join kunnen uitvoeren en hierdoor data ophalen uit meerdere tabellen. Specifiek behandelen we een-op-veel relaties.
Download het voorbeeld van GitPub
Meervoudige navigatie properties
Bestudeer volgende klasse:
public class Klant
{
public string Adres { get; set; }
public string Bedrijf { get; set; }
public int Id { get; set; }
public string Land { get; set; }
public string Plaats { get; set; }
public string Postcode { get; set; }
public string Telefoonnummer { get; set; }
public ICollection<Order> Orders { get; set; }
public override string ToString()
{
return Bedrijf;
}
}In deze klasse is er 1 meervoudige navigatie property: Orders. Deze property is meervoudig. Dit wil zeggen dat er meerdere orders aan een klant gekoppeld kunnen worden. Zoals je kan zien komt de navigation property overeen met een foreign key in de tabel Orders.
Om alle klante op te halen inclusief hun klanten, kunnen we volgende SQL query uitvoeren:
SELECT K.*, O.* FROM Klanten K INNER JOIN Orders O ON K.Id = O.KlantId ORDER BY K.Bedrijf, O.IdVoer deze query uit in Azure Data Studio en bekijk het resultaat. Je zal merken dat we heel wat gedupliceerde data krijgen. We krijgen namelijk meerdere keren dezelfde klant. Om dit op te lossen, zullen we moeten groeperen op het klant-object.
Groeperen van data
Als we deze logica zouden vertalen naar C# code, zal dit er als volgt uit zien in de KlantRepository:
public IEnumerable<Klant> KlantenOphalenMetOrders()
{
string sql = @"SELECT K.*, O.*
FROM Klanten K
INNER JOIN Orders O ON K.Id = O.KlantId
ORDER BY K.Bedrijf, O.Id";
using IDbConnection db = new SqlConnection(ConnectionString);
var klanten = db.Query<Klant, Order, Klant>(
sql,
(klant, order) =>
{
order.Klant = klant;
klant.Orders = new List<Order>() { order };
return klant;
}
);
return GroepeerKlanten(klanten);
}
private static ICollection<Klant> GroepeerKlanten(IEnumerable<Klant> klanten)
{
var gegroepeerd = klanten.GroupBy(k => k.Id);
List<Klant> klantenMetOrders = new List<Klant>();
foreach (var groep in gegroepeerd)
{
var klant = groep.First();
List<Order> alleOrders = new List<Order>();
foreach (var k in groep)
{
alleOrders.Add(k.Orders.First());
}
klant.Orders = alleOrders;
klantenMetOrders.Add(klant);
}
return klantenMetOrders;
}Merk op dat we hier op regel 14 de orders gelijk stellen aan een nieuwe lijst van orders. Dit is nodig omdat Dapper de orders niet automatisch zal toevoegen aan de klant. We zullen dit zelf moeten doen. Voorlopig is de Orders property dus gelijk aan een lijst met 1 order.
De methode GroepeerKlanten zal de klanten groeperen op basis van hun Id. Hierdoor krijgen we een lijst met klanten waarbij elke klant maar 1 keer voorkomt. De orders van de klant worden hierbij samengevoegd in een lijst. Deze lijst wordt dan toegekend aan de Orders property van de klant.
Als eindresultaat krijgen we dus een lijst met klanten waarbij elke klant maar 1 keer voorkomt en waarbij de orders van de klant in een lijst zitten.
Meervoudige relaties over meerdere tabellen
Stel dat we alle klanten willen tonen inclusief hun orders. Daarnaast willen we voor elk order ook nog de orderlijnen tonen die hierbij horen. We zullen dus een inner join moeten uitvoeren op 3 tabellen. Dit kunnen we doen door de volgende query uit te voeren:
SELECT K.*, O.*, OL.* FROM Klanten K INNER JOIN Orders O ON K.Id = O.KlantId INNER JOIN Orderlijnen OL ON O.Id = OL.OrderId ORDER BY K.Bedrijf, O.IdVoer deze query uit in Azure Data Studio en bekijk het resultaat. Je zal merken dat we heel wat gedupliceerde data krijgen. We krijgen namelijk meerdere keren dezelfde klant en order. Om dit op te lossen, zullen we moeten groeperen op het klant-object en op het order-object.
In C# code zal dit er als volgt uit zien in de Klantrepository:
public IEnumerable<Klant> KlantenOphalenMetOrdersEnOrderlijnen()
{
string sql = @"SELECT K.*, O.*, OL.*
FROM Klanten K
INNER JOIN Orders O ON K.Id = O.KlantId
INNER JOIN Orderlijnen OL ON O.Id = OL.OrderId
ORDER BY K.Bedrijf, O.Id";
using (IDbConnection db = new SqlConnection(ConnectionString))
{
var klanten = db.Query<Klant, Order, Orderlijn, Klant>(
sql,
(klant, order, orderlijn) =>
{
order.Klant = klant;
orderlijn.Order = order;
order.Orderlijnen = new List<Orderlijn>() { orderlijn };
klant.Orders = new List<Order>() { order };
return klant;
}
);
var gegroepeerdeKlanten = GroepeerKlanten(klanten);
return gegroepeerdeKlanten.Select(k =>
{
k.Orders = GroepeerOrders(k.Orders);
return k;
});
}
}private static ICollection<Klant> GroepeerKlanten(IEnumerable<Klant> klanten)
{
var gegroepeerd = klanten.GroupBy(k => k.Id);
List<Klant> klantenMetOrders = new List<Klant>();
foreach (var groep in gegroepeerd)
{
var klant = groep.First();
List<Order> alleOrders = new List<Order>();
foreach (var k in groep)
{
alleOrders.Add(k.Orders.First());
}
klant.Orders = alleOrders;
klantenMetOrders.Add(klant);
}
return klantenMetOrders;
}private static ICollection<Order> GroepeerOrders(IEnumerable<Order> orders)
{
return orders.GroupBy(o => o.Id).Select(g =>
{
var order = g.First();
order.Orderlijnen = g.Select(o => o.Orderlijnen.Single()).ToList();
return order;
}).ToList();
}In bovenstaande code groeperen we eerst op regel 17 de klanten. Daarna groeperen we op regel 23 de orders.
Het groeperen van de orders gebeurt op dezelfde denkwijze en manier als het groeperen van de klanten.
Combinatie van meervoudige en enkelvoudige relaties - 1
Stel dat we in ons overzicht alle klanten willen tonen inclusief hun orders. Daarnaast willen we voor elk order ook nog de orderlijnen tonen die hierbij horen. Voor een orderlijn willen we ook nog de naam tonen van het product dat gekoppeld is. De SQL om al deze data op
te halen ziet er als volgt uit:
SELECT K.*, O.*, OL.*, P.* FROM Klanten K INNER JOIN Orders O ON K.Id = O.KlantId INNER JOIN Orderlijnen OL ON O.Id = OL.OrderId INNER JOIN Producten P ON OL.ProductId = P.Id ORDER BY K.Bedrijf, O.IdDe denkwijze om dit op te lossen is een combinatie van de vorige voorbeelden. De code ziet er als volgt uit:
public IEnumerable<Klant> KlantenOphalenMetOrdersEnOrderlijnenEnProduct()
{
string sql = @"SELECT K.*, O.*, OL.*, P.*
FROM Klanten K
INNER JOIN Orders O ON K.Id = O.KlantId
INNER JOIN Orderlijnen OL ON O.Id = OL.OrderId
INNER JOIN Producten P ON OL.ProductId = P.Id
ORDER BY K.Bedrijf, O.Id";
using (IDbConnection db = new SqlConnection(ConnectionString))
{
var klanten = db.Query<Klant, Order, Orderlijn, Product, Klant>(
sql,
(klant, order, orderlijn, product) =>
{
order.Klant = klant;
orderlijn.Order = order;
orderlijn.Product = product;
order.Orderlijnen = new List<Orderlijn>() { orderlijn };
klant.Orders = new List<Order>() { order };
return klant;
}
);
var gegroepeerdeKlanten = GroepeerKlanten(klanten);
return gegroepeerdeKlanten.Select(k =>
{
k.Orders = GroepeerOrders(k.Orders);
return k;
});
}
}- Product is enkelvoudig en kan dus rechtstreeks worden toegekend
- Orderlijnen is meervoudig en kan dus niet rechtstreeks worden toegekend. We moeten eerst groeperen op de orderlijnen van een order.
- Orders is meervoudig en kan dus niet rechtstreeks worden toegekend. We moeten eerst groeperen op de orders van een klant.
Combinatie van meervoudige en enkelvoudige relaties - 2
Stel dat we in ons overzicht alle klanten willen tonen met bijhorende orders. Voor elk order willen we de orderlijnen tonen inclusief het product dat hieraan gekoppeld is. Daarnaast willen we ook weten welke werknemer dit order heeft verwerkt. De SQL om al deze data op te halen ziet er als volgt uit:
SELECT K.*, O.*, OL.*, P.*, W.*
FROM Klanten K
INNER JOIN Orders O ON K.Id = O.KlantId
INNER JOIN Orderlijnen OL ON O.Id = OL.OrderId
INNER JOIN Producten P ON OL.ProductId = P.Id
INNER JOIN Werknemers W ON O.WerknemerId = W.Id
ORDER BY K.Bedrijf, O.IdDe denkwijze om dit op te lossen is een combinatie van de vorige voorbeelden. De code ziet er als volgt uit:
public IEnumerable<Klant> KlantenOphalenMetOrdersEnOrderlijnenEnProductEnWerknemers()
{
string sql = @"SELECT K.*, O.*, OL.*, P.*, W.*
FROM Klanten K
INNER JOIN Orders O ON K.Id = O.KlantId
INNER JOIN Orderlijnen OL ON O.Id = OL.OrderId
INNER JOIN Producten P ON OL.ProductId = P.Id
INNER JOIN Werknemers W ON O.WerknemerId = W.Id
ORDER BY K.Bedrijf, O.Id";
using (IDbConnection db = new SqlConnection(ConnectionString))
{
var klanten = db.Query<Klant, Order, Orderlijn, Product, Werknemer, Klant>(
sql,
(klant, order, orderlijn, product, werknemer) =>
{
order.Klant = klant;
order.Werknemer = werknemer;
orderlijn.Order = order;
orderlijn.Product = product;
order.Orderlijnen = new List<Orderlijn>() { orderlijn };
klant.Orders = new List<Order>() { order };
return klant;
}
);
var gegroepeerdeKlanten = GroepeerKlanten(klanten);
return gegroepeerdeKlanten.Select(k =>
{
k.Orders = GroepeerOrders(k.Orders);
return k;
});
}
}- Werknemer is enkelvoudig en kan dus rechtstreeks worden toegekend
- Product is enkelvoudig en kan dus rechtstreeks worden toegekend
- Orderlijnen is meervoudig en kan dus niet rechtstreeks worden toegekend. We moeten eerst groeperen op de orderlijnen van een order.
- Orders is meervoudig en kan dus niet rechtstreeks worden toegekend. We moeten eerst groeperen op de orders van een klant.
Enkele belangrijke aandachtspunten
- Hoe meer meervoudige navigatie properties je hebt, hoe meer je zal moeten groeperen en hoe langer de query zal worden. Dit betekent ook een langere uitvoeringstijd van de query.
- Voeg enkel hetgeen toe dat je nodig hebt.
- Denk aan performantie.