Sort BPM query results by date using c#

I have a UD Field on the Part Bin table which is date stamped when an entry is made to the PartBin table.
I am wanting to update the Material Queue record (Auto Replen Trans Type) with the Bin Number which has the oldest date from the PartBin.

I have created the main bulk of the code but i cannot sort through a datetime like i would be able to an integer.

Is there anyone who could help me?

My current code is below as though I would sieve through an integer field.
Also i am yet to figure out once i have the oldest record how to set the MtlQueue.FromBinNum as the PartBin.BinNum

int x =0;
var ttMtlQueue_xRow = (from row in ttMtlQueue select row).FirstOrDefault();
if (ttMtlQueue_xRow != null)
{

foreach(var PartBin_Row in (from row in Db.PartBin.With(LockHint.UpdLock) where row.Company == ttMtlQueue_xRow.Company && row.PartNum == ttMtlQueue_xRow.PartNum select row))
{
if( x < PartBin_Row[“lastreceiveddate”])
{
x = PartBin_Row[“lastreceiveddate”];
}
}

ttMtlQueue_xRow.FromBinNum = x.ToString();

}

You could do it in two steps:

  1. Find oldest lastreceiveddate with:
oldestDate = Db.PartBin.Where( r =>r.Company == ttMtlQueue_xRow.Company && row.PartNum == ttMtlQueue_xRow.PartNum).Min( r =>r.lastreceiveddate);
  1. Find the Bin with that date
binNum = Db.PartBin.Where( r =>r.Company == ttMtlQueue_xRow.Company && row.PartNum == ttMtlQueue_xRow.PartNum && r.lastreceiveddate == oldestDate).Select( r =>r.BinNum).FirstOrDefault();

A couple of notes:

  • I haven’t tried this exact thing, because I don’t have that field in my PartBin table.
  • This ignores sites/plants and warehouses might ant to add a few more items to the WHERE clauses in those two
  • If two (or more) bins (for the given partnum) have the same lastreceiveddate, no guarantee of which would be returned.

This LOOKS like you are trying to update the BINNUM field with the DATE found in the PartBin.LastReceivedDate field… that is confusing to me.

Did you know you can OrderBy in your linq? then you don’t need to do your foreach loop… the first one retrieved will be the record you want… ANYWAY… what i THINK you want is the following which will return the BIN that has the oldest last received date. If there

var myQueue = ttMtlQueue.FirstOrDefault();
if (myQueue != null) {
    string oldestBinNum = Db.PartBin.Where(p =>
            p.Company == myQueue.Company &&
            p.PartNum == myQueue.PartNum)
        .OrderBy(p => p.lastreceiveddate)
        .Select(p => p.BinNum).FirstOrDefault() ?? "";
    if (oldestBinNum != "") {
        myQueue.FromBinNum = oldestBinNum;
    }
}

So, some explaining on this… (note… that the code is NOT TESTED).

  • the first line, i simplifed… changing the variable name to “myQueue”.
  • The PartBin Query:
  1. there was no need to LOCK the record.
  2. I did an OrderBy on the received date…
  3. I selected only the one variable that is being used… this speeds up the query
  4. I also defaulted the value to blank if nothing is found (the two Question marks is called a C# Null Coalescing and Null Conditional Operators - Dot Net Perls operator… it really simplifies code when wanting to default a value if nothing is found.
  5. we set the field “oldestBinNum” string to the value found.
  • THEN… if there is a bin number, I set the value.
1 Like

How does one specify ASC vs DESC in a LINQ’s .OrderBy(...) clause?

Ahh… here is where “Google is your friend”… simply type “Linq orderby desc” and you get all sorts of hints: https://www.tutorialsteacher.com/linq/linq-sorting-operators-orderby-orderbydescending

But the answer is… “OrderBy” is always ascending or Oldest date first. If you wanted the NEWEST received date, you would change the query to have “.OrderByDescending”:

string oldestBinNum = Db.PartBin.Where(p =>
            p.Company == myQueue.Company &&
            p.PartNum == myQueue.PartNum)
        .OrderByDescending(p => p.lastreceiveddate)
        .Select(p => p.BinNum).FirstOrDefault() ?? "";

(See, I am really not that smart… I just use google. :wink: )

Thanks for this.

It may be working however I have some ‘lastreceiveddate’ with null values and I believe this will show Null values first. Can these null values be ignored?

I have added the following above the first order by which did not end up working.

.OrderByDescending(p => p.lastreceiveddate_c.HasValue)

Right this code works perfectly as i want it.
Thank you guys for the help and that website is pretty great so i will be using that in future.
Working code below.

var myQueue = ttMtlQueue.FirstOrDefault();
if (myQueue != null) {
string oldestBinNum = Db.PartBin.Where(p =>
p.Company == myQueue.Company &&
p.PartNum == myQueue.PartNum)
.OrderByDescending(p => p.lastreceiveddate_c.HasValue)
.ThenBy(p => p.lastreceiveddate_c)
.Select(p => p.BinNum).FirstOrDefault() ?? “”;
if (oldestBinNum != “”) {
myQueue.FromBinNum = oldestBinNum;
}
}

Thanks Tim. But everytime I Google questions about LINQ, none of the results use the syntax we most often use here. The format with

Db.<tablename>.Where( …).Select(…).FirstOrDefault()

Nearly all examples I find online use a list object for the source of the “query”

BTW - Is there a name for the syntax most often used on here?

The one with the .Where(p=> p.Company == ...)

@ckrusen I believe the two LINQ syntaxes are Comprehension and Lambda. Lambda can be more difficult to read at first and Comprehension is easier to read when grouping / joining (more like SQL). I believe you can mix both syntaxes

//Lambda
var i = Db.Customer.With(LockHint.NoLock).
        Where(p=>p.Company==callContextClient.CurrentCompany);
//Comprehension
var i = from cust in Db.Customer.With(LockHint.NoLock) 
        where cust.Company == callContextClient.Company 
        select cust;
1 Like