How to: Execute an Entity SQL Query Using EntityCommand (Entity Framework)

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This topic provides an example of how to execute an Entity SQL query by using the EntityCommand. It also shows how to retrieve results through EntityDataReader. The example in this topic is based on the AdventureWorks Sales Model (EDM). To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedure in How to: Use the Entity Data Model Wizard (Entity Framework).

The Entity Data Model (EDM) defines various types. These types can be grouped into the following type families:

  • Primitive types, such as Int32 and String.
  • Nominal types that are defined in the schema, such as entity and relationship types.
  • Anonymous types, such as collection, row, and reference. For more information, see Type System.

The following example executes an Entity SQL query that returns nested collection results. The code retrieves results through DbDataReader; saves the results in XmlDocument; and, after reading all the results, writes the content of XmlDocument to console. Depending on the type of the record returned by the DbDataReader, the code retrieves information about the record as follows:

  • If a field of a record is of the DbDataRecord type, the value contains a nominal object. In this case, another DbDataRecord is nested within the current DbDataRecord. To get the nested DbDataRecord, the code calls System.Data.IDataReader.GetValue and passes the nested DbDataRecord to the recursive VisitRecord() function for further processing.
  • If a field of a record is of the DbDataReader type, the field points to a nested collection. In this case, the System.Data.IDataReader.GetData method is called to get the nested DbDataReader and pass it to the recursive VisitReader() function for further processing.
  • If a field of a record is of the System.Data.EntityKey type, the record is a reference object, and the result will contain the key property plus the entity set name.
  • If a field is none of the types listed above, it is of a primitive type and in this case we retrieve the value by calling the System.Data.EntityClient.EntityDataReader.GetValue method.
class Program
{
    static XmlDocument _XmlDoc;
    static private XmlElement _XmlCommand;

    static public void VisitReader(DbDataReader reader)
    {
        VisitReader(reader, _XmlCommand);
    }

    static private void VisitReader(DbDataReader reader, 
                                    XmlElement xmlParent)
    {
        XmlElement xmlResult = _XmlDoc.CreateElement("Result");
        xmlParent.AppendChild(xmlResult);

        while (reader.Read())
        {
            VisitRecord(reader, xmlResult);
        }
    }

    static private void VisitRecord(IDataRecord record, XmlElement xmlParent)
    {
        XmlElement xmlRecord = _XmlDoc.CreateElement("Record");
        xmlParent.AppendChild(xmlRecord);

        for (int i = 0; i < record.FieldCount; i++)
        {
            string name = record.GetName(i);
            XmlElement xmlProperty = _XmlDoc.CreateElement(XmlConvert.EncodeName(name));
            xmlRecord.AppendChild(xmlProperty);

            if (record.IsDBNull(i))
            {
                xmlProperty.InnerText = "[NULL]";
                continue;
            }
            else
            {
                Type type = record.GetFieldType(i);
                if (type.Equals(typeof(DbDataRecord)))
                {
                    DbDataRecord nestedRecord = record.GetValue(i) as DbDataRecord;
                    VisitRecord(nestedRecord, xmlProperty);
                }
                else if (type.Equals(typeof(DbDataReader)))
                {
                    DbDataReader nestedReader = record.GetData(i) as DbDataReader;
                    VisitReader(nestedReader, xmlProperty);
                }
                else if (type.Equals(typeof(EntityKey)))
                {
                    EntityKey key = record.GetValue(i) as EntityKey;
                    StringBuilder builder = new StringBuilder();

                    builder.Append(key.EntitySetName);
                    builder.Append("[");
                    bool isFirstTime = true;
                    foreach (EntityKeyMember keyMember in key.EntityKeyValues)
                    {
                        if (!isFirstTime)
                        {
                            builder.Append(";");
                        }
                        isFirstTime = false;
                        builder.AppendFormat("{0}={1}", 
                            keyMember.Key, keyMember.Value.ToString());
                    }
                    builder.Append("]");

                    xmlProperty.InnerText = builder.ToString();
                }
                else
                {
                    xmlProperty.InnerText = record.GetValue(i).ToString();
                }
            }
        }
    }
    static void Main(string[] args)
    {
        using (EntityConnection conn =
            new EntityConnection("name=AdventureWorksEntities"))
        {
            conn.Open();

            _XmlDoc = new XmlDocument();
            _XmlCommand = _XmlDoc.CreateElement("Command");
            _XmlDoc.AppendChild(_XmlCommand);

            // Create a query.
            string esqlQuery = @" Select c.ContactID, c.SalesOrderHeader
                        From AdventureWorksEntities.Contact as c";

            // Create an EntityCommand.
            using (EntityCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = esqlQuery;
                // Execute the command.
                using (EntityDataReader rdr =
                    cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    // Start reading results and 
                    // write them to _XmlDoc.
                    VisitReader(rdr);
                    // Output the content of_XmlDoc content to console.
                    _XmlDoc.Save(new StreamWriter(Console.OpenStandardOutput()));
                }
            }
            conn.Close();
        }
    }
}

Show: