How to: Execute a Query that Returns RefType Results (EntityClient)

This topic provides an example of how to execute a command against an Entity Data Model by using EntityCommand, and how to retrieve the RefType results by using EntityDataReader.

To run the code in this example

  1. Add the AdventureWorks Sales Model to your project and configure your project to use the Entity Framework. To do this, do one of the following:

  2. In the code page for your application, add the following using statements (Imports in Visual Basic):.

    using System;
    using System.Collections.Generic;
    using System.Collections;
    using System.Data.Common;
    using System.Data;
    using System.IO;
    using System.Data.SqlClient;
    using System.Data.EntityClient;
    using AdventureWorksModel;
    using System.Data.Metadata.Edm;
    
    

Example

To test the code, pass the following query as an argument to the ExectueRefTypeQuery function:

"SELECT REF(p) FROM AdventureWorksEntities.Product as p"

The query above returns the RefType results.

static void ExectueRefTypeQuery(string esqlQuery)
{
    if (esqlQuery.Length == 0)
    {
        Console.WriteLine("The query string is empty.");
        return;
    }

    using (EntityConnection conn =
        new EntityConnection("name=AdventureWorksEntities"))
    {
        conn.Open();

        try
        {
            // Create an EntityCommand.
            using (EntityCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = esqlQuery;
                // Execute the command.
                using (EntityDataReader rdr =
                    cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    // Start reading results.
                    while (rdr.Read())
                    {
                        RefTypeVisitRecord(rdr as IExtendedDataRecord);
                    }
                }
            }
        }
        catch (EntityException ex)
        {
            Console.WriteLine(ex.ToString());
        }
        conn.Close();
    }
}

static void RefTypeVisitRecord(IExtendedDataRecord record)
{
    // For RefType the record contains exactly one field.
    int fieldIndex = 0;

    // If the field is flagged as DbNull, the shape of the value is undetermined.
    // An attempt to get such a value may trigger an exception.
    if (record.IsDBNull(fieldIndex) == false)
    {
        BuiltInTypeKind fieldTypeKind = record.DataRecordInfo.FieldMetadata[fieldIndex].
            FieldType.TypeUsage.EdmType.BuiltInTypeKind;
        //read only fields that contain PrimitiveType
        if (fieldTypeKind == BuiltInTypeKind.RefType)
        {
            // Ref types are surfaced as EntityKey instances. 
            // The containing record sees them as atomic.
            EntityKey key = record.GetValue(fieldIndex) as EntityKey;
            // Get the EntitySet name.
            Console.WriteLine("EntitySetName " + key.EntitySetName);
            // Get the Name and the Value information of the EntityKey.
            foreach (EntityKeyMember keyMember in key.EntityKeyValues)
            {
                Console.WriteLine("   Key Name: " + keyMember.Key);
                Console.WriteLine("   Key Value: " + keyMember.Value);
            }
        }
    }
}

See Also

Community Additions

Show: