LINQ Filter by Date

Epicor ERP 10
,
1

Hi,
I am trying to get the records that has EffectiveDate less than 2 months old. I am not sure how to do the filtering for this BPM. The current code throws out a NotSupportedException error. Below is my code.

Thank you!

var quoteMtlInfo = from quoteMtl in Db.QuoteMtl join priceInfo in Db.VendPBrk on new {quoteMtl.Company, quoteMtl.PartNum} equals new {priceInfo.Company,priceInfo.PartNum} where quoteMtl.QuoteNum == callContextBpmData.Number01 && quoteMtl.QuoteLine == callContextBpmData.Number02 && priceInfo.BreakQty == 1 && priceInfo.EffectiveDate > DateTime.Today.AddMonths(-2)
select new {quoteMtl.PartNum,priceInfo.EffectiveDate, quoteMtl.QuoteNum, quoteMtl.QuoteLine};

2

I think I had to use Now.AddDays(30) to get a value to test against. Not near a computer but I’ll check on Monday.

3

It might be “Today.AddDays” instead of “Now.AddDays”… I always get confused with date fields because there are dates with just the DATE and date fields that are Date AND Time… and these are not interchangeable.
Check out: C DateTime to add days to the current date
and: C# DateTime Examples - Dot Net Perls
and: c# - Get only the Date without Time from DateTime - Stack Overflow

4

I’ve done a few date comparison BPM’s
I’ve always had to set the date as a variable outside the queiry

Try this

var twomonthsago = DateTime.Today.AddDays(-60) 

// var twomonthsago = DateTime.Today.AddMonths(-2) 

var quoteMtlInfo = from quoteMtl in Db.QuoteMtl join priceInfo in Db.VendPBrk on new {quoteMtl.Company, quoteMtl.PartNum} equals new {priceInfo.Company,priceInfo.PartNum} where quoteMtl.QuoteNum == callContextBpmData.Number01 && quoteMtl.QuoteLine == callContextBpmData.Number02 && priceInfo.BreakQty == 1 && priceInfo.EffectiveDate > twomonthsago
select new {quoteMtl.PartNum,priceInfo.EffectiveDate, quoteMtl.QuoteNum, quoteMtl.QuoteLine};
5

that does it. thank you for all the replies!

6

There are also a set of functions you can use for linq. The statement below compiles in K21, but I don’t have a way to test.

System.Data.Entity.SqlServer.SqlFunctions // StringConvert, DateDiff...
System.Data.Entity.DbFunctions // For EF Functions like AddDays or TruncateTime, left, right and like%

var quoteMtlInfo = from quoteMtl in Db.QuoteMtl join priceInfo in Db.VendPBrk on new {quoteMtl.Company, quoteMtl.PartNum} equals new {priceInfo.Company,priceInfo.PartNum} where quoteMtl.QuoteNum == callContextBpmData.Number01 && quoteMtl.QuoteLine == callContextBpmData.Number02 && priceInfo.BreakQty == 1 && 
System.Data.Entity.SqlServer.SqlFunctions.DateDiff("month",DateTime.Now,priceInfo.EffectiveDate) < 2
select new {quoteMtl.PartNum,priceInfo.EffectiveDate, quoteMtl.QuoteNum, quoteMtl.QuoteLine};
7

System.Data.Entity.SqlServer.SqlFunctions.DateDiff

Not sure… but I know that in the cloud there are some additional rules about sql insertions… this may not be compatible with companies in the cloud.