Get data from another table [With no direct relation with]

[basselz]

Hi @volkanceylan

I have this scenario I have thee tables X, Y, Z X is linked to Y with a foreign key [ X.yID] Y is linked to Z with a foreign key [Y.zID] in the editing dialog of X I need to get the matching value from Z (i.e [Z.Name] ) for example X {id: 1, Name: xxx, yID:1 } Y {id:1, Name: yyy, zID:1} Z{id:1, Name: zzz}

so I need to get Z.Name I tried to add a property to XRow class and populate in the MyRetreiveHandler class in XRepository with no success I tried to do it with LinkingSetRelation with no success

what's the correct approach here must be??

[Nathan2020-0126]

Hi basselz, i have met the similar situation with you. i have 3 tables TraveTask{id, CityID} City{CityID, CiteCategotyID} CityCategotyID{CiteCategotyID, CiteCategotyText}

i do as below. in CityRow.cs

 [DisplayName("City Category"), Column("cityCategoryID"), ForeignKey(typeof(CityCategoryRow)), LeftJoin("c"), LookupInclude]
        [LookupEditor(typeof(CityCategoryRow))]
        public Int32? CityCategoryId
        {
            get { return Fields.CityCategoryId[this]; }
            set { Fields.CityCategoryId[this] = value; }
        }
        [DisplayName("City Category"), Size(20), Width(280), Origin("c"), LookupInclude]
        public String CityCategoryText
        {
            get { return Fields.CityCategoryText[this]; }
            set { Fields.CityCategoryText[this] = value; }
        }

Then we can get the CityCategoryID also CityCategoryText in TravelTaskRow.cs

 [DisplayName("To City"), Column("toCityID"), ForeignKey(typeof(CityCategoryRow)), LeftJoin("tc"),NotNull]
        [CityEditor]
        public Int32? ToCityId
        {
            get { return Fields.ToCityId[this]; }
            set { Fields.ToCityId[this] = value; }
        }
        [DisplayName("ToCity CategotyID"), Expression("tc.cityCategoryID")]
        public Int32? ToCityCategoryID
        {
            get { return Fields.ToCityCategoryID[this]; }
            set { Fields.ToCityCategoryID[this] = value; }
        }
        [DisplayName("ToCity Categoty"), Expression("tc.cityCategoryText")]
        public String ToCityCategoryText
        {
            get { return Fields.ToCityCategoryText[this]; }
            set { Fields.ToCityCategoryText[this] = value; }
        }

Hope could help you.

[Nathan2020-0126]

Sorry, i have a mistake. it don't work.

[basselz]

Hi @PanChenhao thanks for the help I have tried that solution already, and no success

[Estrusco]

Use some like this.

Table X:

    [ForeignKey(typeof(Y)), LeftJoin("y")]
    public Int32? yID
    {
        get { return Fields.yID[this]; }
        set { Fields.yID[this] = value; } 
    }

    [Expression("y.zID")]
    [ForeignKey("[dbo].[zTable]", "ID"), LeftJoin("myZTable")]
    Int32? zID
    {
        get { return Fields.zID[this]; }
        set { Fields.zID[this] = value; }
    }

    [Expression("myZTable.[Name]")]
    public String zIDName
    {
        get { return Fields.zIDName[this]; }
         set { Fields.zIDName[this] = value; }
    }

[basselz]

@Estrusco it worked thanks alot but I have to ask though ... is there any more direct way for getting data? (server side I mean ) like Z.getById (zId) and if there's no direct way what is the client side method to retrieve from a table (i.e: what method in XDialog.ts I have to override and what function to use)

[Estrusco]

You can override some methods in RetrieveRequestHandler of your Repository, for example PrepareQuery and act on query. Or, in these method, use other Repository or Connection object to get data. The difference between Repository and Connection is that the Repository process calls the behaviors of an entity, and Connection not.

public class CategoryRepository
    {
        [...]

        private class MyRetrieveHandler : RetrieveRequestHandler<MyRow>
        {
            protected override void PrepareQuery(SqlQuery query)
            {
                base.PrepareQuery(query);

                // Repository mode
                CustomerRepository cr = new CustomerRepository();
                var customerResponse = cr.Retrieve(Connection, new RetrieveRequest { EntityId = 1 });
                var customerRowFromResponse = customerResponse.Entity;

                // Connection mode
                var customerRow = Connection.ById<CustomerRow>(1);
            }    
        }
}

[volkanceylan]

There is also connection.ById method