Entity Framework - Results from subquery, speed issue?

Question

I've made a query to get a list of articles, each bound to a "header", so i retrieve the header plus the related articles and their properties. The query works, however in its current style it is

• Somewhat messy ( in my opinion)
• The .ToList() takes way longer than i would expect.

Does anyone see any obvious reason for the speed-issue?

        var offerheaders = 
        from o in dbcontext.F_CAB_OFFER_HEADERS
        where
        o.OFHD_FK_BUYER == userinfo.orgaTypeSequence
        && o.OFHD_VALID_FROM <= userinfo.selectedDate
        && o.OFHD_VALID_TO >= userinfo.selectedDate
        && o.OFHD_DELETED_YN == 0
        && o.OFHD_DELETED_BY_OWNER_YN == false
        && o.OFHD_OFFER_TYPE == userinfo.offerType
        orderby o.OFHD_NO ascending
        select o;

    var offerlist = 
        from ofhd in offerheaders
        select new {
            ofhd = new {
                OfferNo = ofhd.OFHD_NO,
                OfferSequence = ofhd.OFHD_SEQUENCE,
                ValidFrom = ofhd.OFHD_VALID_FROM,
                ValidTo = ofhd.OFHD_VALID_TO,
                OfferType = ofhd.OFHD_OFFER_TYPE,
                Maingroup = new { cdmg_seq = ofhd.F_CAB_CD_MAIN_GROUP_TYPE.CDMG_SEQUENCE, Desc = ofhd.F_CAB_CD_MAIN_GROUP_TYPE.CDMG_DESC },
                Supplier = new {
                    Name = ofhd.F_CAB_GROWER.F_CAB_ORGANISATION.ORGA_NAME,
                    Pic = ofhd.F_CAB_GROWER.F_CAB_ORGANISATION.ORGA_FK_PICTURE,
                    Seq = ofhd.F_CAB_GROWER.GROW_SEQUENCE
                },
                Caption = ofhd.OFHD_CAPTION,
                Seperate = ofhd.OFHD_SHOW_SEPARATE_YN,
                //ofdts = (from ofdt in dbcontext.F_CAB_OFFER_DETAILS.Where(x => x.OFDT_FK_OFFER_HEADER == ofhd.OFHD_SEQUENCE && x.OFDT_NUM_OF_ITEMS > 0 && x.OFDT_LATEST_DELIVERY_DATE_TIME > compareDateTime && x.OFDT_LATEST_ORDER_DATE_TIME > compareDateTime)
                ofdts = from ofdt in dbcontext.F_CAB_OFFER_DETAILS
                        join props in dbcontext.F_CAB_CAB_PROP on ofdt.OFDT_FK_CAB_CODE equals props.PROP_FK_CABC_SEQ
                        join cabcode in dbcontext.F_CAB_CD_CAB_CODE on ofdt.OFDT_FK_CAB_CODE equals cabcode.CABC_SEQUENCE
                        join cabgroup in dbcontext.F_CAB_CD_CAB_GROUP on cabcode.CABC_FK_CAB_GROUP equals cabgroup.CDGR_SEQUENCE
                        join grouptype in dbcontext.F_CAB_CD_GROUP_TYPE on cabgroup.CDGR_FK_GROUP_TYPE equals grouptype.CDGT_SEQUENCE
                        join maingrouptype in dbcontext.F_CAB_CD_MAIN_GROUP_TYPE on grouptype.CDGT_FK_MAIN_GROUP equals maingrouptype.CDMG_SEQUENCE
                        join caca in dbcontext.F_CAB_CAB_CASK_MATRIX on ofdt.OFDT_FK_CACA_SEQ equals caca.CACA_SEQUENCE
                        join cask in dbcontext.F_CAB_CD_CASK on caca.CACA_FK_CASK equals cask.CDCA_SEQUENCE
                        join vbncode in dbcontext.F_CAB_CAB_VBN_MATRIX on cabcode.CABC_SEQUENCE equals vbncode.CVMA_FK_CAB_CODE
                        join grel in dbcontext.F_CAB_GENERAL_RELATIONS on ofdt.OFDT_FK_GREL_SEQ equals grel.GREL_SEQUENCE into greltable
                            from g_loj in greltable.DefaultIfEmpty()
                        where 
                            ofdt.OFDT_FK_OFFER_HEADER == ofhd.OFHD_SEQUENCE 
                            && ofdt.OFDT_NUM_OF_ITEMS > 0
                            && props.PROP_FK_CDLA_SEQ == userinfo.lang.CDLA_SEQUENCE
                        orderby props.PROP_CAB_DESC ascending
                        select new {
                            Desc = props.PROP_CAB_DESC,
                            Group = new { cdgr_seq = cabgroup.CDGR_SEQUENCE, Desc = cabgroup.CDGR_DESC },
                            Grouptype = new { grouptype.CDGT_SEQUENCE, Desc = grouptype.CDGT_DESC },
                            Properties = new CABProperties { props = props },            
                            Price = ofdt.OFDT_ITEM_PRICE,
                            PIC_SEQ = ofdt.OFDT_FK_PICTURE ?? ((cabcode.CABC_FK_PICTURE ?? cabcode.CABC_SEQUENCE)),
                            PIC_URL = ofdt.OFDT_EXT_PICTURE_REF ?? "",
                            Seq = ofdt.OFDT_SEQUENCE,
                            Available = ofdt.OFDT_NUM_OF_ITEMS,
                            CabCode = ofdt.F_CAB_CD_CAB_CODE.CABC_CAB_CODE,
                            VBNCode = vbncode.CVMA_FK_VBN_CODE,
                            Remark = ofdt.OFDT_REMARK,
                            IsSpecial = ofdt.OFDT_SPECIAL_YN,
                            Arrived = inTransit ? ofdt.OFDT_ARRIVAL_DATE < DateTime.Now : true,
                            Cask = new CABCask { cask = cask, caca = caca },                                            
                            Supplier = g_loj == null ? (ofdt.OFDT_SUPPLIER ?? "") : g_loj.GREL_NAME,
                            SupplierWeb = g_loj == null ? "" : g_loj.GREL_WEBSITE_URL,
                            SupplierLogo = g_loj == null ? ofhd.F_CAB_GROWER.F_CAB_ORGANISATION.ORGA_FK_PICTURE : g_loj.GREL_FK_PICT_SEQ,
                            SupplierSeq = g_loj == null ? -1 : g_loj.GREL_SEQUENCE,
                        }
            }
        };
    userinfo.mainofferlist = offerlist.ToList();

Answer 1

As Daniel Kelly also mentioned the ToList function is where your query is executed, because these LinqToEntities queries are executed at the point where they are first enumerated, and ToList does that to be able to create a list.

Basically the reason why your querying takes so much time can be separated into two different reasons:

1. you are using too much projections and I thine (the parts with new { })
2. your query has an incredible amount of join clauses

I would recommend to separate your query into subqueries, and run them separately like the first part in

...
select o

use

...
select o).ToList()

by breaking down the main query where you have a lot of subqueries it will be faster and much more readable, so you have less "messiness".

And last but not least you should create mapping for the anonymous objects, and use those classes other than projection that should speed up your query.