[C# / LINQ2SQL] Dynamic Linq Joins

[PdeBie]

Hoi allen,

ik heb een article, een occasion en een articleoccasion tabel. De laatste is niks anders dan een koppeltabel tussen beide.

Ik haal artikelen op uit de artikel tabel met een dynamic linq query voor de where statement.
Maar hoe kan ik nu een join leggen in dynamic linq met de articleOccasion tabel?

Voorbeeld van een functie die het where-statement opbouwt:

C#:

private string ComposeQueryString()
    {
        string query = string.Format("CountryID = {0} AND Deleted = {1}", NETHERLANDS, "false");

        //ArticleCode or Name
        query += !string.IsNullOrEmpty(fltrValueArticle) ? string.Format(" AND (ArticleCode.Contains(\"{0}\") OR Name.Contains(\"{0}\"))", fltrValueArticle) : string.Empty;

        //From MinValue
        query += !string.IsNullOrEmpty(fltrValueValueFrom) ? string.Format(" AND MinValue >= {0}", fltrValueValueFrom) : string.Empty;

        //Till MinValue
        query += !string.IsNullOrEmpty(fltrValueValueTill) ? string.Format(" AND MinValue <= {0}", fltrValueValueTill) : string.Empty;
        
        //Occasion
        query += !string.IsNullOrEmpty(fltrValueOccasion) ? string.Format(" AND ArticleOccassions.Contains(\"{0}\"))", fltrValueOccasion) : string.Empty;
        
        return query;
    }

Bij de laatste gaat het dus mis. Hoe kan ik dit voor elkaar krijgen?

[Grijze Vos]

Waarom gebruik je hier een string-based query? Dit kan ook gewoon met standaard linq queries zo te zien, en dan is joinen ook een eitje.

[PdeBie]

omdat wij een tabel ophalen op basis van deze methode:

C#:

dc.GetTable<T>().Where(whereClause).OrderBy(orderBy).Skip(startRowIndex).Take(maximumRows);

en de string die uit de functie in de eerste post komt, wordt hier als whereClause gebruikt.

[ Voor 22% gewijzigd door PdeBie op 12-02-2013 15:39 ]

[Grijze Vos]

Je kunt vziw ook gewoon een linq expressie meegeven in die where.

Dat zou dan van het type Func<T, string> moeten zijn geloof ik.

[SaphuA]

.

[ Voor 98% gewijzigd door SaphuA op 31-01-2022 15:06 ]

[Amras]

Een join leggen in je where clause gaat echter ook niet lukken, dan zul je toch met Join operator aan de gang moeten. Wat niet wil zeggen dat ik het niet met bovenstaande tips eens ben.

[PdeBie]

SaphuA schreef op dinsdag 12 februari 2013 @ 15:55:
Wat Grijze Vos zegt... Het is een beetje vreemd dat je je queries alsnog als string opbouwt terwijl Linq juist zoveel strongly typed mogelijkheden geeft.

Mja, ben bezig op basis van legacy code en ze willen 'even' wat toevoegen. (altijd fijn.... not).

Maar ik zal eens kijken of ik het kan bouwen op de methode die Grijze Vos aangeeft:

Grijze Vos schreef op dinsdag 12 februari 2013 @ 15:51:
Je kunt vziw ook gewoon een linq expressie meegeven in die where.

Dat zou dan van het type Func<T, string> moeten zijn geloof ik.

Heb dit nog nooit gebruikt, maar zal kijken of ik iets voor elkaar kan bakken.

Amras schreef op dinsdag 12 februari 2013 @ 16:01:
Een join leggen in je where clause gaat echter ook niet lukken, dan zul je toch met Join operator aan de gang moeten. Wat niet wil zeggen dat ik het niet met bovenstaande tips eens ben.

Wil eigenlijk alleen weten of de combinatie van artikel + occasion bestaat en zo ja, mag hij dat artikel ophalen uit de database. Iemand een idee hoe dat voor elkaar te boxen is met de methode van Grijze vos (of zoals ik het had)?

[ Voor 24% gewijzigd door PdeBie op 12-02-2013 16:07 ]

[PdeBie]

ik heb de 'whereclause' inmiddels verbouwd tot een Expression. Dit ziet er als volgt uit:

C#:

private System.Linq.Expressions.Expression<Func<Article, bool>> BuildConditions()
    {
        System.Linq.Expressions.Expression<Func<Article, bool>> conditions = a => a.MemberID.Equals(internalMemberID) &&
                                                                                    a.CountryID.Equals(NETHERLANDS) &&
                                                                                    a.Deleted.Equals(false) &&
                                                                                    a.UsedFor.Equals(0) &&
                                                                                    !string.IsNullOrEmpty(fltrValueArticle) ? a.ArticleCode.Contains(fltrValueArticle) || a.Name.Contains(fltrValueArticle) : true &&
                                                                                    !string.IsNullOrEmpty(fltrValueValueFrom) ? a.MinValue >= FormatMoneyFromStrToDec(fltrValueValueFrom) : true &&
                                                                                    !string.IsNullOrEmpty(fltrValueValueTill) ? a.MinValue <= FormatMoneyFromStrToDec(fltrValueValueTill) : true &&
                                                                                    !string.IsNullOrEmpty(fltrValueOccasion) ? a.ArticleOccassions.SingleOrDefault(aoc => aoc.MemberID.Equals(internalMemberID) && aoc.OccassionID.Equals(fltrValueOccasion)) != null : true;

        return conditions;
    }

Alleen hoe geef ik die nu mee aan de objectDataSource?
Ik had bij de select parameters 'whereClause' als String staan, maar kan ik daar ook een Func. aan mee geven? Zo ja, wat is daar de TypeCode voor?
Of hoe doe ik dit?

--edit--
gevonden. Je kan ook het type 'object' mee geven i.p.v. String. Nu werkt het wel.
Nu nog zorgen dat de where conditions ook goed werken, maar dat is even puzzelen in de functie 'BuildConditions' die ik gemaakt heb.

HTML:

<asp:ObjectDataSource ID="odsArticles" runat="server" 
    EnablePaging="true" 
    TypeName="BLL.ArticleBLL"
    SelectCountMethod="GetCountWithConditions" SelectMethod="BindGridWithConditions" 
    DataObjectTypeName="Article" OnSelecting="odsArticles_Selecting" >
        <SelectParameters>
            <asp:Parameter Name="whereClause" Type="Object" />
            <asp:Parameter Name="sortExp" Type="String" />
            <asp:Parameter Name="sortDirection" Type="String" />
        </SelectParameters>
</asp:ObjectDataSource>

C#:

protected void odsArticles_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        e.InputParameters["whereClause"] = whereClause;
        e.InputParameters["sortExp"] = sortExp;
        e.InputParameters["sortDirection"] = sortDirection;
    }

