locked
Problem with join type in L2E - MVC Index View RRS feed

  • Question

  • I'm trying to write something which will alter my default view in an MVC app to map a text explanation from a look up table to an integer input from the parent table. The underlying db is Oracle and I'm using odp.net and EF 5 to connect to the db.

    In the parent table the CONTACT_REASON is an NUMBER(10,0) as is the VALCOD from the look up table.

    When I put it together in LinqPad, this query works:

    var query = from call in Vanguard
    join r in Van_reason on call.Contact_reason equals r.Valcod
    orderby call.Eid
    where call.Cdts.CompareTo(DateTime.Now.AddDays(-1).ToString("yyyyMMddHHmmss")) >= 0 && call.Cterm.StartsWith("$11")
    select new {call.Eid, call.Cdts, call.Cpers, call.Cterm, r.Eng_valcod};
    
    query.Dump();

    However, when I try to write it in MVC, I get a "The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'" error and at this stage, I'm feeling lost as to why.

    This is the working code in the MVC app

     var calls = from c in db.VANGUARDs
                          orderby c.EID
                          where c.CDTS.CompareTo(compTime) >= 0 && c.CTERM.StartsWith("$11")
                          select c;
              return View(calls.OrderByDescending(VANGUARD => VANGUARD.EID).ToList());

    Trying to write it per several different suggestions have not been productive.

    Thanks in advance to anyone who can help me get through this headache.

    Monday, October 13, 2014 7:47 PM

Answers

  • Hello Tony,

    >> In the parent table the CONTACT_REASON is an NUMBER(10,0) as is the VALCOD from the look up table.

    Do you mean that the Valcod from the look up table is also number(10,0).

    If not, you might need to change the type in database or explicitly cast the Valcod to number(10,0), or a different comparison in the join clause would cause this error, there is thread which discusses it at large.

    If it is also the number(10,0) type, since I do not have an Oracle database, I made a test with SQL Sever database with the equal type numeric(18,0), the Newolumn1 and Count are both numeric(18,0) type:

    var query = (from call in db.Orders
    
                                join r in db.OrderDetails on call.NewColumn1 equals r.Count
    
                                select call).ToList();

    While it works fine even in a MVC project.

    Considering that you are working with odp.net and Entity Framework 5, my suggestion is that you could try to update EF to a higher version and you could confirm with the Oracle experts if the number type is supported in Oracel Entity Framework:

    https://community.oracle.com/welcome

    And you could also post it to the MVC forum since you are working with a MVC project, there MVC experts who might have experience for such issue.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Tuesday, October 14, 2014 2:19 AM
    • Marked as answer by Tony Dunsworth Thursday, October 16, 2014 1:47 PM
    Tuesday, October 14, 2014 2:11 AM

All replies

  • Hello Tony,

    >> In the parent table the CONTACT_REASON is an NUMBER(10,0) as is the VALCOD from the look up table.

    Do you mean that the Valcod from the look up table is also number(10,0).

    If not, you might need to change the type in database or explicitly cast the Valcod to number(10,0), or a different comparison in the join clause would cause this error, there is thread which discusses it at large.

    If it is also the number(10,0) type, since I do not have an Oracle database, I made a test with SQL Sever database with the equal type numeric(18,0), the Newolumn1 and Count are both numeric(18,0) type:

    var query = (from call in db.Orders
    
                                join r in db.OrderDetails on call.NewColumn1 equals r.Count
    
                                select call).ToList();

    While it works fine even in a MVC project.

    Considering that you are working with odp.net and Entity Framework 5, my suggestion is that you could try to update EF to a higher version and you could confirm with the Oracle experts if the number type is supported in Oracel Entity Framework:

    https://community.oracle.com/welcome

    And you could also post it to the MVC forum since you are working with a MVC project, there MVC experts who might have experience for such issue.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Tuesday, October 14, 2014 2:19 AM
    • Marked as answer by Tony Dunsworth Thursday, October 16, 2014 1:47 PM
    Tuesday, October 14, 2014 2:11 AM
  • Fred,

    Thanks for that and making me check myself. It turns out, after looking at the table again in Oracle SQL Developer (Finally found something DbVis isn't as good at) that the VALCOD column is NUMBER(38,0) not NUMBER(10,0) so I can truncate the table, modify it, and rebuild it.

    So, once I get that done, I can't see how the query won't work. 

    Thursday, October 16, 2014 1:59 PM