Query with multiple table

4 posts, 1 answers
  1. Olanrewaju Ogunseye
    Olanrewaju Ogunseye avatar
    5 posts
    Member since:
    Sep 2009

    Posted 19 Dec 2010 Link to this post

    I am making use of the ORM an application and needed  to query record from mysql backend and would require the result to be rendered on radgridview.

    when the table involed in the join is just two it worked perfectlly but on trying to increase the number of table involved in the query to three as shown below
    Dim loclist = From person In scope.Extent(Of Person)() Join sex In scope.Extent(Of Sex)() On person.TblSex Equals sex.TblSexID _
                              Join salutaion In scope.Extent(Of Salutaion)() On person.TblSalutation Equals salutaion.TblSalutaionID _
                              Where person.TblEMPNo.Contains(Search) _
                              Select New With { _
                              .Name = String.Concat(salutaion.TblSalutation, " ", String.Concat(String.Concat(person.TblSurname, " ", person.TblMiddleName), "", person.TblOtherNames)), _
                              .ID = person.TblID, _
                              .Sex = sex.TblSex _
                              }
    and tried binding the resultset to a radgrid a get the following error
     

    System.ArgumentOutOfRangeException was unhandled
      Message="Left join key not an extent bound iterator Parameter name: leftField Actual value was System.Object[]."
      ParamName="leftField"
      Source="Telerik.OpenAccess.Query"
     
    i dont know if there is any thing am doing wrong, i would appreciate any advice i can  get now
  2. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 22 Dec 2010 Link to this post

    Hello Olanrewaju Ogunseye,

    Can you give us details with regard to your model? We are interested in the class definitions of the Person, Sex and Salutation entities and specifically the types of the relevant properties participating in the query you provided. We have some difficulties reproducing the issue on our side and in order to continue with our investigation we will need to get as close to your scenario as possible. Can you also tell us the version of MySQL you are using? It seems that during our parsing process of the LINQ query somehow we encounter an object type for the left join key which does not match the right join key. The left join keys in the case for this query are person.TblSex and person.TblSalutation. Can you tell us what the types of  TblSex and TblSalutation properties in the Person class are? If we know the structure of your entities we may come up with an alternative query which is even more efficient. In general, you should prefer traversing of associations (when possible) instead of joins in LINQ. By traversing associations what we mean is use dot syntax to get to related entities reachable from a given type in a query - for example if you have a reference to a Salutation object in the Person class, you can use something like person.Salutation.TblSalutation to get access to the property values in the corresponding Salutation object.

    We are looking forward to your reply.

    Best wishes,
    Petko_I
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
  4. Olanrewaju Ogunseye
    Olanrewaju Ogunseye avatar
    5 posts
    Member since:
    Sep 2009

    Posted 23 Dec 2010 Link to this post

    Thanks for your response am using Mysql VERSION 5.1.32  and used reverse mapping to generate the class Person,Sex and Salutation the and the query script used in  generating the table is


    CREATE TABLE `person` (
      `tbl_ID` INT(11) NOT NULL AUTO_INCREMENT,
      `tbl_EMP_No` VARCHAR(20) DEFAULT NULL,
      `tbl_Salutation` INT(11) NOT NULL,
      `tbl_Surname` VARCHAR(30) DEFAULT NULL,
      `tbl_Middle_Name` VARCHAR(30) DEFAULT NULL,
      `tbl_OtherNames` VARCHAR(60) DEFAULT NULL,
      `tbl_Sex` INT(11) NOT NULL,
      `tbl_Address` VARCHAR(200) DEFAULT NULL,
      `tbl_Local_Government_Area` INT(11) NOT NULL,
      `tbl_Phone` VARCHAR(30) DEFAULT NULL,
      `tbl_Email` VARCHAR(70) DEFAULT NULL,
      `tbl_Date_Modified` DATETIME NOT NULL,
      `tbl_Modified_By` INT(11) NOT NULL,
      PRIMARY KEY (`tbl_ID`),
      KEY `FK_person_Salutation` (`tbl_Salutation`),
      KEY `FK_person_Sex` (`tbl_Sex`),
      CONSTRAINT `FK_person_Salutation` FOREIGN KEY (`tbl_Salutation`) REFERENCES `salutaion` (`tbl_Salutaion_ID`) ON UPDATE CASCADE,
      CONSTRAINT `FK_person_Sex` FOREIGN KEY (`tbl_Sex`) REFERENCES `sex` (`tbl_Sex_ID`) ON UPDATE CASCADE
    ) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;



    CREATE TABLE `salutaion` (
      `tbl_Salutaion_ID` INT(11) NOT NULL AUTO_INCREMENT,
      `tbl_Salutation` VARCHAR(20) DEFAULT NULL,
      PRIMARY KEY (`tbl_Salutaion_ID`)
    ) ENGINE=INNODB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;





    CREATE TABLE `sex` (
      `tbl_Sex_ID` INT(11) NOT NULL AUTO_INCREMENT,
      `tbl_Sex` VARCHAR(8) DEFAULT NULL,
      PRIMARY KEY (`tbl_Sex_ID`)
    ) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
  5. Answer
    Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 23 Dec 2010 Link to this post

    Hello Olanrewaju Ogunseye,

    Thank you for providing us your database schema. As we suspected the query can be rewritten when you have references to the Salutation and Sex persistent types from the Person class. Here are two alternatives queries that should work on your side:
    Dim loclist = From person In scope.Extent(Of Person)() _
                  Where person.TblEMPNo.Contains(Search) _
                  Select New With { _
                              .Name = String.Concat(person.Salutaion.TblSalutation, " ", String.Concat(String.Concat(person.TblSurname, " ", person.TblMiddleName), "", person.TblOtherNames)), _
                              .ID = person.TblID, _
                              .Sex = person.Sex.TblSex _
                              }
     
    Dim loclist2 = From person In scope.Extent(Of Person)() _
                   Where person.TblEMPNo.Contains(Search) _
                   Select New With { _
                              .Name = String.Concat(person.Salutaion.TblSalutation, " ", person.TblSurname, " ", person.TblMiddleName, "", person.TblOtherNames), _
                              .ID = person.TblID, _
                              .Sex = person.Sex.TblSex _
                              }
    So, you can reverse map your classes with the appropriate collections and references to mark the existence of associations between your entities. Here is a link that elaborates on the option "Create one-to-many list" in the Reverse Mapping Wizard. You can always run it again and let it generate the additional reference properties.
    Partial Public Class Person
     
    'Other properties
     
    <Telerik.OpenAccess.FieldAlias("_salutaion")> _
        Public Property Salutaion() As Salutaion
            Get
                Return _salutaion
            End Get
            Set( ByVal Value As Salutaion )
                Me._salutaion = Value
            End Set
        End Property
      
        <Telerik.OpenAccess.FieldAlias("_sex")> _
        Public Property Sex() As Sex
            Get
                Return _sex
            End Get
            Set( ByVal Value As Sex )
                Me._sex = Value
            End Set
        End Property
      
     
    End Class
    We have added the issue with the joins to our internal issue tracking system and will address it when we proceed with the improvements for our LINQ support. 

    We find it worth mentioning that should you decide to use the Visual Designer, many of the tasks you had to perform manually with the classic wizards previously, are now automated with the designer (including the code generation of the appropriate association ends).

    Do no hesitate to contact us, should you have any questions.

    All the best,
    Petko_I
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Back to Top