LINQ Assistance - orderby

[Asz0ka (Adam Szoka)]

Hey Epigurus,

I’m working on a BPM to warn (and eventually throw an exception) when an operator is clocking into a job operation where there are previous op’s not marked complete.

The exception/warning is working, but I’m trying to give an informative message about the previous op that is incomplete: what op, who clocked into it last, when they clocked into it, resource group, etc.

I THOUGHT the easiest way to do this would be to get the laborDtl seq in a set argument/variable widget (using linq) in a BPM on Labor.Update, then use the that seq later with fill table by query widget.

I was able to get a linq statement semi-working, but it seems my orderby clauses are doing nothing in terms of sorting. I’m trying to pull in the lowest job operation and its highest related laborDtlSeq.

Here’s the linq:

(from theLaborDtlRow in Db.LaborDtl
join theJobOperRow in Db.JobOper 
on new {Company = theLaborDtlRow.Company, JobNum = theLaborDtlRow.JobNum, AssemblySeq = theLaborDtlRow.AssemblySeq, OprSeq = theLaborDtlRow.OprSeq} equals
new {Company = theJobOperRow.Company, JobNum = theJobOperRow.JobNum, AssemblySeq = theJobOperRow.AssemblySeq, OprSeq = theJobOperRow.OprSeq}
where theJobOperRow.OpComplete == false && theJobOperRow.JobNum == ttLaborDtlRow.JobNum && theJobOperRow.AssemblySeq == ttLaborDtlRow.AssemblySeq && theJobOperRow.OprSeq < ttLaborDtlRow.OprSeq
&& theLaborDtlRow.JobNum == ttLaborDtlRow.JobNum && theLaborDtlRow.AssemblySeq == ttLaborDtlRow.AssemblySeq && theLaborDtlRow.OprSeq < ttLaborDtlRow.OprSeq 
orderby theLaborDtlRow.OprSeq ascending, theLaborDtlRow.LaborDtlSeq descending
select theLaborDtlRow.LaborDtlSeq).DefaultIfEmpty(0).FirstOrDefault()

No matter what i’ve tried with the orderby clauses/statements, the statement returns the lowest laborDtlSeq. I’ve tried using just 1 “orderby … descending”, among many other combinations of orderby, and the result remains the same.

I’ve scoured the internet for linq examples similar to this, and even modified similar example statements in web browser c# compilers that worked as expected (joins + wheres + orderbys + firstordefault). I’m not quite sure where I’ve gone wrong.

Any suggestions would be much appreciated! May just need to move away from the one-liner…

[A.Baeisa (Al)]

Hi @Asz0ka,
have you seen this post ?, my method/code will do what you want and give you the information you need about the previous operations. all what you need is to add another foreach loop within the Db.LaborDtl and cast your info inside a one string or a string list (Var table)

EDIT: once you create this list you can use to sort it the way you want

List.OrderByDescending

How to reference a form checkbox in a BPM

Hi @Lee, i see what you mean, i.e. at Start Activity method the Job Oper record is not yet loaded, so the easiest and correct way -in my opinion- is to trigger a BPMCallContext variable at Post-Process StartActivity then capture it at the Pre-Process Update method, so my design will stop any one to login on any operation as long as its previous operation has not been completed, Note: this solution will not correct history but going forward each start operation will validate previous one i.e. n…

[Asz0ka (Adam Szoka)]

Hey Al,

Thanks for the code, definitely put me in the right direction!

Here’s what I ended up getting to work in the code block. For whatever reason by orderbys weren’t making it to tsql, .OrderBy(…) seems to have though!

var ttLaborDtlRow = (from ttLaborDtl_Row in ttLaborDtl where ttLaborDtl_Row.Company == Session.CompanyID      
select ttLaborDtl_Row).FirstOrDefault();

var laborDataRow = (from theLaborDtlRow in Db.LaborDtl
join theJobOperRow in Db.JobOper 
on new {Company = theLaborDtlRow.Company, JobNum = theLaborDtlRow.JobNum, AssemblySeq = theLaborDtlRow.AssemblySeq, OprSeq = theLaborDtlRow.OprSeq} equals
new {Company = theJobOperRow.Company, JobNum = theJobOperRow.JobNum, AssemblySeq = theJobOperRow.AssemblySeq, OprSeq = theJobOperRow.OprSeq}
where theJobOperRow.OpComplete == false && theJobOperRow.JobNum == ttLaborDtlRow.JobNum && theJobOperRow.AssemblySeq == ttLaborDtlRow.AssemblySeq && theJobOperRow.OprSeq < ttLaborDtlRow.OprSeq
&& theLaborDtlRow.JobNum == ttLaborDtlRow.JobNum && theLaborDtlRow.AssemblySeq == ttLaborDtlRow.AssemblySeq && theLaborDtlRow.OprSeq < ttLaborDtlRow.OprSeq 
select theLaborDtlRow).OrderBy(x=>x.OprSeq).ThenByDescending(y=>y.LaborDtlSeq).FirstOrDefault();

LastLaborDtlSeq = laborDataRow.LaborDtlSeq;

[A.Baeisa (Al)]

@Asz0ka

For whatever reason by orderbys weren’t making it to tsql, .OrderBy(…) seems to have though!

depends on your List dimensions if it is only one dimension then .OrderByxxxx will work, but if it is more than one column then you need to specify what column you need the list to be sorted by.