0

Alright. I worked so hard over the past few weeks to learn the .NET data provider for teradata and set up an infrastructure to query Teradata data sources. Everything is great except....

Canonical Entity functions are not supported by the provider. Alright. Then I look at the link below which says I should use "Teradata.DiffDays" and "EntityFunctions.DiffDays".

http://developer.teradata.com/doc/connectivity/tdnetdp/14.10/webhelp/EntityProviderCanonicalFunctions.html

I merrily try to use "Teradata.DiffDays" in my LINQ to Entities query thusly:

var results = IMAccts.Where(acct => acct.ACCT_CLSD_DT != null && Teradata.DiffDays(ACCT_OPEN_DT, ACCT_CLSD_DT) >= 90).Dump();

The type or namespace name 'DiffDays' does not exist in the namespace 'Teradata'.

However, I observe that the same Teradata.DiffDays can be used in Entity SQL like this:

select Teradata.DiffDays(ACCT_OPEN_DT,ACCT_CLSD_DT) as diff from IMAccts

My questions are:

1) How is it possible that the function under the "Teradata" namespace is recognized in Entity SQL and not in LINQ to Entities?

2) Is it possible for me to work around this by supplying my own DateDiff function? (Please note that collecting the data locally and then applying date manipulation is definitely not an option)

Sundar Venkataraman
  • 133
  • 6

1 Answers1

0

Regarding Q2: All those functions are simply translated by the driver to valid Teradata SQL:

select ACCT_OPEN_DT-ACCT_CLSD_DT as diff from IMAccts

I don't know about the rules for DateDiff, so you might have to change it to ACCT_CLSD_DT-ACCT_OPEN_DT

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • This is not true. I have these two dates mapping to the .NET datatype "DateTime". In fact, subtracting the dates was the first method that I attempted and the error that I got was "DbArithmeticExpression arguments must have a numeric common type". – Sundar Venkataraman Jul 27 '13 at 16:57
  • Oops, seems the provider doesn't support native statements like this, you might try to add an explicit cast: select CAST(ACCT_OPEN_DT-ACCT_CLSD_DT as INT) as diff from IMAccts – dnoeth Jul 29 '13 at 07:24
  • Please note that the fields are mapped to .NET datatypes.The problem is not that the result is not cast to integer. The provider is not able to allow subtraction of two datetimes in the first place. Casting can even come into picture only after the subtraction is recognized. Anyway, trying this cast in the .NET way (int)(acct.ACCT_CLSD_DT - acct.ACCT_OPEN_DT) yields the same error message. – Sundar Venkataraman Jul 29 '13 at 14:47