[C# / LINQ] meerdere left joins in 1 query

[PdeBie]

Ik heb een grid welke ik vul vanuit de database middels LINQ.
De data bestaat uit 3 tabellen:
- debtor
- debtorAddress (0-2 per debtor)
- debtorContact (0-xx per debtor)

de debtor tabel heeft de primary key op ID. De tabellen debtorAddress en debtorContact hebben een Foreign key relatie met de tabel debtor.

Nu moet de gebruiker kunnen zoeken in de data. Hiervoor heb ik een klein formuliertje gemaakt waarin ze kunnen zoeken op debiteurnaam, adres (debtorAddress) en contactpersoon naam (debtorContact). Echter krijg ik altijd alle records terug, dus heb even wat assistentie nodig bij het opbouwen van de LINQ query.

In SQL ziet de query er als volgt uit, waarbij de where statements even ter voorbeeld zijn van een zoekopdracht:

SQL:

SELECT     distinct Debtor.*
FROM         Debtor left JOIN
                      DebtorAddress ON Debtor.DebtorID = DebtorAddress.debtorID left JOIN
                      DebtorContact ON Debtor.DebtorID = DebtorContact.debtorID
WHERE Debtor.MemberID = 1269 and Debtor.Deleted = 0 and Debtor.ParentID is null and DebtorContact.lastname like 'vel%'

Bovenstaande query levert mij keurig 1 record op, wat ik ook verwacht.

In LINQ heb ik het als volgt:

C#:

from debt in dc.Debtors.Where(whereClause)

                   join debAddr in dc.DebtorAddresses.Where(whereClauseAddress)
                       on debt.DebtorID equals debAddr.debtorID
                       into debt_addr
                   from debAddr in debt_addr.DefaultIfEmpty()

                   join debCon in dc.DebtorContacts.Where(whereClauseContact)
                       on debt.DebtorID equals debCon.debtorID
                       into debt_cont
                   from debCon in debt_cont.DefaultIfEmpty()

                   where debt.Deleted == false

                   select debt;

Alleen dit werkt dus niet goed, omdat als bijvoorbeeld de variabele 'whereClauseContact' bijvoorbeeld leeg is hij altijd de default pakt, oftewel de gehele debiteur. Hiermee krijg ik dus altijd alle records terug, tenzij ik beide whereclauses vul.

Iemand een idee hoe ik dit aan kan pakken?

[pedorus]

Eerst de joins doen en dan de .DefaultIfEmpty() dus, en die .Where() lijkt me 2 verschillende syntaxen mixen op een vreemde manier, wat beter naar het einde kan aangezien je gewoon een join wil doen. Zie bijv. http://smehrozalam.wordpr...eft-outer-joins-with-linq hoe het wel kan.

Edit: dit geld voor een left join in het algemeen, maar bij nader inzien wil je voor dit probleem waarschijnlijk geen left joins gebruiken, maar gewoon de query opbouwen zoals hieronder staat. Dus als in

C#:

if (debiteurnaam ingevuld)
   query = query.Where(juist debiteurnaam);

[ Voor 34% gewijzigd door pedorus op 12-12-2011 17:31 ]

[Grijze Vos]

Uberhaupt geen joins doen, maar gewoon de navigation properties gebruiken. Die relaties zouden gewoon beschikbaar moeten zijn in je L2S/EF datamodel.

Joins gebruik je voor die uitzonderlijke keer waarbij je relaties aan elkaar knoopt die geen FK relaties zijn in de DB.

Zoiets ongeveer:

C#:

db.Debtors
  .Where(whereClause)
  .Where(d => whereClauseAddress(d.Address))
  .Where(d => whereClauseContact(d.Contact))
  .Where(d => d.Deleted == false)

[ Voor 26% gewijzigd door Grijze Vos op 12-12-2011 16:43 ]

[PdeBie]

even ter verduidelijking van de eerste code.
De variabelen 'whereClause', 'whereClauseAddress' en ''whereClauseContact' zijn van het type string en worden gevuld met SQL code.

Voorbeeld van bijvoorbeeld whereClauseAddress is:

C#:

private string ComposeQueryStringAddresses()
    {
        string query = "1 = 1";

        //add Postcode to querystring
        query += string.IsNullOrEmpty(fltrValuePostcode) ? "" : string.Format(" AND postcode.Contains(\"{0}\")", fltrValuePostcode);

        //add city to querystring
        query += string.IsNullOrEmpty(fltrValueCity) ? "" : string.Format(" AND city.Contains(\"{0}\")", fltrValueCity);

        return query;
    }

In de variabelen whereClause etc. staat dus letterlijk SQL code.

Morgen ga ik beide genoemde suggesties bekijken en kijken wat ik ervan kan bakken.

[ Voor 5% gewijzigd door PdeBie op 12-12-2011 17:00 ]

[Woy]

Waarom zou je nog op deze manier SQL gaan opbouwen?

[pedorus]

Het idee van linq is nu juist dat je niet meer handmatig (delen van) SQL gaat zitten produceren, zodat je bijvoorbeeld geen SQL injection krijgt waarvoor bovenstaande code nogal gevoelig lijkt.

[PdeBie]

mja, zo had ik het nog niet eens bekeken.
Zal het eens met m'n collega (die het initieel gebouwd heeft) overleggen wat we ermee gaan doen.

[sig69]

Sterker nog, download de plugin en posten maar!

[PdeBie]

Ik heb het eerst geposte stuk code laten vervallen en ben nu bezig met de tip van Grijze Vos.
Hiervoor probeer ik de AssociateWith() functie van de DataLoadOptions te gebruiken.

Is nog even puzzelen, maar moet goedkomen denk ik

[PdeBie]

Toch nog even wat assistentie nodig.
De code uit de eerste post mag uit beeld gelaten worden, dit is compleet op de schop gegaan.

Wat hebben we gedaan. De zoekvelden worden nu opgeslagen in een list van KeyValuePairs<string, string>, waarbij de Key het veld is waarin gezocht moet worden en de value de te zoeken waarde.

Deze KeyValuePairs zien er als volgt uit:

code:

[b]whereClause :[/b]
MemberID - xxxx
DebtorIDbyMember - xxxxx
Name - xxxx

[b]whereClauseAddress :[/b]
Postcode - xxxxxx
City - xxxxxx

[b]whereClauseContacts :[/b]
Name - xxxxxx

Deze lists sturen we vanuit de site door naar de DAL en hier wordt de daadwerkelijke query opgebouwd.

Deze functie ziet er nu als volgt uit:

C:

private IQueryable<Debtor> DebtorSearchQuery(List<KeyValuePair<string,string>> whereClause, List<KeyValuePair<string,string>> whereClauseAddress, List<KeyValuePair<string,string>> whereClauseContact)
        {
            dc.DeferredLoadingEnabled = false;

            DataLoadOptions dlo = new DataLoadOptions();
            //dlo.LoadWith<Debtor>(d => d.DebtorContacts);
            //dlo.LoadWith<Debtor>(d => d.DebtorAddresses);

            dlo.AssociateWith<Debtor>(d => d.DebtorAddresses.Where(d_address => 
                                                                        d_address.postcode.Contains(whereClauseAddress.Single(wca => wca.Key == "Postcode").Value) || 
                                                                        d_address.city.Contains(whereClauseAddress.Single(wca => wca.Key == "City").Value)));
            dlo.AssociateWith<Debtor>(d => d.DebtorContacts.Where(d_contact => 
                                                                        (d_contact.infix + " " + d_contact.lastname).Contains(
                                                                            whereClauseContact.Single(wcc => wcc.Key == "Name").Value)
                                                                        ));
            dc.LoadOptions = dlo;
            
            IQueryable<Debtor> debtors = dc.Debtors.Where(d => d.ParentID == null
                                            && d.Deleted == false
                                            && d.MemberID == int.Parse(whereClause.SingleOrDefault(w => w.Key == "MemberID").Value)
                                            && d.Name.Contains(whereClause.SingleOrDefault(w => w.Key == "Name").Value)
                                            && d.DebtorIDbyMember.Contains(whereClause.SingleOrDefault(w => w.Key == "DebtorIDbyMember").Value)
                                            );

            return debtors.Distinct();
        }

Het zoeken op Name en DebtorIDbyMember uit de whereclause gaat goed.

Bij het zoeken op de velden uit de whereClauseContact en whereClauseAddress gaat het fout, hierbij krijg ik alle records uit de tabel terug.
Als ik de SQL query bekijk, zie ik de velden uit deze twee clauses ook niet terug komen in de query.
Ik denk dus dat ik de AssociateWith verkeerd gebruik, maar wellicht dat iemand hier dat weet?