join on three tables

3 posts, 0 answers
  1. piotre6
    piotre6 avatar
    18 posts
    Member since:
    Apr 2011

    Posted 30 Jan 2012 Link to this post

    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;
                }
  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 02 Feb 2012 Link to this post

    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

    SP1 for Q3’11 of Telerik OpenAccess ORM is available for download

  3. DevCraft banner
  4. piotre6
    piotre6 avatar
    18 posts
    Member since:
    Apr 2011

    Posted 02 Feb 2012 Link to this post

    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 :)
Back to Top