c# Linq Oracle convert long to string for join

Question

I'm having the following issue, in trying to join up two tables in Oracle. One of the tables has a string value and the other is a long.

var query = (from d in context.entity
join m in context.entity2
on d.ordernum.Substring(2) equals m.ordernum.ToString())
select new { d.ordernum, d.customer, m.recordkey});
var items = query.ToList();

The issue is that this does not work with Oracle

Cant use SQLFunctions as this is an Oracle Database
Cant use .ToString()

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.
Cast use long.Parse()

LINQ to Entities does not recognize the method 'Int64 Parse(System.String)' method, and this method cannot be translated into a store expression.

Any helps would be appreciated.

`ToString` is supported in LINQ to Entities. What version of EF are you using? — Ivan Stoev, Aug 09 '16 at 18:16
The EF is 6.1.3 and as shown above when we use the ToString() we get the error that Linq does not recognize the method. — DenisJC, Aug 10 '16 at 07:46

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

You can use AsEnumerable() and thus it will run in the C# and won't get those errors.

var reault = (from d in context.entity.AsEnumerable() 
             join m in context.entity2.AsEnumerable() 
             on d.ordernum.Substring(2) equals m.ordernum.ToString()
             select new 
             { 
                 d.ordernum, 
                 d.customer,
                 m.recordkey
             }).ToList();

Otherwise you can extend it and write your "own" functions. Something like: (not tested)

<Function Name="OracleSubString" ReturnType="Edm.String">
  <Parameter Name="order_number" Type="System.Int64" />
  <Parameter Name="start_index" Type="System.Int32" />
  <DefiningExpression>
   SUBSTR(CAST (order_number as varcahr(100)),start_index, LEN(CAST (order_number as varcahr(100)) - start_index)
  </DefiningExpression>
</Function>

edited May 23 '17 at 11:51

Community

1
1

answered Aug 09 '16 at 15:19

Gilad Green

36,708
7
61
95

1

I think by my common sense that he is trying to get the query on database instead of grab all the items into memory. Is not feasible I think. – gds03 Aug 09 '16 at 15:33
@anotherNeo - I would also prefer in the database but from what I currently know `SqlFunctions` is the way (but isn't supported for Oracle by what he says..) – Gilad Green Aug 09 '16 at 15:35
@Denisjc - which function the `AsEnumerable()` or the "write you own functions"? – Gilad Green Aug 10 '16 at 08:11
The function would have the same result as you need to still have a way to cast the long to a string. So what would you put in the function to perform the cast? `longValue.ToString()` this will result in the same issue `The specified method 'System.String LongToString(Int64)' cannot be translated into a LINQ to Entities store expression.` – DenisJC Aug 10 '16 at 08:12
@Denisjc - edited answer. Also for the ToString() you will have to write a function that does it (or use the substring one with starting index of 0 but it isn't really nice) – Gilad Green Aug 10 '16 at 08:20
I think is a pretty much basic operation cast a column to a type and make a join. Hope that is only Oracle that don't have this, since is quite natural that people need this and they need to go to other options. – gds03 Aug 12 '16 at 11:10
1

@anotherNeo - for the others then the SQLFunctions/ parsing should work... Oracle.. I guess not – Gilad Green Aug 12 '16 at 11:18
we used a view in the end it was a lot simpler to just do this in SQL – DenisJC Sep 19 '16 at 08:38

c# Linq Oracle convert long to string for join

1 Answers1