[ Voor 22% gewijzigd door PdeBie op 13-02-2013 11:20 ]

[PdeBie]

En het is gelukt.

Dit is de opbouw van het where-statement:

C#:

int occasionID;
int.TryParse(fltrValueOccasion, out occasionID);

Expression<Func<Article, bool>> conditions = a => a.MemberID.Equals(internalMemberID) &&
                                                  a.CountryID.Equals(NETHERLANDS) &&
                                                  a.Deleted.Equals(false) &&
                                                  a.UsedFor.Equals(0) &&
                                                  (!string.IsNullOrEmpty(fltrValueArticle) ? (a.ArticleCode.Contains(fltrValueArticle) || a.Name.Contains(fltrValueArticle)) : true) &&
                                                  (!string.IsNullOrEmpty(fltrValueValueFrom) ? a.MinValue >= FormatMoneyFromStrToDec(fltrValueValueFrom) : true) &&
                                                  (!string.IsNullOrEmpty(fltrValueValueTill) ? a.MinValue <= FormatMoneyFromStrToDec(fltrValueValueTill) : true) &&
                                                  (!string.IsNullOrEmpty(fltrValueOccasion) ? a.ArticleOccassions.SingleOrDefault(aoc => aoc.MemberID.Equals(internalMemberID) && aoc.OccassionID.Equals(occasionID)) != null : true);

De TryParse in het begin moet ik doen, omdat als fltrValueOccasion leeg is hij een cast van een lege string naar Int probeert te doen en dit levert een exception op.

[ Voor 6% gewijzigd door PdeBie op 13-02-2013 13:47 ]

[R4gnax]

pdebie schreef op woensdag 13 februari 2013 @ 13:45:
En het is gelukt.

Dat ziet er heel erg uit alsof het in LINQ-to-Objects draait ipv EF of LINQ-to-SQL: je hebt daar een functie FormatMoneyFromStrToDec die vziw niet omgezet kan worden naar SQL en waar de LINQ provider je een error op zou moeten geven als je het wel probeert.

Je gebruikt ook expliciet Equals terwijl een gewone == operator even goed werkt. (Je kunt de overloaded versie van Equals die een Comparer opgeeft toch niet gebruiken. De LINQ providers kunnen daar niet mee omgaan.)

Daarnaast ben je vergeten om een heleboel zaken buiten de Expression voor te berekenen zodat de waardes vanuit het oogpunt van de LINQ provider constantes zijn. Als je huidige code echt naar SQL omgezet zou worden (wat ik dus niet denk dat het doet...) dan zouden bijv. alle ternaire operators ( '?:' dus) ook direct in SQL omgezet worden. Gaat me dat even een gruwelijke query worden.


Doe jezelf eens een plezier: download het LinqKit NuGet package en gebruik de PredicateBuilder.

C#:

using LinqKit;

[...]

