Joining poorly designed tables in Oracle?

[rmagruder]

I have a legacy database which has a char(5) field that is being joined with a integer field in another table.

Although the table containing the char(5) field DOES have some non-numeric values, obviously the table with the integer field would never join with them.

Oracle SQL has no problem doing a WHERE join between these two fields. I need to do this with Entity Framework 6 (I'm doing database-first on this). What's the best way to fix this?

1. Lie about the field types in the edml file and regenerate the classes? (if I can even get away with it) and tell it that the integer field is REALLY a char field so it will put the values from the table into string properties?

2. Some other way to make a join work ? The SQL Server EF stuff has SqlFunctions to solve this problem but I'm not aware of those being possible in Oracle?

Thanks,
Randy

[rmagruder]

Update: Trying approach one and telling the Entity Model and Storage model that the integer field is really a string resulted in an Oracle error: Invalid Number when executing the query.

Still stumped.

[Shalex]

Please try suggestion from https://social.msdn.microsoft.com/Forum ... =linqtosql:

Code: Select all

from t1 in datacontext.table1
join t2 in datacontext.table2 on t1.intField.ToString() equals t2.stringField
select ...

[rmagruder]

You know, I had this working, but now I'm getting :

LINQ to Entities does not recognize the method 'System.String ToChar(System.Nullable`1[System.Decimal])' method, and this method cannot be translated into a store expression.

This is the syntax inside my LINQ to Entities Query, and I had this working...so I can't figure out why today I'm getting the above error.

Code: Select all

join l in db.FLD_LOOKUP_T on new { f1 = OracleFunctions.ToChar((decimal?)a.IFS_TYP_CD), f2 = 312 } equals new { f1 = l.FieldCode.Trim(), f2 = l.FieldNumber } into gjl                
                                from l1 in gjl.DefaultIfEmpty()

Does anything look suspicious to you? Could it be a driver/assembly reference problems? A configuration issue?

Randy

[rmagruder]

I want to add that this WAS working. I am stymied trying to figure out what happened.

The DevArt.Data.Oracle.Entity is being referenced, and that's where these functions are, right?

The web.config is referencing it, and hasn't been changed.

So -- how can I make this work?

Thanks,
Randy

[rmagruder]

Found the solution.

In one of my Projects in my solution, the Data Access Layer solution, I was including a reference to the EF5 version of Devart.Data.Oracle.Entity rather than EF6. So even though other, higher level projects were referring to the right one, this one was referring to the wrong one, and it blows up at runtime.

Thanks!

[Shalex]

Glad to hear that all issues related to dotConnect for Oracle are fixed.