Hi, This is my first time really looking at Ling to LLBLGEN, so I may be headed down the wrong path, but I think I traced a query problem I'm having to the mapping of Datetime.Date in Oracle.

SD.LLBLGen.Pro.LinqSupportClasses.NET35 3.1.0.0 SD.LLBLGen.Pro.DQE.OracleODPNet.NET20 3.1.0.0 Oracle.DataAccess 4.112.2.0

The string predicate passed to the LLBLGenProProvider CreateQuery is " ( ExpectedArrivalDate.Date = @0) ", where ExpectedArrivalDate is an Oracle TIMESTAMP(6) field, mapped to a System.DateTime. The value parameter I'm passing in is also a DateTime. The debugger lists the lambda expression as:

"($var1.ExpectedArrivalDate).Date == .Constant<System.DateTime>(1/1/2013 12:00:00 AM)"

The generated SQL, however, just has:

WHERE ( ( ( ( ( "LPA_L1"."EXPECTED_ARRIVAL_DATE" = :p2)))))

as the where clause, requiring an exact Date and Time match, instead of just the date match.

DateTime.Date is listed as a supported property for Oracle in Linq to LLBLGEN, so I would expect it to be mapped to the Oracle TRUNC function. Do I need to add a custom function mapping to support this?

thanks, Andrew

SD.LLBLGen.Pro.LinqSupportClasses.NET35 3.1.0.0 SD.LLBLGen.Pro.DQE.OracleODPNet.NET20 3.1.0.0

These are not correct rutime libraries versions or build numbers. Please check the forum guideline thread for more details.

Oops, the Runtime is v2.0.50727 and the full version is 3.1.12.0222.

