Using the AdventureWorks Object Model (EDM)

The code used by the AdventureWorks client application shown in the example below demonstrates several features of the Entity Data Model (EDM). The schemas described in previous AdventureWorks Model topics are the basis for entities and associations used by the code in this section.

Types designed in schemas and mapped to storage are built as a programmable object model. Data on this model is programmable using common language runtime (CLR) syntax without SQL queries embedded as strings in the code.

Application Configuration

Using the object model requires a connection to the database where application data is stored. An entity connection to the runtime objects provided by the DLL built from the schemas is also needed.

The exe.config file contains a connection string that is used to connect to a SQL Server database and establish an entity connection. With an entity connection in place, the entities and associations in the object model can be accessed from code.

The connection string text must be added to the exe.config file by the developer.

The assignment providerName="System.Data.EntityClient" specifies an entity connection that uses the schemas and mappings defined in schemas shown in the other topics in this section.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="AdventureworksContext" connectionString='Metadata=.;
            Provider=System.Data.SqlClient;
            Provider Connection String="server=.\sqlExpress;
            database=Adventureworks;
            Integrated Security=true;Connection Timeout=5;
            multipleactiveresultsets=true"'
            providerName="System.Data.EntityClient"/>
    </connectionStrings>
</configuration>

Note

This connection string sets multiple active result sets to true as required to invoke the Load method on associations when another data reader is already open on the same connection.

Using AdventureWorks Entities and Associations

The following code demonstrates queries based on the entities and associations defined in the schemas in other topics in this AdventureWorks section.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AdventureWorks;
using System.Data.Objects;
using System.Data.EntityClient;
using System.Data.Metadata.Edm;
using System.Collections.ObjectModel;


namespace AdventureworksClient
{
    class Program
    {
        static void Main(string[] args)
        {
            using (AdventureWorksContext objCtx = new AdventureWorksContext())
            {
                // find first 50 employees and display data.
                foreach (Employee employee in
                    objCtx.Employee.Where("it.EmployeeID < 50"))
                {
                    Console.WriteLine("EmployeeID: {0}",
                                              employee.EmployeeID);

                    // Find employee job candidate and print resume.
                    employee.JobCandidate.Load();
                    foreach (JobCandidate jc in employee.JobCandidate)
                        Console.WriteLine("\tJobCandidate: {0} \n\t{1}",
                            jc.JobCandidateID, jc.Resume);


                    // Find manager of employee and display.
                    if (null != employee.Employee2)
                    {
                        employee.Employee2.Employee2Reference.Load();
                        Console.WriteLine("Employee: {0} Manager: {1}",
                             employee.EmployeeID.ToString(),
                             employee.Employee2.EmployeeID.ToString());
                    }

                }

                // Employees IDs 1 - 290. 
                // Find first 50 with contact information.
                foreach (Contact contact in
                     objCtx.Contact.Where("it.ContactID < 50"))
                {
                    Console.WriteLine("Contact: {0} {1} {2}",
                        contact.ContactID, contact.FirstName,
                        contact.LastName);

                    foreach (EmployeeAddress emplAddress in
                                          objCtx.EmployeeAddress)
                    {
                        if (emplAddress.EmployeeID.Equals(contact.ContactID))
                        {
                            ObjectParameter param = new
                            ObjectParameter("p",
                                emplAddress.AddressID);
                            Address address =
                                objCtx.Address.Where(
                               "it.AddressID = @p",
                                param).First();

                            Console.WriteLine(
                              "\tEmployee address: {0}\n\t{1}\n\t{2}",
                              address.AddressLine1,
                              address.City, address.PostalCode);
                        }
                    }
                }

                // Find and display vendors.
                foreach (Vendor vendor in objCtx.Vendor)
                    Console.WriteLine("Vendor: {0}", vendor.Name);

                // Find and display store information.
                foreach (Store store in objCtx.Store)
                    Console.WriteLine("Store: {0}", store.Name);


                // Find and display product information.
                foreach (Product product in objCtx.Product)
                {
                    Console.WriteLine("Product Name: {0}", product.Name);
                    product.ProductModelReference.Load();
                    if (null != product.ProductModel)
                    {
                        Console.WriteLine("Model: {0}",
                            product.ProductModel.ProductModelID.ToString());
                    }
                }
            }

            try
            {
                // Establish a connection to the underlying data provider by 
                // using the connection string specified in the config file.
                using (EntityConnection connection =
                    new EntityConnection("Name=AdventureWorksContext"))
                {
                    // Open the connection.
                    connection.Open();

                    // Access the metadata workspace.
                    MetadataWorkspace workspace =
                       connection.GetMetadataWorkspace();

                    // To run DisplayProperties(workspace, DataSpace.CSpace);
                    // to display the extended properties in the conceptual model.
                    // see Extended Property (CSDL) (link under See Also).
                }
            }
            catch (System.Data.MetadataException exceptionMetadata)
            {
                Console.WriteLine("MetadataException: {0}",
                                exceptionMetadata.Message);
            }
            catch (System.Data.MappingException exceptionMapping)
            {
                Console.WriteLine("MappingException: {0}",
                                 exceptionMapping.Message);
            }

        }

        
    }
}

See Also

Concepts

Extended Property (CSDL)

Other Resources

EDM Specifications
Schemas and Mapping Specification (Entity Framework)
Sample Applications (Entity Framework)