CROSS LINK

Trixz · Post by **Trixz** » Tue 06 Oct 2009 08:17

Tried to optimize my sql and got this sql generated that is not supported by oracle

SELECT t1.ID, t1.WEBKUNDLISTA_ID, t1.ARTNR, t1.ARTTYP_ID, t1.ANTAL, t1.BOKSTAV
FROM JFP.WEBKUNDLISTARADER t1
LEFT OUTER JOIN JFP.ARTIKLAR t2 ON ((t1.ARTNR = t2.ARTNR) OR ((t1.ARTNR IS NULL) AND (t2.ARTNR IS NULL))) AND (t1.ARTTYP_ID = t2.ARTTYP_ID)
CROSS APPLY (
    SELECT t4.LANDKOD
    FROM JFP.ARTIKLARLAND t4
    WHERE (t2.ARTNR = t4.ARTNR) AND (t2.ARTTYP_ID = t4.ARTTYP_ID)
    ) t3
LEFT OUTER JOIN JFP.WEBKUNDLISTA t5 ON t1.WEBKUNDLISTA_ID = t5.ID
INNER JOIN JFP.KUNDER t6 ON t5.KUNDNR = t6.KUNDNR
LEFT OUTER JOIN JFP.ARTIKLAR t7 ON ((t1.ARTNR = t7.ARTNR) OR ((t1.ARTNR IS NULL) AND (t7.ARTNR IS NULL))) AND (t1.ARTTYP_ID = t7.ARTTYP_ID)
WHERE (t1.WEBKUNDLISTA_ID = :p0) AND (t3.LANDKOD = t6.LANDKOD)
ORDER BY t7.ARTTYP_ID, t7.SORTERINGSNYCKEL, t7.ARTNR
ParameterName = p0
DbType = Decimal
Value = 55

CROSS APPLY is a command I never seen, seems to be ms sql?

EDIT: Did not put in my linq

Code: Select all

IEnumerable customerItems = (from ra in context.Webkundlistaraders
from al in ra.Artiklar.Artiklarlands
where ra.WebkundlistaId == customerId &&
al.Landkod == ra.Webkundlista.Kunder.Landkod
orderby ra.Artiklar.ArttypId, ra.Artiklar.Sorteringsnyckel, ra.Artiklar.Artnr
select ra);

and when handled later

Code: Select all

        var presentation = rows.Select(r => new WebOrderLineView { Id = r.Id, ItemNo = r.Artnr, Description = r.Artiklar.Benamning, Quantity = r.Antal, Price = r.Artiklar.Artiklarlands.Where(al => al.Landkod == sessionVariables.countryCode).First().Pris, Currency = sessionVariables.currency });

Trixz · Post by **Trixz** » Tue 06 Oct 2009 08:34

Ok, i changed my sql so it work but I got a problem

Code: Select all

            IEnumerable customerItems = (from ra in context.Webkundlistaraders
                                                            join al in context.Artiklarlands on new { ra.Artiklar.ArttypId, ra.Artiklar.Artnr, ra.Webkundlista.Kunder.Landkod } equals new { al.ArttypId, al.Artnr, al.Landkod }
                                                            where ra.WebkundlistaId == customerId 
                                                            orderby ra.Artiklar.ArttypId, ra.Artiklar.Sorteringsnyckel, ra.Artiklar.Artnr
                                                            select ra);

I can't get the ra.Artiklar.Artiklarlands to be preloaded so when the second linq is run

Code: Select all

        var presentation = rows.Select(r => new WebOrderLineView { Id = r.Id, ItemNo = r.Artnr, Description = r.Artiklar.Benamning, Quantity = r.Antal, Price = r.Artiklar.Artiklarlands.Where(al => al.Landkod == sessionVariables.countryCode).First().Pris, Currency = sessionVariables.currency });

another sql is sent... and if we have an order with 50 items, this gets pretty slow. How do I preload the data?

AndreyR · Post by **AndreyR** » Wed 07 Oct 2009 13:40

Try to use DataLoadOptions.LoadWith() to load Artiklarlands of the Artiklar entity.