join on three tables

[piotre6]

I'm trying to use openaccess to bind grid. Unfortunately after executing following code columns
Agreement and Tenant contains the same string. What is wrong with that code?
sql query attached in code block works fine

            System.Data.DataTable data = new System.Data.DataTable();
            data.Columns.Add("AgreementID", typeof(int));
            data.Columns.Add("ParentAgreementID", typeof(int));
            data.Columns.Add("Agreement", typeof(string));
            data.Columns.Add("Company", typeof(string));
            data.Columns.Add("Tenant", typeof(string));
            data.Columns.Add("SigningDate", typeof(DateTime));
            data.Columns.Add("StartDate", typeof(DateTime));
            data.Columns.Add("EndDate", typeof(DateTime));
            data.Columns.Add("RetailArea", typeof(decimal));

using (EntitiesModel dbContext = new EntitiesModel())
            {
//                    SQL QUERY
//                    SELECT
//                      AgreementID,
//                        ParentAgreementID,
//                      Agreements.Name Agreement,
//                      Companies.Name as Company,
//                      Tenants.Name as Tenant,
//                      SigningDate,
//                      StartDate,EndDate,
//                      RetailArea
//                    FROM Agreements, Companies, Tenants
//                    where Agreements.CompanyID = Companies.CompanyID
//                    AND Tenants.TenantID = Agreements.TenantID



                var agreements = from a in dbContext.Agreements
                                 join t in dbContext.Tenants on a.TenantID equals t.TenantID
                                 join c in dbContext.Companies on a.CompanyID equals c.CompanyID
                                select new
                                {
                                    AgreementID = a.AgreementID,
                                    ParentAgreementID = a.ParentAgreementID,
                                    Name = a.Name,
                                    Company = c.Company,
                                    Tenant = t.Name,
                                    a.TenantID,
                                    a.SigningDate,
                                    a.StartDate,
                                    a.EndDate,
                                    a.RetailArea
                                };
                foreach (var a in agreements)
                {
                    data.Rows.Add(
                        a.AgreementID,
                        a.ParentAgreementID,
                        a.Name,
                        a.Company,
                        a.Tenant,
                        a.SigningDate,
                        a.StartDate,
                        a.EndDate,
                        a.RetailArea
                    );
                }
                AgreementsGrid.DataSource = data;
            }

[Zoran]

Hello Piotr,

 I believe that the Agreement entity has a reference to the Tenant  and Company entities in your model. If it has not, I suggest you to create ones using the Association Editor.

In that case, you do not need to perform Joins in your Linq statement. Consider writing the statement like this:

var agreements = from a in dbContext.Agreements
            select new
            {
                 AgreementID = a.AgreementID,
                 ParentAgreementID = a.ParentAgreementID,
                 Name = a.Name,
                 Company = a.Company.Company,
                 Tenant = a.Tenant..Name,
                  a.TenantID,
                  a.SigningDate,
                  a.StartDate,
                  a.EndDate,
                  a.RetailArea
             };

All the best,
Zoran
the Telerik team

[piotre6]

Thanks for your answer.
I came to that solution sometime after sending my first post, however I didn't have opportunity to write it.
Anyway I didn't realise I will be so easy :)