What are the consequences of putting a method directive on JobEntry.GetDatasetForTreeView

Kinetic ERP
, ,
12

So this

var inMem = from jo in result.JobOper
          join ld in Db.LaborDtl
          on new { jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq }
          equals new { ld.Company, ld.JobNum, ld.AssemblySeq, ld.OprSeq } into gj
          from grp in gj.DefaultIfEmpty()
          select new 
          {
            jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq,
            grp.ScrapQty
          };

Is joining an in memory object (result.JobOper) to a Db Context. Now for very long reasons what Linq to SQL does is query (or bring back) the entire SQL Context and does the join in memory. So this query that you did will query out the entire LaborDtl table (ALL OF IT NO FILTERS) bring that into memory and then do the join in memory.

This will cause a massive amount of time and memory to be consumed. You should NEVER join to ttTables (using a Join statement)

here’s an entire long thread about it

Always use a whereClause to pull records back from a Db Context if your source is a tt (temporary) in memory table.

My query below (which I had to correct cause I just noticed that result was in memory)

var query = from ld in Db.LaborDtl
                where ld.JobNum == jobNum && ld.Company == this.Session.Company && ld.ScrapQty > 0 
                group ld by new { ld.Company, ld.JobNum, ld.AssemblySeq, ld.OprSeq } into g
                select new
                {
                    g.Key.Company,
                    g.Key.JobNum,
                    g.Key.AssemblySeq,
                    g.Key.OprSeq,
                    ActScrapQty = g.Sum(ld => ld.ScrapQty)
                };

Simply uses the jobNumber and Company to return all relevant LaborDtl records and then the in memory lookup should be much more efficient.

2 Likes