none
Get results from entity sql with nested query RRS feed

  • Question

  • Hi,

    I have three tables AA, BB, CC, these tables can join together through keys.

                string sql = "select a, (select b, c.CC1, c.CC2" + "\r\n" +
                                        "from MyEntities.BB as b " + "\r\n" +
                                        "join MyEntities.CC as c on c.BBId = b.BBId" + "\r\n" +
                                        "where b.AAId = a.AAId) as BCs" + "\r\n" +
                            "from MyEntities.AA as a" + "\r\n" +
                            "where a.AAId > 0" + "\r\n" +
                            "and a.Surname like 'ABB%'";
                ObjectQuery<DbDataRecord> ABCs = context.CreateQuery<DbDataRecord>(sql);
                foreach (DbDataRecord abc in ABCs)
                {
                    foreach (DbDataRecord bc in abc[1] as DbDataReader)
                    {
                        if (bc["CC1"] == "N")
                            sql = "asdfasd";
                    }
                }

    Executing the query fine. But when working on foreach (DbDataRecord bc in abc[1] as DbDataReader), it shows an error that abc[1] is null.
    The table joins in the query are OK, I checked the database that the records for this particular query are not null. In the debug, I can see the values as well. so how can I get these values (b, CC1, CC2) out? For some reason, I don't want to change the query structure.

    Monday, January 23, 2012 4:27 AM

Answers

  • Hi,

    I think I found the problem here.  We cannot convert the abc[1] to DbDataReader.  Instead, EF has combined it into a collection of DbDataRecord, so you may use such a line of codes:

         foreach (DbDataRecord bc in (IEnumerable<DbDataRecord>)abc[1]) ....

    Please let me know whether it solves the problem for you. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, January 24, 2012 7:48 AM
    Moderator
  • Hi Michael,

    Thank you for the help again.

    I have figured out the solution.

                foreach (var abc in ABCs)
                {
                    AAs a = abc[0] as AAs;
                    sql = a.Surname.ToString();
                    List<DbDataRecord> dr = (List<DbDataRecord>)abc[1];
                    foreach (DbDataRecord bc in dr)
                    {
                        BBs b = bc["b"] as BBs;
                        sql = b.bName;
                        string epiF = bc["EpiF"].ToString();
                        string uniqF = bc["UniqF"].ToString(); 
                    }

                }

    Tuesday, January 24, 2012 10:16 PM

All replies

  • Hi,

    Have you tried use some profiler mentioned here, http://msdn.microsoft.com/en-us/magazine/gg490349.aspx, to check the SQL command generated at the database side?   What is the query result from the SQL command?  

    I am not quite familar with the data strucure you provided.   But maybe the not every "a" object whose AAId > 0 and Surname like 'ABB%' has the corresponding "b" object together with "c" object? 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 23, 2012 6:04 AM
    Moderator
  • Hi Michael,

     

    Thank you for your help.

    There are records in the database that the AAId > 0 and surname start with ABB. and there are corresponding BB, CC records as well.

    I used sql server profile and got the sql and run the sql on the sql server managerment studio. The result is copied below.

    AA.AAId,  AA.Surname,   AA.FirstName,  C1,  BB.AAId,  BB.BBID,  CC.BBId,  CC.CC1,   CC.CC2
    1             ABBOTT           Paul                1      1               1             1               N          N   
    1             ABBOTT           Paul                1      1               2             2               N          N
    2             ABBOTT           Graham           1      2               3             3               Y          N
    2             ABBOTT           Graham           1      2               4             4               N          Y

    Monday, January 23, 2012 11:29 PM
  • Hi,

    I think I found the problem here.  We cannot convert the abc[1] to DbDataReader.  Instead, EF has combined it into a collection of DbDataRecord, so you may use such a line of codes:

         foreach (DbDataRecord bc in (IEnumerable<DbDataRecord>)abc[1]) ....

    Please let me know whether it solves the problem for you. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, January 24, 2012 7:48 AM
    Moderator
  • Hi Michael,

    Thank you for the help again.

    I have figured out the solution.

                foreach (var abc in ABCs)
                {
                    AAs a = abc[0] as AAs;
                    sql = a.Surname.ToString();
                    List<DbDataRecord> dr = (List<DbDataRecord>)abc[1];
                    foreach (DbDataRecord bc in dr)
                    {
                        BBs b = bc["b"] as BBs;
                        sql = b.bName;
                        string epiF = bc["EpiF"].ToString();
                        string uniqF = bc["UniqF"].ToString(); 
                    }

                }

    Tuesday, January 24, 2012 10:16 PM
  • Hi,

    I am glad to hear that it works for you!

    Have a nice day! :)

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 25, 2012 1:45 AM
    Moderator