Joining poorly designed tables in Oracle?

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
rmagruder
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Joining poorly designed tables in Oracle?

Post by rmagruder » Tue 09 Dec 2014 16:04

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
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Joining poorly designed tables in Oracle?

Post by rmagruder » Tue 09 Dec 2014 16:16

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Joining poorly designed tables in Oracle?

Post by Shalex » Wed 10 Dec 2014 15:14

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
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Joining poorly designed tables in Oracle?

Post by rmagruder » Wed 17 Dec 2014 21:38

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
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Joining poorly designed tables in Oracle?

Post by rmagruder » Thu 18 Dec 2014 15:49

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
Posts: 47
Joined: Thu 26 Jun 2014 17:12

Re: Joining poorly designed tables in Oracle?

Post by rmagruder » Thu 18 Dec 2014 20:02

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Joining poorly designed tables in Oracle?

Post by Shalex » Fri 19 Dec 2014 15:50

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

Post Reply