Help modifying a LINQ query to add an IF and numeric range instead of exact equals

So I have a LINQ query:

sheetsDict(sheetName).AsEnumerable.Where(Function(r) DateDiff(DateInterval.Day,Now,DateTime.ParseExact(r("Maturity Date").ToString),"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)) = CInt(Config("Days Until Maturity").ToString)).ToList

The basic logic is to look for rows a specific number of days in the future. Now I want to modify that logic so on Friday it looks for things that number of days out OR two additional days out. In other words, instead of “=95” I want “>= 95 and <=97”

Is there a simpler way to do it than duplicating the whole DateDiff repeatedly like this:

sheetsDict(sheetName).AsEnumerable.Where(Function(r) If(Now.ToString("dddd").Equals("Friday"), DateDiff(DateInterval.Day,Now,DateTime.ParseExact(Split(r("Maturity Date").ToString," ")(0),"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)) >= CInt(Config("Days Until Maturity").ToString) Or DateDiff(DateInterval.Day,Now,DateTime.ParseExact(Split(r("Maturity Date").ToString," ")(0),"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)) <= CInt(Config("Days Until Maturity").ToString)+2, DateDiff(DateInterval.Day,Now,DateTime.ParseExact(Split(r("Maturity Date").ToString," ")(0),"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)) = CInt(Config("Days Until Maturity").ToString))).ToList

@postwick

A little restructuring is possible

sheetsDict(sheetName).AsEnumerable.Where(Function(r) DateDiff(DateInterval.Day,Now,DateTime.ParseExact(Split(r("Maturity Date").ToString," ")(0),"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)) >= CInt(Config("Days Until Maturity").ToString) Or DateDiff(DateInterval.Day,Now,DateTime.ParseExact(Split(r("Maturity Date").ToString," ")(0),"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)) <= If(Now.ToString("dddd").Equals("Friday"), CInt(Config("Days Until Maturity").ToString)+2, CInt(Config("Days Until Maturity").ToString))).ToList

Cheers

(From r in sheetsDict(sheetName).AsEnumerable
Let isFriday = now.DayOfWeek.equals(System.DayOfWeek.Friday)
Let ds = Split(r("Maturity Date").ToString," ")(0)
Let dp = DateTime.ParseExact(ds,"M/d/yyyy", CultureInfo.InvariantCulture)
Let dif = DateDiff(DateInterval.Day,Now,dp)
Let dum = CInt(Config("Days Until Maturity").ToString) 
Let chkA = dif >= dum Or dif <= (dum + 2)
Let chkB = dif.Equals(dum)
Where If(isFriday, chkA, chkB)
Select d = r).toList

Ensure the following:
import_systemglobalization

  • we did shift to Query Syntax
  • reduced the redundancies
  • kept the initial statements (e.g. DateDif, Split) to be closed at the origin base
1 Like

OK this is great, thank you, I really appreciate it. I did remove a couple things before the DateDiff to simplify the question, so how would I add in some additional conditions?

Where(Function(r) r("CREATED_IN_NCINO").ToString.ToUpper <> "YES" And r("CREATED_IN_NCINO").ToString.ToUpper <> "NO" And r("ON_PREVIOUS_TABS").ToString <> "YES" AndAlso DateDiff(DateInterval.Day,Now,DateTime.ParseExact(Split(r("Maturity Date").ToString," ")(0)...

Do I just add those back into the Where like this?

Where r("CREATED_IN_NCINO").ToString.ToUpper <> "YES" And r("CREATED_IN_NCINO").ToString.ToUpper <> "NO" And r("ON_PREVIOUS_TABS").ToString <> "YES" AndAlso If(isFriday, chkA, chkB)

One other note for others who may come across this post, I changed this to And instead of Or, because it needs to be “between” logic.

Lets assume we have the right understanding of the new filter requirements. We can exploit the split Where which is acting like an And / AndAlso

(From r in sheetsDict(sheetName).AsEnumerable
Where Not r("ON_PREVIOUS_TABS").ToString.ToUpper.Trim.Equals("YES")
Where Not {"YES","NO"}.Any(function (f) r("CREATED_IN_NCINO").toString.ToUpper.Trim.Equals(f))
Let isFriday = now.DayOfWeek.equals(System.DayOfWeek.Friday)
Let ds = Split(r("Maturity Date").ToString," ")(0)
Let dp = DateTime.ParseExact(ds,"M/d/yyyy", CultureInfo.InvariantCulture)
Let dif = DateDiff(DateInterval.Day,Now,dp)
Let dum = CInt(Config("Days Until Maturity").ToString) 
Let chkA = dif >= dum AndAlso dif <= (dum + 2)
Let chkB = dif.Equals(dum)
Where If(isFriday, chkA, chkB)
Select d = r).toList

the part <> Yes And <> No was rewritten as we do understand that we are interrested on all when it is different to a Yes or No. But here just cross-check if we are right with this flip

Just adding my additional conditions into the existing Where did work, but I like your solution. Very elegant and organized. Much easier to understand than what I originally had, even without the new requirements. Thanks!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.