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

[postwick (Paul Ostwick)]

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

[Anil_G (Anil Gorthi)]

@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

[ppr (Peter Preuss)]

@postwick

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)

(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:

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

[postwick (Paul Ostwick)]

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)

[postwick (Paul Ostwick)]

@ppr

Let chkA = dif >= dum Or dif <= (dum + 2)

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.

[ppr (Peter Preuss)]

@postwick

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

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

[postwick (Paul Ostwick)]

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!