I've tested against Oracle 10g and 11 database servers. Here's the longer debug output:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource`1[MMUS.SAMTracks.BL.EntityClasses.VWReceivingSearchEntity]).Where(Param_0 => (Param_0.ExpectedArrivalDate.Date == 1/1/2013 12:00:00 AM)).OrderBy(Param_1 => Param_1.ItemCode).Take(1000)
Method Enter: DaoBase.PerformGetMultiAction
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT * FROM (SELECT "LPA_L1"."BULK" AS "Bulk", "LPA_L1"."BYPASS" AS "Bypass", "LPA_L1"."CAPTION" AS "Caption", "LPA_L1"."CLIENT_SYSTEM" AS "ClientSystem", "LPA_L1"."COMMENTS" AS "Comments", "LPA_L1"."COUPON" AS "Coupon", "LPA_L1"."DBROWNUM" AS "Dbrownum", "LPA_L1"."DESCRIPTION" AS "Description", "LPA_L1"."DESTINATION_LOCATION_ID" AS "DestinationLocationId", "LPA_L1"."EXPECTED_ARRIVAL_DATE" AS "ExpectedArrivalDate", "LPA_L1"."ISSUE_DATE" AS "IssueDate", "LPA_L1"."ISSUE_ID" AS "IssueId", "LPA_L1"."ITEM_CODE" AS "ItemCode", "LPA_L1"."ITEM_GROUP_ID" AS "ItemGroupId", "LPA_L1"."ITEM_ID" AS "ItemId", "LPA_L1"."ITEM_MASTER_DESCRIPTION" AS "ItemMasterDescription", "LPA_L1"."ITEM_MASTER_ID" AS "ItemMasterId", "LPA_L1"."ITEM_TYPE_CODE" AS "ItemTypeCode", "LPA_L1"."ITEM_TYPE_ID" AS "ItemTypeId", "LPA_L1"."LAST_RECEIPT_ID" AS "LastReceiptId", "LPA_L1"."LENGTH" AS "Length", "LPA_L1"."MATERIAL_ORDER_ID" AS "MaterialOrderId", "LPA_L1"."ORDER_QUANTITY" AS "OrderQuantity", "LPA_L1"."PUBLICATION_ID" AS "PublicationId", "LPA_L1"."READABLE_PAGES" AS "ReadablePages", "LPA_L1"."RECEIPT_DATE" AS "ReceiptDate", "LPA_L1"."RECEIPT_STATUS" AS "ReceiptStatus", "LPA_L1"."RECORD_TYPE" AS "RecordType", "LPA_L1"."REFERENCE" AS "Reference", "LPA_L1"."SALES_REP" AS "SalesRep", "LPA_L1"."SPECIAL_INSTRUCTIONS" AS "SpecialInstructions", "LPA_L1"."THICKNESS" AS "Thickness", "LPA_L1"."TOTAL_GROSS_WEIGHT" AS "TotalGrossWeight", "LPA_L1"."TOTAL_RECEIVED" AS "TotalReceived", "LPA_L1"."TOTAL_UNITS_RECEIVED" AS "TotalUnitsReceived", "LPA_L1"."UPDATE_SOURCE" AS "UpdateSource", "LPA_L1"."WAREHOUSE_CODE" AS "WarehouseCode", "LPA_L1"."WEIGHT" AS "Weight", "LPA_L1"."WEIGHT_MODE_CODE" AS "WeightModeCode", "LPA_L1"."WIDTH" AS "Width" FROM "V_RECEIVING_SEARCH" "LPA_L1" WHERE ( ( ( ( ( "LPA_L1"."EXPECTED_ARRIVAL_DATE" = :p2))))) ORDER BY "LPA_L1"."ITEM_CODE" ASC) WHERE rownum <= 1000
    Parameter: :p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/1/2013 12:00:00 AM.

Please show us your linq code. And you can use the .Date property of the EntityField you are filtering to produce the appropriate conversion, see this....

daelmo wrote:

Please show us your linq code. And you can use the .Date property of the EntityField you are filtering to produce the appropriate conversion, see this....

It was using some complex dynamic query generation code, so I rewrote a test in simple Linq.

The statement:

var rq = (from r in MetaData.VWReceivingSearch where r.ExpectedArrivalDate.Date == new DateTime(2013, 1, 1) orderby r.ItemCode select r).Take(1000);

produces the same SQL as above, which is the same as:

var rc2 = (from r in MetaData.VWReceivingSearch where r.ExpectedArrivalDate == new DateTime(2013, 1, 1) orderby r.ItemCode select r).Take(1000);

My understanding is that Datetime.Date is a default-mapped function for Oracle, so the .Date property should be generating SQL mapped to the TRUNC function or something similar. But for my environment, querying against a TIMESTAMP(6) field, .Date is apparently ignored. Note that Oracle also has a DATE type, for which this empty mapping might be appropriate.

First thing, could you please try the latest build of v.3.1?

Walaa wrote:

First thing, could you please try the latest build of v.3.1?

I didn't see anything in the change log, so for now I'm just going to assume that it's still broken, and use a custom function mapping.

Edit: Adding FunctionMapping(typeof(DateTime), "get_Date", 0, "TRUNC({0})") did the trick for me. I don't know if it's the most efficient function to use on the database server side, but it works for my purposes.

Edit 2: I finally thought to check the DynamicQueryEngine source code, and Date is still an empty mapping in 3.5, so, no it wouldn't have worked in the latest versions either.

I think you are right. Please let us take a closer look at this...

The function works OK for fields which are mapped onto a Date typed field (not causing an error).

But indeed, more oracle types are mapped as datetime, like the timestamp field, and this could cause a problem. TRUNC(date) indeed removes the time portion, so changing the func to have TRUNC() in it is better, and this also works for date fields.

I'll fix it in v3.1 and up. You can keep using your workaround, it won't conflict with the fix (as you simply override the original mapped function)

Attached is the fixed DQE for oracle ODP.NET v3.1

Otis wrote:

The function works OK for fields which are mapped onto a Date typed field (not causing an error).

Actually, the Oracle Date type can also store hours, minutes, and seconds. The default Oracle date format just hides that in normal use.

Anyhow, thanks for your help.

AHenry wrote:

Otis wrote:

The function works OK for fields which are mapped onto a Date typed field (not causing an error).

Actually, the Oracle Date type can also store hours, minutes, and seconds. The default Oracle date format just hides that in normal use.

You're right, I found that out in testing, I tried to fetch some rows from the DB using a new DateTime(year, month day) value, but it failed. Thanks for this info