public Expression<Func<Article,bool>> BuildPredicate(
  int memberId,
  int countryId,
  string articleFilter,
  string fromFilter,
  string toFilter,
  string occasionFilter
)
{
  var predicate = PredicateBuilder
    .True<Article>()
    .And(article => article.MemberID == memberId)
    .And(article => article.CountryID == countryId)
    .And(article => !article.Deleted)
    .And(article => article.UsedFor == 0);
    
  if (!string.IsNullOrWhiteSpace(articleFilter))
  {
    var innerPredicate = PredicateBuilder
      .False<Article>()
      .Or(article => article.ArticleCode.Contains(articleFilter))
      .Or(article => article.Name.Contains(articleFilter));
      
    predicate = predicate.And(innerPredicate);
  }
  
  if (!string.IsNullOrWhiteSpace(fromFilter))
  {
    decimal minValue = FormatMoneyFromStrToDec(fromFilter);
    
    predicate = predicate
      .And(article => article.MinValue >= minValue);
  }
  
  if (!string.IsNullOrWhiteSpace(toFilter))
  {
    decimal maxValue = FormatMoneyFromStrToDec(toFilter);
    
    predicate = predicate
      .And(article => article.MaxValue <= maxValue);
  }
  
  if (!string.IsNullOrWhiteSpace(occasionFilter))
  {
    int occasionId;
    if (int.TryParse(occasionFilter, out occasionId))
    {
      var innerPredicate = PredicateBuilder
        .True<ArticleOccasion>()
        .And(occasion => occasion.MemberId == memberId)
        .And(occasion => occasion.OccasionId == occasionId);
      
      predicate = predicate
        .And(article => article.ArticleOccasions.Any(innerPredicate));     
    }
  }
  
  return predicate;   
}

[PdeBie]

De Equals gebruik ik eigenlijk vrij vaak in lambda expressions, omdat ik dat prettiger vind lezen (vooral bij langere lambda expressions).

Ik kan inderdaad een aantal zaken nog vooraf berekenen. Zal hier nog eens naar kijken.

Die LinqKit NuGet package zal ik eens downloaden en kijken wat ik er mee kan.
Heb daar nog geen ervaring mee. Zal het waarschijnlijk voor dit project niet meer gebruiken (i.v.m. aankomende deadlines. Deze pagina werkt nu overigens wel), maar wellicht bij een nieuw project dat ik er wat mee kan.

[R4gnax]

pdebie schreef op woensdag 13 februari 2013 @ 22:04:
Deze pagina werkt nu overigens wel

Heb je al eens met wat logging gekeken wat voor concrete SQL queries er nu naar je database gaan? Als je huidige implementatie nl. inderdaad de Where clausule die je op hebt gebouwd in LINQ-to-Object doet i.p.v. LINQ-to-SQL, dan ben je met je query complete tables binnen aan het halen. Omdat je de ArticleOccasions relational property gebruikt, zou dat goed kwadratisch kunnen zijn.

Dat gaat natuurlijk niet zo'n probleem zijn voor een kleine set dummy data, maar op een groot volume productie data wordt alles de nek omgedraaid.

[PdeBie]

De query haalt keurig maximaal 15 records op met zowel develop, test en productie data. Dus dat is goed.

Zal hem nog eens door SQL profiler halen en kijken wat er gebeurt.

[ Voor 15% gewijzigd door PdeBie op 14-02-2013 09:32 ]

[Phyxion]

pdebie schreef op donderdag 14 februari 2013 @ 09:31:
De query haalt keurig maximaal 15 records op met zowel develop, test en productie data. Dus dat is goed.

Zal hem nog eens door SQL profiler halen en kijken wat er gebeurt.

Daar gaat het niet om. Met LINQ moet je erg oppassen welke queries generated worden, vaak loont het de moeite om bijvoorbeeld twee queries uit te voeren ipv een. Ik heb queries gezien die echt verschrikkelijk waren, en ook erg traag waren. Laatst toevallig wat geoptimaliseerd aan een service, leek goed te draaien totdat het in eens meer dan een minuut duurde. Inmiddels draait het weer < 2 seconds dankzij het herschrijven van de linq queries. Dit was wel met EF, maar bovenstaand packet heeft hetzelfde probleem uitervaring

[PdeBie]

Traag is hij niet. Volgens de profiler doet hij er minder dan een honderste van een seconde over. Dus dat zit wel goed.

De gegenereerde SQL kan netter geef ik toe, maar ik heb helaas niet de tijd om het nog groot op de schop te gooien. Daarvoor zal ik eerst met de projectmanager moeten overleggen.

[D-Raven]

De gegenereerde SQL is wel degelijk belangrijk. Dat het een grote brij is hoeft helemaal niet erg te zijn. Maar waar je voornamelijk op moet letten is of er 1-n of n-m relaties in zitten. Want als dat zo is dan betekend dat dat hoe groter je dataset wordt, hoe trager je query.
Als je niet wilt dat je omgeving op een gegeven moment langzaam aan het dichtslibben is, dan is dat iets wat je zo snel mogelijk wilt oplossen.
Hetzij door het limiteren van het maximum aantal results, of door de 1-n / n-m relaties eruit te halen.

[PdeBie]

D-Raven schreef op donderdag 14 februari 2013 @ 11:49:
Hetzij door het limiteren van het maximum aantal results

En dat heb ik dus gedaan. Hij haalt maximaal 15 records op.
Meer worden er ook niet getoond op een pagina