converting int to string in linq to entites

Question

My Code is :

        var currency = (from ac in db.shop
          join cur in db.books
          on ac.CODE equals cur.CODE.ToString()  //here is the Error
          // because "ac.code is type strig" & "cur.code is type long 
          where ac.ID == merchantId
          select new Currency()
          {
                 ShopCode = ac.CODE,
                 PosCode = ac.POSCODE,
           }).ToList();

I found that .ToString(), SqlFunctions.StringConvert(long) are not working in the join query conditions but working in 'select' area in the query. However Devart.Data.Oracle.Entity.OracleFunctions.ToChar((long)cur.CODE) is working fine. Since I am using entity framework it shouldn't have problems with particular DB types (i.e. oracle or sql server). It should work even I change from oracle to sql in future.

Please give me a solution for this.

Can't you change the columns to be both long or both string? — Hans Keﬆing, Nov 11 '11 at 13:48

score 0 · Answer 1 · answered Nov 11 '11 at 07:57

0

Did you try casting on this.

Try : ac.CODE equals (string)cur.CODE

answered Nov 11 '11 at 07:57

Pankaj Upadhyay

12,966
24
73
104

No it's unable to recognise System.string() – Onlineuser Nov 11 '11 at 10:48
You can't cast long to string. You'll get an `InvalidCastException`. – jrummell Nov 11 '11 at 13:31

Massimo Zerbini · Answer 2 · 2011-11-11T13:20:04.693

0

You can create a VIEW Currency on the database and perform the query on the view.

Here is the list of supported method for Linq to Entities, if conversion is not supported you can not execute it. http://msdn.microsoft.com/en-us/library/bb738681.aspx

edited Nov 11 '11 at 13:20

answered Nov 11 '11 at 07:59

Massimo Zerbini

3,125
22
22

score 0 · Answer 3 · edited May 23 '17 at 12:29

The problem is, that EF is trying to convert whole your expression into T-SQL query. So it will look similar to this:

select ac.CODE, cur.CODE from shop ac
inner join books cur on ac.CODE = cur.CODE

Here is your problem. CODE fields have diffent types and server can't join on them. In T-SQL you can use CAST, but since EF don't support such operation you can't do anything. And afterall, why do you store those codes in string? If you have such a query, then in most cases there is some problem with your DB schema. I would suggest you to look at the schema and refactor it, so CODE is always of type long. Then everything will work.

If you still really want to use different types for you columns. You can look at this question, to see how to execute CAST Convert String to Int in EF 4.0

score 0 · Answer 4 · answered Nov 11 '11 at 14:24

this should solve your problem:

  var currency = (from ac in db.shop
          join cur in db.books
let codestr = cur.CODE.ToString()
          on ac.CODE equals codestr 
          where ac.ID == merchantId
          select new Currency()
          {
                 ShopCode = ac.CODE,
                 PosCode = ac.POSCODE,
           }).ToList();

converting int to string in linq to entites

4 Answers4