[ASP.NET Core] Toon één instance van one-to-many relatie

[NickThissen]

Ik ben bezig met een web applicatie in ASP.NET Core 2.1 (C#), in combinatie met Entity Framework Core en een MySQL database.

In de database heb ik een lijst met producten (Products). Gebruikers kunnen een rating geven aan een product welke opgeslagen worden in ProductRatings. Een Product heeft dus een lijst met ratings, en een rating heeft een referentie naar het product en welke gebruiker de rating heeft gegeven.

De database modellen zijn als volgt:

C#:

public class Product
{
    public int Id {get;set;}
    public string Name {get;set;}
    public int Type {get;set;}
    
    public ICollection<ProductRating> Ratings {get;set;}
    
    [NotMapped]
    public ProductRating HighestRating {get;set;}
    
    [NotMapped]
    public ProductRating SecondHighestRating {get;set;}
}

public class ProductRating
{
    public int Id {get;set;}
    public int Rating {get;set;}
    
    public int UserId {get;set;}
    public User User {get;set;}
    
    public int ProductId {get;set;}
    public Product Product {get;set;}
}

Ik wil nu een overzicht pagina bouwen waarop een lijstje met een aantal producten te zien zijn. In dit overzicht wil ik voor elk product de hoogste en de een-na-hoogste rating tonen, zowel het cijfer als de naam van de gebruiker die de rating heeft gegeven.

In de Product class heb ik momenteel de properties HighestRating en SecondHighestRating, hier kom ik later op terug.


Hoewel ik dit allemaal wel voor elkaar krijg loop ik tegen dingen aan waarvan ik denk dat het beter zou moeten. Ik zie echter nog niet zo snel in hoe precies, dus ik zou graag de discussie starten, wat denken jullie?

---

Probleem 1: hoe haal ik zo efficient mogelijk de producten en alleen de hoogste en een-na-hoogste rating uit de database?

Momenteel haal ik voor elk product ook alle ratings op, en filter ik later in een loop tot alleen de twee hoogste ratings. Dit is natuurlijk niet optimaal. Hoewel ik niet zoveel ratings verwacht dat dit echt problemen gaat leveren zou ik toch graag willen weten hoe het beter kan, mocht het ooit voorkomen op een tabel die wel enorm veel records heeft.

Huidige code die de objecten uit de database haalt:

C#:

public async Task<List<Product>> GetProducts(int type)
{
    // Get products with all ratings
    var products = await Database.Products
        .Include(p => p.Ratings).ThenInclude(r => r.User)
        .Where(p => p.Type == type)
        .ToListAsync();
        
    // Take only relevant ratings and store in Product object
    foreach (var product in products)
    {
        var orderedRatings = product.Ratings.OrderByDescending(r => r.Rating);
        product.HighestRating = orderedRatings.FirstOrDefault();
        product.SecondHighestRating = orderedRatings.Skip(1).FirstOrDefault();
    }
}

Een alternatief zou zijn om eerst de producten op te halen, en dan per product een nieuwe query te doen om de twee hoogste ratings op te halen. Afhankelijk van hoeveel producten en ratings er verwacht worden kan ik me voorstellen dat dit efficienter is (bijv: heel weinig producten met enorm veel ratings).


Mijn gevoel zegt echter dat dit in één query moet kunnen, in ieder geval in SQL (of het ook vertaald kan worden naar een LINQ query is nog de vraag).

Weet iemand hoe dit beter kan?

---

Probleem 2: hoe hou ik de database en de app logica gescheiden?

Momenteel heb ik de twee properties HighestRating en SecondHighestRating in de Product class gestopt, met een NotMapped zodat het database model geen rare dingen gaat doen. Ik vind dit niet zo netjes, aangezien de HighestRating alleen nuttig is voor de applicatie zelf en weinig met de database te maken heeft.

Een alternatief zou zijn om een apart model (viewmodel) te maken met Product en de ratings apart:

C#:

public class ProductViewModel
{
    public Product Product {get;set;}
    public ProductRating HighestRating {get;set;}
    public ProductRating SecondHighestRating {get;set;}
}

Dit viewmodel zou ik dan gebruiken om de koppeling te maken tussen Product en de twee ratings die alleen voor m'n applicatie relevant zijn. Echter, wil ik alle informatie in een query halen (zie probleem 1) dan zal de repository / data laag nog steeds toegang moeten hebben tot deze viewmodel. Opnieuw ben ik dus een koppeling aan het maken tussen database en applicatie logic.

Ook dit is geen enorm probleem maar het is volgens mij niet netjes, en het is een fundamenteel "probleem" waar ik vaker tegenaan loop. Ik denk dat er een standaard oplossing is maar ik zie hem niet.

[Lethalis]

NickThissen schreef op woensdag 7 november 2018 @ 15:59:
Mijn gevoel zegt echter dat dit in één query moet kunnen, in ieder geval in SQL (of het ook vertaald kan worden naar een LINQ query is nog de vraag).

Weet iemand hoe dit beter kan?

Welcome to the vietnam of computer science.

Object relational mapping

In SQL Server (geen idee hoe dit in MySQL moet of kan) heb je de ROW_NUMBER functie:

https://docs.microsoft.co...-sql?view=sql-server-2017

Ik kan hiermee dus een query schrijven die Products met Ratings joint en dan afhankelijk van hun rating een ROW_NUMBER met ORDER BY geven en daarvan alleen de eerste 2 pakken.

Nogmaals, ik heb geen idee hoe dit in MySQL moet

Vervolgens kun je vast met EF direct een stored prodedure of query mappen naar het resultaat.

Probleem 2: hoe hou ik de database en de app logica gescheiden?

Voor het mappen tussen view models en data models wordt vaak AutoMapper gebruikt.

Ik vind het alleen wel een hoop overhead en moet bekennen dat ik mij in veel situaties erbij neergelegd heb dat er een koppeling tussen de database en de app logica is. Je moet je namelijk afvragen waarom je het zo graag gescheiden houdt.

Meestal ontkoppel je iets met een reden. Een reden kan zijn dat een bepaald onderdeel vaak wijzigt. Of dat het ook gebruikt moet kunnen worden vanuit een andere app.

Heb je deze redenen echter niet, dan vind ik het eigenlijk een beetje zonde van de energie

Maar goed, ik heb ook het gebruiken van een full fledged ORM opgegeven en ben een tevreden Dapper gebruiker.

[Voor 3% gewijzigd door Lethalis op 07-11-2018 16:38]

[NickThissen]

Lethalis schreef op woensdag 7 november 2018 @ 16:28:
Voor het mappen tussen view models en data models wordt vaak AutoMapper gebruikt.

Ik vind het alleen wel een hoop overhead en moet bekennen dat ik mij in veel situaties erbij neergelegd heb dat er een koppeling tussen de database en de app logica is. Je moet je namelijk afvragen waarom je het zo graag gescheiden houdt.

Meestal ontkoppel je iets met een reden. Een reden kan zijn dat een bepaald onderdeel vaak wijzigt. Of dat het ook gebruikt moet kunnen worden vanuit een andere app.

Heb je deze redenen echter niet, dan vind ik het eigenlijk een beetje zonde van de energie

Er is momenteel niet een hele harde reden, maar een "gevaar" dat ik kan bedenken is dat er nu twee properties HighestRating en SecondHighestRating op Product bestaan, welke lang niet altijd relevant zijn. In dit specifieke voorbeeld kan ik GetProducts aanroepen en verwacht ik dat ze gevuld worden, maar er zullen vast ook scenarios zijn waar ik de ratings helemaal niet nodig heb en dus enkel de products opvraag. In dat geval worden de properties niet gevuld maar ze bestaan nog wel. Het is dus nu aan degene die de logica implementeert om na te denken dat deze ratings niet altijd een waarde hebben, en als deze nodig is om deze specifiek op te vragen via deze GetProducts (en niet een andere functie).

Klein detail maar dit is wel vaak hoe er bugs ontstaan. Vooral als iemand anders met deze code / repository gaat werken zou het zomaar kunnen dat hij ziet dat er een HighestRating bestaat maar dat hij niet begrijpt waarom deze geen waarde krijgt. Nog erger als de database laag in een apart project zou zitten waar hij wellicht geen toegang tot de source code heeft.

Als er een apart object terug komt als "ProductWithHighestRating", of een apart viewmodel zoals in m'n eerste post, dan is het meteen een duidelijk verschil tussen:

C#:

interface IProductRepository
{
    List<Product> GetProducts();
    List<ProductWithHighestRating> GetProducts();
}

[Voor 7% gewijzigd door NickThissen op 07-11-2018 16:40]

[Haan]

Lethalis schreef op woensdag 7 november 2018 @ 16:28:

Voor het mappen tussen view models en data models wordt vaak AutoMapper gebruikt.

Ik vind het alleen wel een hoop overhead en moet bekennen dat ik mij in veel situaties erbij neergelegd heb dat er een koppeling tussen de database en de app logica is. Je moet je namelijk afvragen waarom je het zo graag gescheiden houdt.

Meestal ontkoppel je iets met een reden. Een reden kan zijn dat een bepaald onderdeel vaak wijzigt. Of dat het ook gebruikt moet kunnen worden vanuit een andere app.

Heb je deze redenen echter niet, dan vind ik het eigenlijk een beetje zonde van de energie

Dan moet je eens in een grote code base gaan werken Stel je bijvoorbeeld een solution voor met een paar dozijn web applicaties die allemaal een harde koppeling hebben met (bijvoorbeeld) EntityFramework en ga dan even de versie bijwerken. Daar wordt je niet vrolijk van ( ja ik spreek uit ervaring ).
Voor een simpel projectje kan het misschien wat zwaar geschut zijn, maar ook dat kan uitgroeien tot iets groters en dan ben je blij als het er al in zit.

Wat betreft probleem 1, op een Include kan je inderdaad geen Take(2) oid zetten, dus dat moet je anders oplossen, ik heb het zelf nooit nodig gehad, maar even googelen leert me dat iets als dit wel schijnt te kunnen:

C#:

var products = Database.Products.Select(p=> new { Product = p, Ratings = p.Ratings.OrderByDescending(r => r.Rating).Take(2));

[Lethalis]

NickThissen schreef op woensdag 7 november 2018 @ 16:39:
[...]
Er is momenteel niet een hele harde reden, maar een "gevaar" dat ik kan bedenken is dat er nu twee properties HighestRating en SecondHighestRating op Product bestaan, welke lang niet altijd relevant zijn. In dit specifieke voorbeeld kan ik GetProducts aanroepen en verwacht ik dat ze gevuld worden, maar er zullen vast ook scenarios zijn waar ik de ratings helemaal niet nodig heb en dus enkel de products opvraag.

Daarvoor is in principe lazy loading uitgevonden, waarbij die properties pas worden gevuld op het moment dat iemand ze opvraagt.

Maar ik moet eerlijk bekennen dat ik al een tijdje geen full fledged ORM meer gebruik, maar bijvoorbeeld Dapper. En dan zou ik geneigd zijn een ProductWithRatings class te definiëren die van Product overerft en simpelweg een losse functie GetProductsWithRatings te maken.

Simpel, maar levert ook weinig gezeik op.

[NickThissen]

Haan schreef op woensdag 7 november 2018 @ 16:41:
[...]

Wat betreft probleem 1, op een Include kan je inderdaad geen Take(2) oid zetten, dus dat moet je anders oplossen, ik heb het zelf nooit nodig gehad, maar even googelen leert me dat iets als dit wel schijnt te kunnen:

C#:

var products = Database.Products.Select(p=> new { Product = p, Ratings = p.Ratings.OrderByDescending(r => r.Rating).Take(2));

Vreemd, ik kwam juist eerder tegen dat dit niet werkt. Ik zal het eens proberen vanavond.

Lethalis schreef op woensdag 7 november 2018 @ 16:42:
[...]

Daarvoor is in principe lazy loading uitgevonden, waarbij die properties pas worden gevuld op het moment dat iemand ze opvraagt.

Maar ik moet eerlijk bekennen dat ik al een tijdje geen full fledged ORM meer gebruik, maar bijvoorbeeld Dapper. En dan zou ik geneigd zijn een ProductWithRatings class te definiëren die van Product overerft en simpelweg een losse functie GetProductsWithRatings te maken.

Simpel, maar levert ook weinig gezeik op.

Lazy loading wil ik juist niet want dan krijg je dus het probleem dat hij voor elke aparte Product een nieuwe query gaat uitvoeren. Daarnaast werkt lazy loading voor zover ik weet nog steeds niet in EF Core (maar misschien is m'n info outdated).

Mijn "probleem" met een aparte ProductWithRatings is:
- Als ik deze class in de database/repository laag stop dan zit ik dus applicatie logica ("ik wil 2 ratings") in de database laag te stoppen. Dit kan prima maar als er meer van dit soort dingen nodig zijn dan is de hele scheiding van de twee kapot. Inderdaad hoeft dat niet meteen een probleem te zijn maar het is gewoon niet netjes.
- Als ik deze class in de applicatie laag stop dan zie ik geen andere optie dan minimaal twee queries te doen: eentje voor alle products en daarna eentje (of meer) om voor elk product de 2 ratings op te halen. Als ik een query kan vinden als "haal de top 2 ratings op voor deze specifieke producten 1,2,3,4" dan is dit ook nog wel te overzien. Dat komt weer terug in probleem 1 dan.

[Lethalis]

Haan schreef op woensdag 7 november 2018 @ 16:41:
[...]
Dan moet je eens in een grote code base gaan werken Stel je bijvoorbeeld een solution voor met een paar dozijn web applicaties die allemaal een harde koppeling hebben met (bijvoorbeeld) EntityFramework en ga dan even de versie bijwerken. Daar wordt je niet vrolijk van ( ja ik spreek uit ervaring ).
Voor een simpel projectje kan het misschien wat zwaar geschut zijn, maar ook dat kan uitgroeien tot iets groters en dan ben je blij als het er al in zit.

Dan komen we denk ik in een andere discussie terecht, namelijk eentje van horizontal of juist vertical layering.

Bij horizontal layering ga je ervan uit dat een systeem uit een aantal horizontale lagen bestaat (database, data acces layer, business layer, presentation layer, enzovoorts) en dat werkelijk alles gebruik maakt van deze lagen (die weer als libraries zijn geïmplementeerd).

Dit leidt bijna altijd tot coupling tussen diverse onderdelen van een groot systeem die eigenlijk niets met elkaar van doen hebben.

De oplossing daarvoor is dus juist vertical layering, waarbij je een groot systeem opdeelt in de verantwoordelijkheden die het heeft (klantbeheer, boekhouding, enzovoorts). Dit komt de cohesie ten goede en vermindert de coupling.

Dus in mijn ideale wereld heb je in een grote codebase geen libraries die door werkelijk alles worden gebruikt Maar juist allemaal - op een bepaalde functionaliteit - toegespitste libraries die onafhankelijk zijn.

NickThissen schreef op woensdag 7 november 2018 @ 16:47:
[...]
Mijn "probleem" met een aparte ProductWithRatings is:
- Als ik deze class in de database/repository laag stop dan zit ik dus applicatie logica ("ik wil 2 ratings") in de database laag te stoppen. Dit kan prima maar als er meer van dit soort dingen nodig zijn dan is de hele scheiding van de twee kapot. Inderdaad hoeft dat niet meteen een probleem te zijn maar het is gewoon niet netjes.

Je kan er ook "ik wil X ratings" van maken

Dat maakt het iig iets algemener, waardoor je het op meer plekken kunt gebruiken.

Maar je kunt denk ik nooit helemaal voorkomen dat je een soort van leaky abstraction hebt, waarbij details van de ene laag in de andere lekken.

Het punt met EF en LINQ providers, is dat er altijd - iets - moet zijn dat het naar SQL vertaalt. Een LINQ query die het prima doet met SQL Server, hoeft dus helemaal niet te werken met MySQL. Bijvoorbeeld, omdat MySQL geen uitgebreide ranking functies heeft (of had? gebruik je wel MySQL versie 8?).

Op dat moment zit een implementatiedetail dus de abstractie in de weg.

[Voor 27% gewijzigd door Lethalis op 07-11-2018 16:59]

[R4gnax]

NickThissen schreef op woensdag 7 november 2018 @ 15:59:
Probleem 1: hoe haal ik zo efficient mogelijk de producten en alleen de hoogste en een-na-hoogste rating uit de database?

Wil je het in 1 query? Of in elk geval EF Core een zo efficient mogelijk query plan laten bouwen voor je?
Dat kan!

Bouw een AggregateProductRatings view in je database waarin je per product de hoogste twee ratings boven haalt, samen met een ProductID: de primary key ID van je Product.

Gebruik EF Core 2.1 Query Types om een 'entity' over deze view heen te bouwen. Leg daarbij navigational properties aan tussen je Product en AggregateProductRating entities via de ProductID van de AggregateProductRatings als foreign key.

Let wel op dat je dat goed doet, want anders kun je rare problemen krijgen!

Gelukkig is er een goed artikel over EF Core 2.1 wat je o.a. leert hoe je relaties kunt definieren tussen Entity en Query Types.

Heb je dat allemaal netjes gedaan, dan kun je daarna een query bouwen die producten samen met hun aggregate rating ophaalt in één query. Net zoals je dat al eerder deed met Include over de navigational property heen. En heb je de ratings niet nodig; dan laat je die Include weg.

NickThissen schreef op woensdag 7 november 2018 @ 15:59:
Probleem 2: hoe hou ik de database en de app logica gescheiden?

Factories / Repositories / Dataproviders / etc. in combinatie met een Model / ViewModel/ DTO / POCO / etc.

Je geeft aan dat je het lelijk vindt, maar ergens zul je die transitie van database model naar applicatie model toch moeten maken. Kwestie van de juiste plek te vinden. Meestal doe je die transitie al een aantal lagen dieper dan de view model factories waar de business logica leeft voor het arrangeren van de data die je wilt presenteren.

[Voor 67% gewijzigd door R4gnax op 07-11-2018 20:18]

[Sandor_Clegane]

Misschien mis ik iets, maar waarom haal je niet de twee beste ratings van de alle producten op en zoek je dan per rating het product erbij? Of moet het een subset van producten zijn?

[NickThissen]

R4gnax schreef op woensdag 7 november 2018 @ 19:32:
[...]


Wil je het in 1 query? Of in elk geval EF Core een zo efficient mogelijk query plan laten bouwen voor je?
Dat kan!

Bouw een AggregateProductRatings view in je database waarin je per product de hoogste twee ratings boven haalt, samen met een ProductID: de primary key ID van je Product.

Gebruik EF Core 2.1 Query Types om een 'entity' over deze view heen te bouwen. Leg daarbij navigational properties aan tussen je Product en AggregateProductRating entities via de ProductID van de AggregateProductRatings als foreign key.

Let wel op dat je dat goed doet, want anders kun je rare problemen krijgen!

Gelukkig is er een goed artikel over EF Core 2.1 wat je o.a. leert hoe je relaties kunt definieren tussen Entity en Query Types.

Heb je dat allemaal netjes gedaan, dan kun je daarna een query bouwen die producten samen met hun aggregate rating ophaalt in één query. Net zoals je dat al eerder deed met Include over de navigational property heen. En heb je de ratings niet nodig; dan laat je die Include weg.

Thanks, dit klinkt veelbelovend, zal er binnenkort in detail naar kijken. Is dit allemaal ondersteund in MySQL?

Sandor_Clegane schreef op woensdag 7 november 2018 @ 21:06:
Misschien mis ik iets, maar waarom haal je niet de twee beste ratings van de alle producten op en zoek je dan per rating het product erbij? Of moet het een subset van producten zijn?

Ja, Products table is heel groot, en ik wil maar een kleine subset selecteren plus de 2 ratings. Via de ratings tabel gaan is dus niet heel efficient lijkt me.

[R4gnax]

NickThissen schreef op woensdag 7 november 2018 @ 22:42:
[...]

Thanks, dit klinkt veelbelovend, zal er binnenkort in detail naar kijken. Is dit allemaal ondersteund in MySQL?

De extra plumbing voor de Query Types feature zit grotendeels aan de kant van het conceptual model, voor zover ik heb begrepen. Zolang EF Core weet hoe het tegen MySQL views aan kan praten, zou het aan de DB access kant verder goed moeten gaan en moeten werken.

[Voor 31% gewijzigd door R4gnax op 07-11-2018 23:15]

[Lethalis]

Zit je overigens vast aan MySQL of kan je ook iets beters anders gebruiken zoals Postgres? Of SQL Express desnoods als het geen geld mag kosten (tot 10GB grootte en doet het tegenwoordig ook op Linux).

[qless]

Gewoon over naar Mongo, elk product in 1 document met alle productratings, heb je altijd maar 1 call nodig

[mulder]

Volgens mij moet dit gewoon kunnen, enigzins pseudo:

code:

from p in products
where p.Type = type
select new YourViewModelOrEvenAnonymous
{
    ProductId = p.Id,
    HighestRating = p.Ratings.FirstOrDefault()  
}

Evt een join of subquery zou voldoende moeten zijn om alles 1 keer op te halen.

Opnieuw ben ik dus een koppeling aan het maken tussen database en applicatie logic.

Je zou evt een Repository met een IQueryable er tussen kunnen zetten als je boel nog extra wilt scheiden, want je applicatie laag moet toch gewoon bij de datalaag moeten kunnen komen?

[Voor 33% gewijzigd door mulder op 09-11-2018 21:12]