none
Navigate EF relationships using dynamic and Entity Sql RRS feed

  • Question

  • I have some interdependent objects that I have created using DataBase First method to map their relationships.

    I want to create a dynamic "flat" structure that uses the existing relations from the EF definitions, for custom reporting.

    I have tried to use the following and notice that my tests pass when navigating up the stack but not down, in the code below the Company and Employees are mapped in EF a 1..* by ID  

    i.e. why does this works...

    string query = @"SELECT e.PERSON_NAME, e.Company.COMPANY_NAME FROM AdHocEntities.Employees AS e where e.ID = @id";
    ObjectParameter[] paras = new ObjectParameter[] { new ObjectParameter("id", 1234) };


    MetadataWorkspace workspace = new MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { Assembly.GetExecutingAssembly() });

    using (SqlConnection connect = GetConnection())
    {
    using (EntityConnection entityConnect = new EntityConnection(workspace, GetConnection()))
    {
    ObjectContext context = new ObjectContext(entityConnect);
    ObjectQuery<DbDataRecord> compQuery = context.CreateQuery<DbDataRecord>(query, paras);

    // Execute the query and get the ObjectResult.
    List<DbDataRecord> results = new List<DbDataRecord>();

    results.AddRange(compQuery.Execute(MergeOption.NoTracking));
    return results;
    }
    }

    but this doesn't...

    string query = @"SELECT c.Employees.PERSON_NAME, c.COMPANY_NAME FROM AdHocEntities.Companies AS c where c.IID = @id";
    ObjectParameter[] paras = new ObjectParameter[] { new ObjectParameter("id", 1234) };

    MetadataWorkspace workspace = new MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { Assembly.GetExecutingAssembly() });

    using (SqlConnection connect = GetConnection())
    {
    using (EntityConnection entityConnect = new EntityConnection(workspace, GetConnection()))
    {
    ObjectContext context = new ObjectContext(entityConnect);
    ObjectQuery<DbDataRecord> compQuery = context.CreateQuery<DbDataRecord>(query, paras);

    // Execute the query and get the ObjectResult.
    List<DbDataRecord> results = new List<DbDataRecord>();

    results.AddRange(compQuery.Execute(MergeOption.NoTracking));
    return results;
    }
    }


    Friday, January 10, 2014 5:50 PM

Answers

  • I'm not an ESQL expert (And I don't know any).  But I guess that a Company row flows out of your FROM clause in the second query.  And a Company has multiple Employees, and the Employees collection doesn't have a PERSON_NAME.

    In the first query your FROM clause returns Employee rows, and each Employee has a single Company.COMPANY_NAME.

    Also, I would try rather hard not to use ESQL.  It's fairly obscure and lightly used.  If you can, use LINQ or store SQL instead.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Friday, January 10, 2014 6:28 PM