Nates Stuff

LINQ to Entities Example Queries

Here are the examples I used during my LINQ to Entities presentation.  They show how to use the EntityClient, ObjectQuery, and LINQ to Entities queries.  All examples are built using a 1:1 mapping to the Adventure Works database that had been slightly modified.  The Object Services with Lazy Loading Example shows some how to debug these queries.

Entity Client Example

// Entity Client & Entity SQL
using (EntityConnection conn = new EntityConnection("Name=AdventureWorksEntities")) {
    conn.Open();
    EntityCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT VALUE c FROM AdventureWorksEntities.Customer AS c WHERE c.State = @state";
    cmd.Parameters.AddWithValue("state", "UT");

    DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
    while (rdr.Read()) {
        Console.WriteLine(string.Format("Company Name: {0}\tCompany City: {1}", rdr["CompanyName"], rdr["City"]));
    }
}

Object Services Query Example

// Object Services & Entity SQL
using (AdventureWorksEntities data = new AdventureWorksEntities()) {
    ObjectQuery<Customer> custs = data.CreateQuery<Customer>(
        "SELECT VALUE c FROM Customer AS c WHERE c.Address.City = @city", 
        new ObjectParameter("city", "Salt Lake City"));

    foreach (Customer cust in custs) {
        Console.WriteLine("Company: " + cust.CompanyName);
    }
}

Object Services Query with Eager Loading Example

// Eager Loading with Object Services
using (AdventureWorksEntities data = new AdventureWorksEntities()) {
    ObjectQuery<Customer> custs = data.CreateQuery<Customer>(
        "SELECT VALUE c FROM Customer AS c WHERE c.State = @state",
        new ObjectParameter("state", "UT")).Include("SalesOrderHeader");


    foreach (Customer cust in custs) {
        SalesOrderHeader sales = cust.SalesOrderHeader.FirstOrDefault();
        string str = (sales == null) ? "none" : sales.OrderDate.ToShortDateString();
        Console.WriteLine(
            string.Format("Company: {0}\tOrder: {1}", cust.CompanyName, str));
    }
}

Object Services Query with Lazy Loading Example

// Lazy Loading with Object Services
using (AdventureWorksEntities data = new AdventureWorksEntities()) {
    ObjectQuery<Customer> custs = data.CreateQuery<Customer>(
        "SELECT VALUE c FROM Customer AS c WHERE c.State = @state",
        new ObjectParameter("state", "UT"));

    Console.WriteLine("DEBUG: " + custs.ToTraceString());

    foreach (Customer cust in custs) {
        if (!cust.SalesOrderHeader.IsLoaded)
            cust.SalesOrderHeader.Load();

        SalesOrderHeader sales = cust.SalesOrderHeader.FirstOrDefault();
        string str = (sales == null) ? "none" : sales.OrderDate.ToShortDateString();
        Console.WriteLine(
            string.Format("Company: {0}\tOrder: {1}", cust.CompanyName, str));
    }
}

LINQ to Entities Query Example

// LINQ to ENTIES (Lazy Loading)
using (AdventureWorksEntities data = new AdventureWorksEntities()) {

    var custquery = from d in data.Customer
                where d.State == "UT"
                select d;

    foreach (Customer cust in custquery) {
        if ( !cust.SalesOrderHeader.IsLoaded )
            cust.SalesOrderHeader.Load();
        SalesOrderHeader sales = cust.SalesOrderHeader.FirstOrDefault();
        string str = (sales == null) ? "none" : sales.OrderDate.ToShortDateString();
        Console.WriteLine(
            string.Format("Company: {0}\tOrder: {1}", cust.CompanyName, str));
    }
}

The presentation slides are available here.