Query with multiple table

[Olanrewaju Ogunseye]

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

[Petko_I]

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

[Olanrewaju Ogunseye]

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;

[Petko_I]

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