Solution

Let’s say that you have a model like the one shown in Figure 3-1.

In this simple model, we have entities that represent associates and their salary history.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-1, we create the entity classes.

Listing 3-1.  Associate and AssociateSalary Entity Types

public class Associate
{
    public Associate()
    {
        AssociateSalaries = new HashSet<AssociateSalary>();
    }
  
    public int AssociateId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<AssociateSalary> AssociateSalaries { get; set; }
}
 
public class AssociateSalary
{
    public int SalaryId { get; set; }
    public int AssociateId { get; set; }
    public decimal Salary { get; set; }
    public DateTime SalaryDate { get; set; }
    public virtual Associate Associate { get; set; }
}

Next, in Listing 3-2, we create the DbContext object required for our Code-First approach. Note in the OnModelCreating method how we explicitly map the SalaryId property as the primary key for the AssociateSalary. When using Code First, if a property has the name Id or <table name>Id, Entity Framework assumes that it is the primary key for the table. Otherwise, you must explicitly specify the key, as we have done here.

Listing 3-2.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString")
    {
    }
  
    public DbSet<Associate> Associates { get; set; }
    public DbSet<AssociateSalary> AssociateSalaries { get; set; }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Associate>().ToTable("Chapter3.Associate");
        modelBuilder.Entity<AssociateSalary>().ToTable("Chapter3.AssociateSalary");
  

        // Explicilty assign key as primary key in AssociateSalary does not meet
        // Entity Framework default mapping conventions.
        modelBuilder.Entity<AssociateSalary>().HasKey(x => x.SalaryId);
        base.OnModelCreating(modelBuilder);
    }
}

Listing 3-3 demonstrates how we can leverage the new Entity Framework Async methods to implement asynchronous processing for the queries that remove, load, and fetch data.

Listing 3-3.  Asynchronously Processing Entity Framework Queries

private static void Main()
{
    var asyncTask = EF6AsyncDemo();
  
    foreach (var c in BusyChars())
    {
        if (asyncTask.IsCompleted)
        {
            break;
        }
        Console.Write(c);
        Console.CursorLeft = 0;
        Thread.Sleep(100);
    }
    Console.WriteLine("
Press <enter> to continue...");
    Console.ReadLine();
}
  
private static IEnumerable<char> BusyChars()
{
    while (true)
    {
        yield return '';
        yield return '|';
        yield return '/';
        yield return '-';
    }
}
  
private static async Task EF6AsyncDemo()
{
    await Cleanup();
    await LoadData();
    await RunForEachAsyncExample();
    await RunToListAsyncExampe();
    await RunSingleOrDefaultAsyncExampe();
}
  
private static async Task Cleanup()
{
    using (var context = new EFRecipesEntities())
    {
        // delete previous test data
        // execute raw sql statement asynchronoulsy
        Console.WriteLine("Cleaning Up Previous Test Data");
        Console.WriteLine("=========
");
  

        await context.Database.ExecuteSqlCommandAsync("delete from chapter3.AssociateSalary");
        await context.Database.ExecuteSqlCommandAsync("delete from chapter3.Associate");
        await Task.Delay(5000);
    }
}
  
private static async Task LoadData()
{
    using (var context = new EFRecipesEntities())
    {
        // add new test data
        Console.WriteLine("Adding Test Data");
        Console.WriteLine("=========
");
  
        var assoc1 = new Associate { Name = "Janis Roberts" };
        var assoc2 = new Associate { Name = "Kevin Hodges" };
        var assoc3 = new Associate { Name = "Bill Jordan" };
        var salary1 = new AssociateSalary
        {
            Salary = 39500M,
            SalaryDate = DateTime.Parse("8/4/09")
        };
        var salary2 = new AssociateSalary
        {
            Salary = 41900M,
            SalaryDate = DateTime.Parse("2/5/10")
        };
        var salary3 = new AssociateSalary
        {
            Salary = 33500M,
            SalaryDate = DateTime.Parse("10/08/09")
        };
        assoc1.AssociateSalaries.Add(salary1);
        assoc2.AssociateSalaries.Add(salary2);
        assoc3.AssociateSalaries.Add(salary3);
        context.Associates.Add(assoc1);
        context.Associates.Add(assoc2);
        context.Associates.Add(assoc3);
  
        // update datastore asynchronoulsy
        await context.SaveChangesAsync();
        await Task.Delay(5000);
    }
}
  
private static async Task RunForEachAsyncExample()
{
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("Async ForEach Call");
        Console.WriteLine("=========");
  
        // leverage ForEachAsync
        await context.Associates.Include(x => x.AssociateSalaries).ForEachAsync(x =>
        {
            Console.WriteLine("Here are the salaries for Associate {0}:", x.Name);
  
            foreach (var salary in x.AssociateSalaries)
            {
                Console.WriteLine("	{0}", salary.Salary);
            }
        });
        await Task.Delay(5000);
    }
}
  
private static async Task RunToListAsyncExampe()
{
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("

Async ToList Call");
        Console.WriteLine("=========");
  
        // leverage ToListAsync
        var associates = await context.Associates.Include(x => x.AssociateSalaries).OrderBy(x => x.Name).ToListAsync();
  
        foreach (var associate in associates)
        {
            Console.WriteLine("Here are the salaries for Associate {0}:", associate.Name);
            foreach (var salaryInfo in associate.AssociateSalaries)
            {
                Console.WriteLine("	{0}", salaryInfo.Salary);
            }
        }
        await Task.Delay(5000);
    }
}
  
private static async Task RunSingleOrDefaultAsyncExampe()
{
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("

Async SingleOrDefault Call");
        Console.WriteLine("=========");
  
        var associate = await context.Associates.
            Include(x => x.AssociateSalaries).
                OrderBy(x => x.Name).
                    FirstOrDefaultAsync(y => y.Name == "Kevin Hodges");
  
        Console.WriteLine("Here are the salaries for Associate {0}:", associate.Name);
        foreach (var salaryInfo in associate.AssociateSalaries)
        {
            Console.WriteLine("	{0}", salaryInfo.Salary);
        }
        await Task.Delay(5000);
    }
}

Listing 3-3 outputs the following result:

Cleaning Up Previous Test Data
=========
Adding Test Data
=========
Async ForEach Call
=========
Here are the salaries for Associate Janis Roberts:
        39500.00
Here are the salaries for Associate Kevin Hodges:
        41900.00
Here are the salaries for Associate Bill Jordan:
        33500.00
Async ToList Call
=========
Here are the salaries for Associate Bill Jordan:
        33500.00
Here are the salaries for Associate Janis Roberts:
        39500.00
Here are the salaries for Associate Kevin Hodges:
        41900.00
Async SingleOrDefault Call
=========
Here are the salaries for Associate Kevin Hodges:
        41900.00

How It Works

In this example, we demonstrate two key concepts of Entity Framework usage: Querying the model using the LINQ extensions for Entity Framework and the new asynchronous capabilities implemented in Entity Framework 6.

For the vast majority of your query operations, you want to use LINQ. Doing so will give you IntelliSense, compile-time checking, and a great strongly typed experience. If you have a use case that requires the construction of a dynamic query at runtime, you may consider using Entity SQL, which enables you to concatenate strings for various parts of the query expression. You will find Entity SQL examples contained in the recipes in this chapter.

We start by clearing out any previous test data in the underlying data store. Notice how we wrap the Cleanup() operation inside an async method. We then generate native SQL statements using the new ExecuteSqlCommandAsync() method. Note how we leverage the async/await patterns found in the 4.5 version of the .NET framework.  This pattern enables asynchronous operations without explicitly instantiating a background thread; additionally, it frees up the current CLR thread while it is waiting for the database operation to complete.

Next we load test data for both Associate and Associate Salaries. To execute the call asynchronously, as before, we wrap the LoadData() operation inside an async method and insert new test data into the underlying data store by calling the newly added SaveChangesAsync() method.

Next, we present three different queries that go against the model. Each leverages the LINQ extensions for Entity Framework. Each is contained within an async method, leveraging the await/async pattern. In the RunForEachAsyncExample() method, we make use of the ForEachAsync() extension method, as there is no async equivalent of a foreach statement. Leveraging this async method, along with the Include() method, we are able to query and enumerate these objects asynchronously.

In the subsequent RunToListAsyncExample() and RunSingelOrDefaultAsyncExample() queries, we leverage the new asynchronous methods for ToList() and SingleOrDefault().

Entity Framework now asynchronously exposes a large number of its operational methods. The naming convention appends the suffix Async to the existing API name, making it relatively simple to implement asynchronous processing when adding or fetching data from the underlying data store.

Problem

You want to execute a native SQL statement against the Entity Framework to update the underlying data store.

Solution

Let’s say that you have a Payment database table like the one shown in Figure 3-2, and you have created a model such as the one in Figure 3-3, which is from the Entity Framework designer tool.

To execute one or more SQL statements directly against the underlying Payment table, use the ExecuteSqlCommand() method available from the Database property from DbContext.class. Although we could query the Payment entity in our model, the ExecuteSqlCommand enables us to query the underlying database table directly, forgoing some Entity Framework features such as change tracking. We simply need a model object that inherently contains a context object against which we execute ad hoc SQL commands.

Follow the pattern in Listing 3-4 to execute one or more SQL statements.

Listing 3-4.  Executing an Insert Statement

// delete previous test data
using (var context = new EFRecipesEntities())
{
    context.Database.ExecuteSqlCommand("delete from chapter3.payment");
}
// insert two rows of data
using (var context = new EFRecipesEntities())
{
    // note how using the following syntax with parameter place holders of @p0 and @p1
    // automatically create the ADO.NET SqlParameters object for you
    var sql = @"insert into Chapter3.Payment(Amount, Vendor) values (@p0, @p1)";
    var rowCount = context.Database.ExecuteSqlCommand(sql, 99.97M, "Ace Plumbing");
    rowCount += context.Database.ExecuteSqlCommand(sql, 43.83M, "Joe's Trash Service");
    Console.WriteLine("{0} rows inserted", rowCount);
}
 
// retrieve and materialize data using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Payments");
    Console.WriteLine("========");
    foreach (var payment in context.Payments)
    {
        Console.WriteLine("Paid {0} to {1}", payment.Amount.ToString(),
                            payment.Vendor);
    }
}

Following is the output of the code in Listing 3-4:

2 rows inserted
Payments
========
Paid $99.97 to Ace Plumbing
Paid $43.83 to Joe's Trash Service

How It Works

In Listing 3-4, we start by removing any previous test data. Notice how we use the ExecuteSqlCommand method from the Database object from the DbContext object to execute this operation. Note how we feed a native SQL Delete statement directly into the method.

Then we create a string containing a SQL Insert statement. This statement contains two parameters: @Amount and @Vendor. These are placeholders that will be replaced by values when the statement is executed.

Next we create two DbParameter parameter objects, which bind the placeholder names to specific values. For the first insert, we bind the value 99.97 to the Amount placeholder along with “Ace Plumbing” to the Vendor placeholder. We then create another vendor record. Notice how both vendors are assigned to an array of type DbParameter. To execute the SQL statement, we pass both the string containing the SQL statement and the array of DbParameter objects to the ExecuteSqlCommand() method. ExecuteSqlCommand() returns the count of rows affected by the statement. In our case, one row is inserted each time we call ExecuteSqlCommand().

If you don’t have any parameters for a SQL statement, there is an overload of the ExecuteSqlCommand() method with a single parameters that expects only a SQL statement.

• The pattern in Listing 3-4 is similar to how we would query data leveraging the Microsoft ADO.NET framework with the SqlClient object. The difference is that we don’t need to construct a connection string and explicitly open a connection. The underlying Entity Framework context object automatically performs this work. Note that there are two versions of the context object in Entity Framework: The DbContext object in Entity Framework versions 5, 6, and the 4.x Code-First approach.
• The ObjectContext in earlier versions of Entity Framework.

Keep in mind as well that the DbContext is simply a wrapper, or “Façade,” which wraps the legacy ObjectContext, making the context object significantly more intuitive and easy to use. All functionality from the underlying ObjectContext is still available.

The way we express the command text and the parameters is also different. With the ADO.NET ExecuteNonQuery() method, the command text and parameters are set on the underlying Command object. Here, these are passed into the ExecuteSqlCommand() method as simple arguments.

Of course, the observant reader will notice here (and this is important) that we’re really not querying the model. In fact, as we mentioned, you don’t need to have the Payment entity shown in Figure 3-3. The ExecuteSqlCommand() method simply uses the object’s DbContext for its connection to the underlying data store.

Best Practice

To parameterize or not to parameterize, that is the question . . . Okay, Shakespeare aside, should you use parameters for SQL statements or just create the SQL statement strings dynamically? The best practice is to use parameters whenever possible. Here are some reasons why:

• Parameterized SQL statements help prevent SQL Injection attacks. If you construct a complete SQL statement as a string by dynamically appending together strings that you get from a user interface, such as an ASP.NET TextBox control, you may end up inadvertently exposing yourself to injected SQL statements that can significantly damage your database and reveal sensitive information. When you use parameterized SQL statements, the parameters are handled in a way that prevents this.
• Parameterized SQL statements, as we have shown in this recipe, allow you to reuse parts of the statement. This reuse can make your code more simple and easier to read.
• Following the re-use idea, most enterprise-class databases like Oracle Database, IBM DB2, and even Microsoft SQL Server in some circumstances, can take advantage of parameterized queries by reusing the parsed version of the query even if the parameters have changed. This boosts performance and lowers the processing overhead for SQL statement re-use.
• Parameterized SQL statements make your code more maintainable and configurable. For example, the statements could come from a configuration file. This would allow you to make some changes to the application without changing the code.

Problem

You want to execute a native SQL statement and fetch objects from your database.

Solution

Let’s say that you have a model with a Student entity type, as shown in Figure 3-4.

You want to execute a native SQL statement that returns a collection of instances of the Student entity type. As you saw in the previous recipe, the ExecuteSqlCommand() method is similar to ADO.NET SQLCommand’s ExecuteNonQuery() method. It executes a given SQL statement and returns the number of rows affected. To have Entity Framework materialize this untyped data into strongly-typed entity objects, we can use the SqlQuery() method.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-5, we create the Student entity class.

Listing 3-5.  Student Entity Class

public class Student
{
    public int StudentId { get; set; }
    public string Degree { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Next, in Listing 3-6, we create the DbContext object required for our Code-First approach.

Listing 3-6.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString") {}
  
    public DbSet<Student> Students { get; set; }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>().ToTable("Chapter3.Student");
    }
}

To execute a SQL statement and get back a collection of instances of the Student entity type, follow the pattern in Listing 3-7.

Listing 3-7.  Using ExecuteStoreQuery() to Execute a SQL statement and Get Back Objects

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.student");
 

    // insert student data
    context.Students.Add (new Student
    {
        FirstName = "Robert",
 
        LastName = "Smith",
        Degree = "Masters"
    });
 
    context.Students.Add (new Student
    {
        FirstName = "Julia",
        LastName = "Kerns", Degree = "Masters"
    });
 
    context.Students.Add (new Student
    {
        FirstName = "Nancy",
 

        LastName = "Stiles", Degree = "Doctorate"
    });
 

    context.SaveChanges();}
 
using (var context = new EFRecipesEntities())
{
    string sql = "select * from Chapter3.Student where Degree = @Major";
    var parameters = new DbParameter[] {
        new SqlParameter {ParameterName = "Major", Value = "Masters"}};
    var students = context.Students.SqlQuery(sql, parameters);
    Console.WriteLine("Students...");
    foreach (var student in students)
    {
        Console.WriteLine("{0} {1} is working on a {2} degree",
                    student.FirstName, student.LastName, student.Degree);
    }
}

Following is the output of the code in Listing 3-7:

Students...
Robert Smith is working on a Masters degree
Julia Kerns is working on a Masters degree

How It Works

In Listing 3-7, we add three Students to the DbContext and save them to the database using SaveChanges().

To retrieve the Students who are working on a master’s degree, we use the SqlQuery() method with a parameterized SQL statement and a parameter set to “Masters.” We iterate through the returned collection of Students and print each of them. Note that the associated context object implements change tracking for these values.

Here we use * in place of explicitly naming each column in the select statement. This works because the columns in the underlying table match the properties in the Student entity type. Entity Framework will match the returned values to the appropriate properties. This works fine in most cases, but if fewer columns returned from your query, Entity Framework will throw an exception during the materialization of the object. A much better approach and best practice is to enumerate the columns explicitly (that is, specify each column name) in your SQL statement.

If your SQL statement returns more columns than required to materialize the entity (that is, more column values than properties in the underlying entity object), Entity Framework will happily ignore the additional columns. If you think about this for a moment, you’ll realize that this isn’t a desirable behavior. Again, consider explicitly enumerating the expected columns in your SQL statement to ensure they match your entity type.

There are some restrictions around the SqlQuery() method. If you are using Table per Hierarchy inheritance and your SQL statement returns rows that could map to different derived types, Entity Framework will not be able to use the discriminator column to map the rows to the correct derived types. You will likely get a runtime exception because some rows don’t contain the values required for the type being materialized.

Interestingly, you can use SqlQuery() to materialize objects that are not entities at all. For example, we could create a StudentName class that contains just first and last names of a student. If our SQL statement returned just these two strings, we could use SqlQuery<StudentName>() along with our SQL statement to fetch a collection of instances of StudentName.

We’ve been careful to use the phrase SQL statement rather than select statement because the SqlQuery() method works with any SQL statement that returns a row set. This includes, of course, Select statements, but it can also include statements that execute stored procedures.

Problem

You want to execute an Entity SQL statement that queries your underlying entity data model and returns strongly-typed objects.

Solution

Let’s say that you have a model like the one shown in Figure 3-5, which contains a single Customer entity type. The Customer entity type has a Name and an Email property. You want to query this model using Entity SQL.

To query the model using Entity SQL (eSQL), a dialect of SQL implemented by Entity Framework, follow the pattern in Listing 3-8. Keep in mind that when querying the underlying data store, you should favor LINQ-to-Entity queries over eSQL, due to feature-rich and strong-typing experience that LINQ provides. Entity SQL gives you the flexibility to construct database queries dynamically against the entity data model.

Listing 3-8.  Executing an Entity SQL Statement Using Both Object Services and EntityClient

using (var context = new EFRecipesEntities())
{
// delete previous test data
context.Database.ExecuteSqlCommand("delete from chapter3.customer");                // add new  test data
var cus1 = new Customer { Name = "Robert Stevens",
                              Email = "[email protected]" };
    var cus2 = new Customer { Name = "Julia Kerns",
                              Email = "[email protected]" };
    var cus3 = new Customer { Name = "Nancy Whitrock",
                              Email = "[email protected]" };
    context.Customers.Add(cus1);
    context.Customers.Add(cus2);
    context.Customers.Add(cus3);
    context.SaveChanges();
}
 
// using object services from ObjectContext object
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Querying Customers with eSQL Leveraging Object Services...");
    String esql = "select value c from Customers as c";    // cast the DbContext to the underlying ObjectContext, as DbContext does not
    // provide direct support for EntitySQL queries
 
    var customers = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Customer>(esql);
     Foreach (var customer in customers)
    {
        Console.WriteLine ("{0}'s email is: {1}",
                           customer.Name, customer.Email);
    }
}
 
Console.WriteLine();
 
// using EntityClient
using (var conn = new EntityConnection("name=EFRecipesEntities"))
{
    Console.WriteLine("Querying Customers with eSQL Leveraging Entity Client...");
    var cmd = conn.CreateCommand();
    conn.Open();
    cmd.CommandText = "select value c from EFRecipesEntities.Customers as c";
    using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
    {
        while (reader.Read())
        {
            Console.WriteLine("{0}'s email is: {1}",
                               reader.GetString(1), reader.GetString(2));
        }
    }
}

Following is the output from the code in Listing 3-8:

Querying Customers with eSQL Leveraging Object Services...
Robert Stevens's email is: [email protected]
Julia Kerns's email is: [email protected]
Nancy Whitrock's email is: [email protected]
Customers Customers with eSQL Leveraging Entity Client...
Robert Stevens's email is: [email protected]
Julia Kerns's email is: [email protected]
Nancy Whitrock's email is: [email protected]

How It Works

In Listing 3-8, we start by removing previous test data from the database. Then we create three customers, add them to the context object, and call SaveChanges() to insert them into the database.

With customers in the database, we demonstrate two different approaches to retrieving them using Entity SQL. In the first approach, we use the CreateQuery() method exposed by the legacy object context to create an ObjectQuery object. Note how we cast the DbContext to an ObjectContextAdapter type to get to its underlying ObjectContext type (keep in mind the newer DbContext wraps the older ObjectContext to improve the developer experience). We do so as the DbContext does not provide direct support for eSQL queries. Note as well how we assign the Customer class type to the generic placeholder value for CreateQuery() and pass in the eSQL query as a parameter. As we iterate over the customers collection, the query is executed against the database and the resulting collection is printed to the console. Because each element in the collection is an instance of our Customer entity type, we can use the properties of the Customer entity type to gain strongly typed usage.

In the second approach, we use the EntityClient libraries in a pattern that is very similar to how we would use SqlClient or any of the other client providers in ADO.NET. We start by creating a connection to the database. With the connection in hand, we create a command object and open the connection. Next we initialize the command object with the text of the Entity SQL statement we want to execute. We execute the command using ExecuteReader() and obtain an EntityDataReader, which is a type of the familiar DbDataReader. We iterate over the resulting collection using the Read () method.

Note that the Entity SQL statement in Listing 3-8 uses the value keyword. This keyword is useful when we need to fetch the entire entity. If our Entity SQL statement projected a specific subset of columns (that is, we use some of the columns and/or create columns using Entity SQL expressions), we can dispense with the value keyword. When working with a context object, this means working with a DbDataRecord directly as demonstrated in Listing 3-9.

Listing 3-9.  Projecting with Both Object Services and EntityClient

// using object services without the VALUE keyword
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Customers...");
    string esql = "select c.Name, c.Email from Customers as c";
    var records = context.CreateQuery<DbDataRecord>(esql);
    foreach (var record in records)
    {
        var name = record[0] as string;
        var email = record[1] as string;
        Console.WriteLine("{0}'s email is: {1}", name, email);
    }
}
 
Console.WriteLine();
 
// using EntityClient without the VALUE keyword
using (var conn = new EntityConnection("name=EFRecipesEntities"))
{
    Console.WriteLine("Customers...");
    var cmd = conn.CreateCommand();
    conn.Open();
    cmd.CommandText = @"select c.Name, C.Email from
                         EFRecipesEntities.Customers as c";
    using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
    {
        while (reader.Read())
        {
            Console.WriteLine("{0}'s email is: {1}",
                        reader.GetString(0), reader.GetString(1));
        }
    }
}

When you form a projection in Entity SQL, the results are returned in a DbDataRecord object that contains one element for each column in the projection. With the value keyword, the single object resulting from the query is returned in the first element of the DbDataRecord.

Problem

You have two entities in a one-to-many association (aka Master-Detail), and you want to find all the master entities that have at least one associated detail entity.

Solution

Imagine that you have a model for blog posts and the comments associated with each post. Some posts have lots of comments. Some posts have few or no comments. The model might look something like the one shown in Figure 3-6.

You want to find all of the blog posts that have at least one comment. To do this using either LINQ to Entities or Entity SQL, follow the pattern in Listing 3-10.

Listing 3-10.  Finding the Masters That Have Detail Using Both LINQ and Entity SQL

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.blogpost");
    context.Database.ExecuteSqlCommand("delete from chapter3.comment");
 

    // add new test data
    var post1 = new BlogPost { Title = "The Joy of LINQ",
           Description = "101 things you always wanted to know about LINQ" };
    var post2 = new BlogPost { Title = "LINQ as Dinner Conversation",
           Description = "What wine goes with a Lambda expression?" };
    var post3 = new BlogPost {Title = "LINQ and our Children",
           Description = "Why we need to teach LINQ in High School"};
    var comment1 = new Comment {
         Comments = "Great post, I wish more people would talk about LINQ" };
    var comment2 = new Comment {
         Comments = "You're right, we should teach LINQ in high school!" };
    post1.Comments.Add(comment1);
    post3.Comments.Add(comment2);
    context.BlogPosts.Add(post1);
    context.BlogPosts.Add(post2);
    context.BlogPosts.Add(post3);
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Blog Posts with comments...(LINQ)");
    var posts = from post in context.BlogPosts
                where post.Comments.Any()
                select post;
    foreach (var post in posts)
    {
        Console.WriteLine("Blog Post: {0}", post.Title);
        foreach (var comment in post.Comments)
        {
            Console.WriteLine("	{0}", comment.Comments);
        }
    }
}
 
Console.WriteLine();
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Blog Posts with comments...(ESQL)");
    var esql = "select value p from BlogPosts as p where exists(p.Comments)";
    var posts = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<BlogPost>(esql);
    foreach (var post in posts)
    {
        Console.WriteLine("Blog Post: {0}", post.Title);
        foreach (var comment in post.Comments)
        {
            Console.WriteLine("	{0}", comment.Comments);
        }
    }
}

Following is the output of the code in Listing 3-10:

Blog Posts with comments...(LINQ)
Blog Post: The Joy of LINQ
        Great post, I wish more people would talk about LINQ
Blog Post: LINQ and our Children
        You're right, we should teach LINQ in high school!
Blog Posts with comments...(ESQL)
Blog Post: The Joy of LINQ
        Great post, I wish more people would talk about LINQ
Blog Post: LINQ and our Children
        You're right, we should teach LINQ in high school!

How It Works

We start off the code in Listing 3-10 by deleting prior test data and inserting new blog posts and comments into the database. We left one of the blog posts without any comments to make sure our query performs correctly.

In the LINQ query, we leverage the LINQ Extension Method Any() in the where clause to determine whether there are comments for a given post. The query finds all of the posts for which the Any() method returns true. In this usage, we iterate through each blog post with Any() returning true if there are comments for the specific post. Moreover, that’s just what we want: all of the posts for which there is at least one comment.

For the Entity SQL approach, we use the SQL exists() operator, again in a where clause, to determine whether the given post has at least one comment.

Of course there are other ways to get the same result. For example, we could use the Count() method in the LINQ query’s where clause and test to see if the count is greater than 0. For the Entity SQL approach, we could use count(select value 1 from p.Comments) > 0 in the where clause. Either one of these approaches would work. However, the code in Listing 3-10 seems a bit cleaner and, from a performance perspective, the semantics behind Any() and exists() don’t require the enumeration of the entire collection on the server (meaning that, after finding the first comment for a blog entry, the process moves onto to the next blog entry), whereas count() does require a full enumeration on the server (meaning that, each comment will be enumerated, despite the fact that one was already found).

Problem

You have a use case for which you must assign a default value to a property when the query returns a null value. In our recipe, we’ll assign a value of ‘0’ to the Years Worked property when a null value for it is returned from the database.

Solution

Let’s say that you have a model like the one shown in Figure 3-7. You want to query the model for employees. In the database, the table representing employees contains a nullable YearsWorked column. This is the column mapped to the YearsWorked property in the Employee entity. You want the rows that contain a null value for the YearsWorked to default to the value 0.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-11, we create the Student entity class.

Listing 3-11.  Employee Entity Class

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public int? YearsWorked { get; set; }
}

Next, in Listing 3-12, we create the DbContext object required for our Code-First approach.

Listing 3-12.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString") {}
 
    public DbSet<Employee> Employees { get; set; }
 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().ToTable("Chapter3.Employee");
        base.OnModelCreating(modelBuilder);
    }
}

Since we are implementing the Code-First approach for Entity Framework, we can programmatically assign default values via a query as shown in Listing 3-13. Note that the pattern in Listing 3-13 doesn’t actually materialize (return from the database) instances of the Employee entity type with the default value. Instead, it projects, that is, places the results of the query into a collection of an anonymous type whose YearsWorked property is programmatically set to the value of 0 whenever the underlying value is null. Thus the underlying value in the column remains NULL, but we project a value of zero as a default value in our Entity Framework result. Keep in mind that an anonymous type, as shown in Listing 3-13, is a class that gets created on the fly at runtime based on the properties that we include within the curly braces that immediately precede the new keyword.

Listing 3-13.  Using Both LINQ and Entity SQL to Fill in Default Values for Nulls

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.employee");
    // add new test data
    context.Employees.Add(new Employee { Name = "Robin Rosen",
                                               YearsWorked = 3 });
    context.Employees.Add(new Employee { Name = "John Hancock" });
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Employees (using LINQ)");
    var employees = from e in context.Employees
                    select new {Name = e.Name, YearsWorked = e.YearsWorked ?? 0};
    foreach(var employee in employees)
    {
        Console.WriteLine("{0}, years worked: {1}",employee.Name,
                            employee.YearsWorked);
    }
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Employees (using ESQL w/named constructor)");
    string esql = @"select value Recipe3_6.Employee(e.EmployeeId,
                      e.Name,
                      case when e.YearsWorked is null then 0
                           else e.YearsWorked end)
                    from Employees as e";
    var employees = context.Database.SqlQuery<Employee>(esql);
    foreach(var employee in employees)
    {
        Console.WriteLine("{0}, years worked: {1}",employee.Name,
                            employee.YearsWorked.ToString());
    }
}

Following is the output of the code in Listing 3-13:

Employees (using LINQ)
Robin Rosen, years worked: 3
John Hancock, years worked: 0
Employees (using ESQL w/named constructor)
Robin Rosen, years worked: 3
John Hancock, years worked: 0

How It Works

Here, our approach is to use either LINQ or eSQL to project the results into a collection of an anonymous type. The query sets the YearsWorked to 0 when the underlying value is null.

For the LINQ approach, we use the C# null-coalescing operator ?? to assign the value of 0 when the underlying value is null. We project the results into a collection of an anonymous type.

For Entity SQL, we use a case statement to assign the value of 0 to YearsWorked when the underlying value is null. Here, we demonstrate how to use Entity SQL to materialize instances of the Employee entity type without setting the Default Value property for the entity. To do this, we use the named constructor for the entity type. This constructor assigns the values from the parameters to the properties in the same order as the properties are defined in the entity. In our case, the properties for the Employee entity are defined in the following order: EmployeeId, Name, and YearsWorked. The parameters to the constructor, as do our arguments in the eSQL query, follow this same order. Unfortunately, there is no corresponding name constructor syntax for LINQ to Entities.

Problem

You have a stored procedure that returns multiple result sets, and you want to materialize entities from each result set.

Solution

Suppose that you have a model like the one shown in Figure 3-8 and a stored procedure like the one shown in Listing 3-14, which returns both jobs and bids.

Listing 3-14.  A Stored Procedure That Returns Multiple Result Sets

create procedure Chapter3.GetBidDetails
as
begin
  select * from Chapter3.Job
  select * from Chapter3.Bid
end

In our model, for each job we have zero or more bids. Our stored procedure returns all of the jobs and all of the bids. We want to execute the stored procedure and materialize all of the jobs and all of the bids from the two result sets. To do this, follow the pattern in Listing 3-15.

Listing 3-15.  Materializing Jobs and Bids from the Two Result Sets Returned by Our Stored Procedure

using (var context = new EFRecipesEntities())
{
    var job1 = new Job { JobDetails = "Re-surface Parking Log" };
    var job2 = new Job { JobDetails = "Build Driveway" };
    job1.Bids.Add(new Bid { Amount = 948M, Bidder = "ABC Paving" });
    job1.Bids.Add(new Bid { Amount = 1028M, Bidder = "TopCoat Paving" });
    job2.Bids.Add(new Bid { Amount = 502M, Bidder = "Ace Concrete" });
    context.Jobs.AddObject(job1);
    context.Jobs.AddObject(job2);
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    var conn = context.Database.Connection;
    var cmd = conn.CreateCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "Chapter3.GetBidDetails";
    conn.Open();
    var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    var jobs = ((IObjectContextAdapter)context).ObjectContext.Translate<Job>(reader, "Jobs",
                                       MergeOption.AppendOnly).ToList();
    reader.NextResult();
    ((IObjectContextAdapter)context).ObjectContext.Translate<Bid>(reader, "Bids", MergeOption.AppendOnly).ToList();
    foreach (var job in jobs)
    {
        Console.WriteLine("
Job: {0}", job.JobDetails);
        foreach (var bid in job.Bids)
        {
            Console.WriteLine("	Bid: {0} from {1}",
                               bid.Amount.ToString("C"), bid.Bidder);
        }
    }
}

Following is the output of the code in Listing 3-15:

Job: Re-surface Parking Log
        Bid: $948.00 from ABC Paving
        Bid: $1,028.00 from TopCoat Paving
 
Job: Build Driveway
        Bid: $502.00 from Ace Concrete

How It Works

We start out by adding a couple of jobs and a few bids for the jobs. After adding them to the context, we use SaveChanges() to save them to the database.

Entity Framework 5.0 has improved capabilities for working with multiple results sets returned from a stored procedure. However, to leverage this functionality, you’ll have to use the legacy ObjectContext object, as the more recent DbContext object does not directly support multiple result sets. To solve the problem, we read the stored procedure data using the familiar SqlClient pattern. This pattern involves creating a SqlConnection, SqlCommand setting the command text to the name of the stored procedure and calling ExecuteReader() to get a data reader.

With a reader in hand, we use the Translate() method from the ObjectContext to materialize instances of the Job entity from the reader. This method takes a reader; the entity set name, and a merge option. The entity set name is required because an entity can live in multiple entity sets. Entity Framework needs to know which to use.

The merge option parameter is a little more interesting. Using MergeOption.AppendOnly causes the new instances to be added to the object context and tracked. We use this option because we want to use Entity Framework’s entity span to fix up the associations automatically between jobs and bids. For this to happen, we simply add to the context all of the jobs and all of the bids. Entity Framework will automatically associate the bids to the right jobs. This saves us a great deal of tedious code.

A simpler version of the Translate() method does not require a MergeOption. This version materializes objects that are disconnected from the object context. This is subtly different from objects that are not tracked in that the objects are created completely outside of the object context. If you were to use this simpler Translate() to read the jobs, you would not be able later to materialize new bids into the object context because Entity Framework would not have any reference to the associated jobs. Those jobs are completely disconnected from the object context. Additionally, you cannot change the properties of the instances and expect Entity Framework to be able to save those changes.

We used ToList() to force the evaluation of each query. This is required because the Translate() method returns an ObjectResult<T>. It does not actually cause the results to be read from the reader. We need to force the results to be read from the reader before we can use NextResult() to advance to the next result set. In practice, you would most likely construct your code to continue to loop through each result set with NextResult() that the stored procedure might return.

Although we didn’t run into it in this example, it is important to note that Translate() bypasses the mapping layer of the model. If you try to map an inheritance hierarchy or use an entity that has complex type properties, Translate() will fail. Translate() requires that the DbDataReader have columns that match each property on the entity. This matching is done using simple name matching. If a column name can’t be matched to a property, Translate() will fail.

Problem

You want to query entities in which a specific property value matches a value contained in a given list.

Solution

Suppose that you have a model like the one shown in Figure 3-9.

You want to find all of the books in a given list of categories. To do this using LINQ or Entity SQL, follow the pattern in Listing 3-16.

Listing 3-16.  Finding Books in a List of Categories Using Both LINQ and Entity SQL

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.category");
    context.Database.ExecuteSqlCommand("delete from chapter3.book");
    // add new test data
    var cat1 = new Category { Name = "Programming" };
    var cat2 = new Category { Name = "Databases" };
    var cat3 = new Category {Name = "Operating Systems"};
    context.Books.Add(new Book { Title = "F# In Practice", Category = cat1 });
    context.Books.Add(new Book { Title = "The Joy of SQL", Category = cat2 });
    context.Books.Add(new Book { Title = "Windows 7: The Untold Story",
                                       Category = cat3 });
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Books (using LINQ)");
    var cats = new List<string> { "Programming", "Databases" };
    var books = from b in context.Books
                where cats.Contains(b.Category.Name)
                select b;
    foreach (var book in books)
    {
        Console.WriteLine("'{0}' is in category: {1}", book.Title,
                            book.Category.Name);
    }
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Books (using ESQL)");
    var esql = @"select value b from Books as b
                 where b.Category.Name in {'Programming','Databases'}";
    var books = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Book>(esql);
    foreach (var book in books)
    {
        Console.WriteLine("'{0}' is in category: {1}", book.Title,
                            book.Category.Name);
    }
}

Following is the output of the code in Listing 3-16:

Books (using LINQ)
'F# In Practice' is in category: Programming
'The Joy of SQL' is in category: Databases
Books (using ESQL)
'F# In Practice' is in category: Programming
'The Joy of SQL' is in category: Databases

How It Works

For the LINQ query, we build a simple list of category names and include the list in the query along with the LINQ Contains query operator. The observant reader will note that we start with the cats collection and determine if it contains any category names. Entity Framework translates the Contains clause to a SQL statement with an in clause, as shown in Listing 3-17.

Listing 3-17.  The SQL Statement Created for the LINQ Expression from Listing 3-16

SELECT
[Extent1].[BookId] AS [BookId],
[Extent1].[Title] AS [Title],
[Extent1].[CategoryId] AS [CategoryId]
FROM  [chapter3].[Books] AS [Extent1]
LEFT OUTER JOIN [chapter3].[Category] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
WHERE [Extent2].[Name] IN (N'Programming',N'Databases')

It is interesting to note that the generated SQL statement in Listing 3-17 does not use parameters for the items in the in clause. This is different from the generated code we would see with LINQ to SQL, where the items in the list would be parameterized. With this code, we don’t run the risk of exceeding the parameters limit that is imposed by SQL Server.

If we are interested in finding all of the books in a given list of categories that are not yet categorized, we simply include null in the category list. The generated code is shown in Listing 3-18.

Listing 3-18.  The SQL Statement Created for a LINQ Expression Like the One in Listing 3-16, but with a Null in the List of Categories

SELECT
[Extent1].[BookId] AS [BookId],
[Extent1].[Title] AS [Title],
[Extent1].[CategoryId] AS [CategoryId]
FROM  [chapter3].[Books] AS [Extent1]
LEFT OUTER JOIN [chapter3].[Category] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
WHERE [Extent2].[Name] IN (N'Programming',N'Databases')
 
      OR [Extent2].[Name] IS NULL

For parity, we also include an Entity SQL version of the query, in which we explicitly include a SQL IN clause.

Problem

You want to want to retrieve some, but not all, related entities.

Solution

Let’s say that you have a model like the one shown in Figure 3-10.

In this model, we have a Worker who has experienced zero or more accidents. Each accident is classified by its severity. We want to retrieve all workers, but we are interested only in serious accidents. These are accidents with a severity greater than 2.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-19, we create entity classes for Worker and Accidents.

Listing 3-19.  Worker Entity Class

public class Worker
{
    public Worker()
    {
        Accidents = new HashSet<Accident>();
    }
  
    public int WorkerId { get; set; }
    public string Name { get; set; }
  
    public virtual ICollection<Accident> Accidents { get; set; }
}
  
public class Accident
{
    public int AccidentId { get; set; }
    public string Description { get; set; }
    public int? Severity { get; set; }
    public int WorkerId { get; set; }
  
    public virtual Worker Worker { get; set; }
}

Next, in Listing 3-20, we create the DbContext object required for our Code-First approach.

Listing 3-20.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString") {}
  
    public DbSet<Accident> Accidents { get; set; }
    public DbSet<Worker> Workers { get; set; }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Accident>().ToTable("Chapter3.Accident");
        modelBuilder.Entity<Worker>().ToTable("Chapter3.Worker");
        base.OnModelCreating(modelBuilder);
    }
}

To retrieve all of the workers, but limit the accidents retrieved to just the serious ones, follow the pattern in Listing 3-21.

Listing 3-21.  Retrieving Serious Accidents Using Anonymous Types and Using CreateSourceQuery()

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.accident");
    context.Database.ExecuteSqlCommand("delete from chapter3.worker");
    // add new test data
    var worker1 = new Worker { Name = "John Kearney" };
    var worker2 = new Worker { Name = "Nancy Roberts" };
    var worker3 = new Worker { Name = "Karla Gibbons" };
    context.Accidents.Add(new Accident {
                  Description = "Cuts and contusions",
                  Severity = 3, Worker = worker1 });
    context.Accidents.Add(new Accident {
                  Description = "Broken foot",
                  Severity = 4, Worker = worker1});
    context.Accidents.Add(new Accident {
                  Description = "Fall, no injuries",
                  Severity = 1, Worker = worker2});
    context.Accidents.Add(new Accident {
                  Description = "Minor burn",
                  Severity = 3, Worker = worker2});
    context.Accidents.Add(new Accident {
                  Description = "Back strain",
                  Severity = 2, Worker = worker3});
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    // explicitly disable lazy loading
    context.Configuration.LazyLoadingEnabled = false;
    var query = from w in context.Workers
                select new
                {
                    Worker = w,
                    Accidents = w.Accidents.Where(a => a.Severity > 2)
                };
    query.ToList();
    var workers = query.Select(r => r.Worker);
    Console.WriteLine("Workers with serious accidents...");
    foreach (var worker in workers)
    {
        Console.WriteLine("{0} had the following accidents", worker.Name);
        if (worker.Accidents.Count == 0)
            Console.WriteLine("	--None--");
        foreach (var accident in worker.Accidents)
        {
            Console.WriteLine("	{0}, severity: {1}",
                  accident.Description, accident.Severity.ToString());
        }
    }
}

Following is the output of the code in Listing 3-21:

Workers with serious accidents...
John Kearney had the following accidents
        Cuts and contusions, severity: 3
        Broken foot, severity: 4
Nancy Roberts had the following accidents
        Minor burn, severity: 3
Karla Gibbons had the following accidents
        --None--

How It Works

As you will see in Chapter 5, when we want to eagerly load a related collection, we often use the Include() method with a query path. (The Include() method returns the parent entity along with all of the child entities in a single query.) However, the Include() method does not allow filtering on the related child entities. In this recipe, we show a slight variation that allows you to load and filter related child entities.

In the block of code, we create a few workers and assign them accidents of varying levels of severity. Granted, it’s a little creepy to assign accidents to people, but it’s all in the name of getting some data with which you can work.

In the subsequent query, we select from all of the workers and project the results into an anonymous type. The type includes the worker and the collection of accidents. For the accidents, notice how we filter the collection to get just the serious accidents.

The very next line is important. Here we force the evaluation of the query by calling the ToList() method. (Keep in mind that LINQ queries typically default to deferred loading, meaning that the query is not actually executed until necessary. The ToList() method forces this very execution.) Enumerating this query brings all of the workers and all of the serious accidents into the DbContext. The anonymous type didn’t attach the accidents to the workers, but by bringing them into the Context, Entity Framework will fix up the navigation properties, attaching each collection of serious accidents to the appropriate worker. This process, commonly known as Entity Span, is a powerful yet subtle side effect that happens behind the scenes to fix up relationships between entities as they are materialized into the Entity Framework Context object.

We’ve turned off lazy loading so that only the accidents in our filter are loaded. (We’ll discuss lazy loading further Chapter 5.) With lazy loading on, all of the accidents would get loaded when we referenced each worker’s accidents. That would defeat the purpose of the filter.

Once we have the collection, we iterate through it, printing out each worker and their serious accidents. If a worker didn’t have any serious accidents, we print none to indicate their stellar safety record.

Problem

You want to combine the properties of two entities using a left-outer join.

Solution

Suppose that you have a model like the one shown in Figure 3-11.

The top-selling products have a related TopSelling entity. Of course, not all products are top sellers, and that’s why the relationship is one to zero or one. When a product is a top seller, the related TopSeller entity also contains the customer rating for the product. You want to find and present all of the products and their related TopSeller entities even if, in some cases, the product is not a top seller. In the case where a product does not have a related TopSelling entity, we simply set to the rating to “0”. In database terms, this is called a left-outer join.

The code in Listing 3-22 demonstrates three slightly different approaches to this problem.

Listing 3-22.  Doing a Left-Outer Join Between Entities

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.topselling");
    context.Database.ExecuteSqlCommand("delete from chapter3.product");
    // add new test data
    // note that p1 has no associated TopSelling entity as do the other products
    var p1 = new Product { Name = "Trailrunner Backpack" };
    var p2 = new Product { Name = "Green River Tent",
                           TopSelling = new TopSelling { Rating = 3 } };
    var p3 = new Product { Name = "Prairie Home Dutch Oven",
                           TopSelling = new TopSelling { Rating = 4 } };
    var p4 = new Product { Name = "QuickFire Fire Starter",
                           TopSelling = new TopSelling { Rating = 2 } };
    context.Products.Add(p1);
    context.Products.Add(p2);
    context.Products.Add(p3);
    context.Products.Add(p4);
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    var products = from p in context.Products
                   orderby p.TopSelling.Rating descending
                   select p;
    Console.WriteLine("All products, including those without ratings");
    foreach (var product in products)
    {
        Console.WriteLine("	{0} [rating: {1}]", product.Name,
            product.TopSelling == null ? "0"
                : product.TopSelling.Rating.ToString());
    }
}
 
using (var context = new EFRecipesEntities())
{
    var products = from p in context.Products
                   join t in context.TopSellings on
                      // note how we project the results together into another
                      // sequence, entitled 'g' and apply the DefaultIfEmpty method
                      p.ProductID equals t.ProductID into g
                   from tps in g.DefaultIfEmpty()
                   orderby tps.Rating descending
                   select new
                   {
                       Name = p.Name,
                       Rating = tps.Rating == null ? 0 : tps.Rating
                   };
 
    Console.WriteLine("
All products, including those without ratings
                                                                    ");
    foreach (var product in products)
    {
        if (product.Rating != 0)
            Console.WriteLine("	{0} [rating: {1}]", product.Name,
                product.Rating.ToString());
    }
}
 
using (var context = new EFRecipesEntities())
{
    var esql = @"select value p from products as p
                 order by case when p.TopSelling is null then 0
                                    else p.TopSelling.Rating end desc";
    var products =((IObjectContextAdapter)context).ObjectContext.CreateQuery<Product>(esql);
    Console.WriteLine("
All products, including those without ratings
                                               ");
    foreach (var product in products)
    {
        Console.WriteLine("	{0} [rating: {1}]", product.Name,
            product.TopSelling == null ? "0"
                : product.TopSelling.Rating.ToString());
 

    }
}

Following is the output of the code in Listing 3-22:

Top selling products sorted by rating
        Prairie Home Dutch Oven [rating: 4]
        Green River Tent [rating: 3]
        QuickFire Fire Starter [rating: 2]
               Trailrunner Backpack [rating: 0]Top selling products sorted by rating
        Prairie Home Dutch Oven [rating: 4]
        Green River Tent [rating: 3]
        QuickFire Fire Starter [rating: 2]
               Trailrunner Backpack [rating: 0]Top selling products sorted by rating
        Prairie Home Dutch Oven [rating: 4]
        Green River Tent [rating: 3]
        QuickFire Fire Starter [rating: 2]
               Trailrunner Backpack [rating: 0]

How It Works

In Listing 3-22, we show three slightly different approaches to this problem. The first approach is the simplest, as Entity Framework handles the join automatically for related entities based on a navigation property that was created between the two entities when the model was created. The entities are in a one-to-zero or one association, which means that Entity Framework will automatically generate a SQL query that includes a left-outer join between the two entities. When the product entities are materialized, any associated top sellers are also materialized. The TopSeller navigation property is either set to the associated TopSeller entity or to null if no TopSeller exists. If a TopSeller entity does not exist for a given Product (that is, it has not been rated as a top seller), we simply assign a value of “0” for the Product Rating.

In some cases, you might not have a relationship (for instance, a navigation property) between the entities that you want to join. In these cases, you can explicitly join the entities, projecting the results into an anonymous type. We need to project into an anonymous type because the unrelated entities won’t have navigation properties, so we wouldn’t otherwise be able to reference the related entity.

The code in the second query block illustrates this approach. Here we join the entities on the ProductId key and put the result into a new sequence entitled “g”. Then, from g we apply the DefaultIfEmpty() method to fill in nulls when g is empty. Sure enough, when the SQL is generated, it includes a left-outer join between the two entities. We include an orderby clause to order the results by the rating. Finally, we project the results into an anonymous type.

In the third approach, we show you how to do the left-outer join more explicitly using Entity SQL, embedding an Entity SQL Case statement within the query.

Problem

You are using Table per Hierarchy inheritance, and you want to sort results by the derived type.

Solution

Let’s suppose that you have a model like the one shown in Figure 3-12.

This model uses Table per Hierarchy inheritance (TPH), which is a feature of Entity Framework. TPH creates an inheritance structure where a parent and given number of related child classes all derive from a single database table.

In this example, the Media entity has a property entitled MediaType, which is used as a discriminator property for our TPH construct. The value of MediaType determines which derived type (Article, Picture, or Video) is represented by a row from the database. The discriminator column has a value of 1 for an Article type, 2 for a Video type, and 3 for the Picture type. Because the property is used only to determine the derived type, it is not shown as part of the Media entity.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-23, we create the entity classes. To keep the example simple, we’ll create empty child objects, as we only want to demonstrate how to order a query based on a derived type.

Listing 3-23.  Parent and Child Entity Types

public class Media
{
    public int MediaId { get; set; }
    public string Title { get; set; }
}
 
public class Article : Media
{
}
 
public class Picture : Media
{
}
public class Video : Media
{
}

Next, in Listing 3-24, we create the DbContext object, which is your gateway into Entity Framework functionality when leveraging the Code-First approach. Note how in the OnModelCreating method, we explicitly map the discriminator column, MediaType, to the child entities using a FluentAPI coding approach (that is, chaining together extension methods to create an operation).

Listing 3-24.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString")
    {
    }
  
    public DbSet<Media> Media { get; set; }
 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Media>().ToTable("Chapter3.Media");
  
        // Map child entities to the 'Discriminator' column, MediaType, from parent table,
        // which will determine the type of medium
        modelBuilder.Entity<Media>().Map<Article>(x => x.Requires("MediaType").HasValue(1));
        modelBuilder.Entity<Media>().Map<Picture>(x => x.Requires("MediaType").HasValue(2));
        modelBuilder.Entity<Media>().Map<Video>(x => x.Requires("MediaType").HasValue(3));
  
        base.OnModelCreating(modelBuilder);
    }
}

With the Code-First artifacts created, we’ll query the model for all of the media and sort the results by the derived types: Article, Video, and Picture. To do this, follow the pattern in Listing 3-25.

Listing 3-25.  Sorting Table per Hierarchy Inheritance by Type

using (var context = new EFRecipesEntities())
{
    context.Media.Add(new Article {
                  Title = "Woodworkers' Favorite Tools" });
    context.Media.Add(new Article {
                  Title = "Building a Cigar Chair" });
    context.Media.Add(new Video {
                  Title = "Upholstering the Cigar Chair" });
    context.Media.Add(new Video {
                  Title = "Applying Finish to the Cigar Chair" });
    context.Media.Add(new Picture {
                  Title = "Photos of My Cigar Chair" });
    context.Media.Add(new Video {
                  Title = "Tour of My Woodworking Shop" });
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    var allMedia = from m in context.Media
                   let mediatype = m is Article ? 1 :
                                   m is Video ? 2 : 3
                   orderby mediatype
                   select m;
    Console.WriteLine("All Media sorted by type...");
    foreach (var media in allMedia)
    {
        Console.WriteLine("Title: {0} [{1}]", media.Title, media.GetType().Name);
    }
}

Following is the output of the code in Listing 3-25:

All Media sorted by type...
Title: Woodworkers' Favorite Tools [Article]
Title: Building a Cigar Chair [Article]
Title: Upholstering the Cigar Chair [Video]
Title: Applying Finish to the Cigar Chair [Video]
Title: Tour of My Woodworking Shop [Video]
Title: Photos of My Cigar Chair [Picture]

How It Works

When we use Table per Hierarchy inheritance, we leverage a column in the table to distinguish which derived type represents any given row. This column, often referred to as the discriminator column, can’t be mapped to a property of the base entity. Because we don’t have a property with the discriminator value, we need to create a variable to hold comparable discriminator values so that we can do the sort. To do this, we use a LINQ let clause, which creates a the mediatype variable. We use a conditional statement to assign an integer to this variable based on the type of the media. For Articles, we assign the value 1. For Videos, we assign the value 2. We assign a value of 3 to anything else, which will always be of type Picture because no other derived types remain.

Problem

You want to create a query with a filter and paging.

Solution

Let’s say that you have a Customer entity type in a model, as shown in Figure 3-13.

You have an application that displays customers based on a filter. Your company has many customers (perhaps millions!), and to keep the user experience as responsive as possible, you want to show only a limited number of customers on each page. To create a query that both filters the customers and returns a manageable set for each results page in your application, follow the pattern in Listing 3-26.

Listing 3-26.  Filtering and Paging a Query

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.customer");
    // add new test data
    context.Customers.Add(new Customer { Name = "Roberts, Jill",
                                 Email = "[email protected]" });
    context.Customers.Add(new Customer { Name = "Robertson, Alice",
                                 Email = "[email protected]" });
    context.Customers.Add(new Customer { Name = "Rogers, Steven",
                                 Email = "[email protected]" });
    context.Customers.Add(new Customer { Name = "Roe, Allen",
                                 Email = "[email protected]" });
    context.Customers.Add(new Customer { Name = "Jones, Chris",
                                 Email = "[email protected]" });
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    string match = "Ro";
    int pageIndex = 0;
    int pageSize = 3;
 
    var customers = context.Customers.Where(c => c.Name.StartsWith(match))
                        .OrderBy(c => c.Name)
                        .Skip(pageIndex * pageSize)
                        .Take(pageSize);
    Console.WriteLine("Customers Ro*");
    foreach (var customer in customers)
    {
        Console.WriteLine("{0} [email: {1}]", customer.Name, customer.Email);
    }
}
using (var context = new EFRecipesEntities())
{
    string match = "Ro%";
    int pageIndex = 0;
    int pageSize = 3;
 
    var esql = @"select value c from Customers as c
                 where c.Name like @Name
                 order by c.Name
                 skip @Skip limit @Limit";
    Console.WriteLine("
Customers Ro*");
    var customers =
        ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Customer>(esql, new[]
                      {
                        new ObjectParameter("Name",match),
                        new ObjectParameter("Skip",pageIndex * pageSize),
                        new ObjectParameter("Limit",pageSize)
                      });
    foreach (var customer in customers)
    {
        Console.WriteLine("{0} [email: {1}]", customer.Name, customer.Email);
    }
}

Following is the output from the code in Listing 3-26:

Customers Ro*
Roberts, Jill [email: [email protected]]
Robertson, Alice [email: [email protected]]
Roe, Allen [email: [email protected]]
 
Customers Ro*
Roberts, Jill [email: [email protected]]
Robertson, Alice [email: [email protected]]
Roe, Allen [email: [email protected]]

How It Works

In Listing 3-26, we show two different approaches to the problem. In the first approach, we use LINQ-To-Entities extension methods to construct a LINQ query. We use the Where() method to filter the results to customers whose last name starts with Ro. Because we are using the StartsWith() extension method inside the lambda expression, we don’t need to use a SQL wildcard expression such as “Ro%”.

After filtering, we use the OrderBy() method to order the results. Ordered results are required by the Skip() method. We use the Skip() method to move over pageIndex number of pages, each of size pageSize. We limit the results with the Take() method. We only need to take one page of results.

Note that in this code block, we create the entire query using LINQ extension methods and not the SQL query-like expressions that we have seen in examples up to now. Both the Skip() and Take() methods are only exposed by extension methods, not query syntax.

For the second approach, we construct a complete, parameterized Entity SQL expression. This is perhaps the most familiar way to solve the problem, but it exposes some of the inherent mismatch risks between a query language expressed using strings and executable code expressed, in this case, in C#.

Problem

You have an entity type with a DateTime property, and you want to group instances of this type based on just the date portion of the property.

Solution

Let’s say that you have a Registration entity type in your model, and the Registration type has a DateTime property. Your model might look like the one shown in Figure 3-14.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-27, we create the entity classes.

Listing 3-27.  Registration Entity Type

public class Registration
{
    public int RegistrationId { get; set; }
    public string StudentName { get; set; }
    public DateTime? RegistrationDate { get; set; }
}

Next, in Listing 3-28, we create the DbContext object, which is your gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 3-28.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString") {}
  
    public DbSet<Registration> Registrations { get; set; }
   
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Registration>().ToTable("Chapter3.Registration");
        base.OnModelCreating(modelBuilder);
    }
}
  

We want to group all of the registrations by just the date portion of the RegistrationDate property. You might be tempted in LINQ to group by RegistrationDate.Date. Although this will compile, you will receive a runtime error complaining that Date can’t be translated into SQL. To group by just the date portion of the RegistrationDate, follow the pattern in Listing 3-29.

Listing 3-29.  Grouping by the Date Portion of a DateTime Property

using (var context = new EFRecipesEntities())
{
    context.Registrations.Add(new Registration {
          StudentName = "Jill Rogers",
          RegistrationDate = DateTime.Parse("12/03/2009 9:30 pm") });
    context.Registrations.Add(new Registration {
          StudentName = "Steven Combs",
          RegistrationDate = DateTime.Parse("12/03/2009 10:45 am") });
    context.Registrations.Add(new Registration {
          StudentName = "Robin Rosen",
          RegistrationDate = DateTime.Parse("12/04/2009 11:18 am") });
    context.Registrations.Add(new Registration {
          StudentName = "Allen Smith",
          RegistrationDate = DateTime.Parse("12/04/2009 3:31 pm") });
    context.SaveChanges();
}
  
using (var context = new EFRecipesEntities())
{
    var groups = from r in context.Registrations
    // leverage built-in TruncateTime function to extract date portion
                 group r by DbFunctions.TruncateTime(r.RegistrationDate)
                    into g
                 select g;
    foreach (var element in groups)
    {
        Console.WriteLine("Registrations for {0}",
               ((DateTime)element.Key).ToShortDateString());
        foreach (var registration in element)
        {
            Console.WriteLine("	{0}", registration.StudentName);
        }
    }
}
  

Following is the output of the code in Listing 3-29:

Registrations for 12/3/2009
        Jill Rogers
        Steven Combs
Registrations for 12/4/2009
        Robin Rosen
        Allen Smith

How It Works

The key to grouping the registrations by the date portion of the RegistrationDate property is to use the Truncate() function. This built-in Entity Framework function, contained in the DbFunctions class, extracts just the date portion of the DateTime value. The built-in DbFunctions contain a wide array of formatting, aggregation, string manipulation, date-time, and mathematical services, and they are found in the System.Data.Entity namespace. The legacy class, EntityFunctions, used prior to Entity Framework 6, will still work with Entity Framework 6, but will give you a compiler warning suggesting you move to the DbFunctions class. We’ll have a lot more to say about functions in Chapter 11.

Problem

You have two entity types in a one-to-many association, and you want, in one query, to obtain a flattened projection of all of the entities in the association. By flattened, we are referring to denormalizing, or compressing, an object graph with parent/child relationships into a result represented by a single class.

Solution

Let’s say that you have a couple of entity types in a one-to-many association. Perhaps your model looks something like the one shown in Figure 3-15.

You want to get all of the associates and all of their salary history in one query. There may be some new hires that are in the system, but they don’t yet have a salary set. You want your query results to include these associates as well.

To query the model and get the results you want, follow the pattern in Listing 3-30.

Listing 3-30.  Flattening Out the Results Using Both LINQ and Entity SQL

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.associatesalary");
    context.Database.ExecuteSqlCommand("delete from chapter3.associate");
    // add new test data
    var assoc1 = new Associate { Name = "Janis Roberts" };
    var assoc2 = new Associate { Name = "Kevin Hodges" };
    var assoc3 = new Associate { Name = "Bill Jordan" };
    var salary1 = new AssociateSalary { Salary = 39500M,
                        SalaryDate = DateTime.Parse("8/4/09") };
    var salary2 = new AssociateSalary { Salary = 41900M,
                        SalaryDate = DateTime.Parse("2/5/10") };
    var salary3 = new AssociateSalary { Salary = 33500M,
                        SalaryDate = DateTime.Parse("10/08/09") };
    assoc2.AssociateSalaries.Add(salary1);
    assoc2.AssociateSalaries.Add(salary2);
    assoc3.AssociateSalaries.Add(salary3);
    context.Associates.Add(assoc1);
    context.Associates.Add(assoc2);
    context.Associates.Add(assoc3);
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Using LINQ...");
    var allHistory = from a in context.Associates
                     from ah in a.AssociateSalaries.DefaultIfEmpty()
                     orderby a.Name
                     select new
                     {
                         Name = a.Name,
                         Salary = (decimal ?) ah.Salary,
                         Date = (DateTime ?) ah.SalaryDate
                     };
    Console.WriteLine("Associate Salary History");
    foreach (var history in allHistory)
    {
        if (history.Salary.HasValue)
            Console.WriteLine("{0} Salary on {1} was {2}", history.Name,
                               history.Date.Value.ToShortDateString(),
                               history.Salary.Value.ToString("C"));
        else
            Console.WriteLine("{0} --", history.Name);
    }
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("
Using Entity SQL...");
    var esql = @"select a.Name, h.Salary, h.SalaryDate
                 from Associates as a outer apply
                   a.AssociateSalaries as h order by a.Name";
    var allHistory =
        ((IObjectContextAdapter)context).ObjectContext.CreateQuery<DbDataRecord>(esql);
Console.WriteLine("Associate Salary History");
foreach (var history in allHistory)
    {
        if (history["Salary"] != DBNull.Value)
            Console.WriteLine("{0} Salary on {1:d} was {2:c}", history["Name"],
                               history["SalaryDate"], history["Salary"]);
        else
            Console.WriteLine("{0} --",history["Name"]);
    }
}

The trick here is that we want to “flatten” out hierarchical data, such as an associate with multiple salary inputs.

Following is the output of the code in Listing 3-30:

Using LINQ...
Associate Salary History
Bill Jordan Salary on 10/8/2009 was $33,500.00
Janis Roberts --
Kevin Hodges Salary on 8/4/2009 was $39,500.00
Kevin Hodges Salary on 2/5/2010 was $41,900.00
Using Entity SQL...
Bill Jordan Salary on 10/8/2009 was $33,500.00
Janis Roberts --
Kevin Hodges Salary on 8/4/2009 was $39,500.00
Kevin Hodges Salary on 2/5/2010 was $41,900.00

How It Works

To flatten the query results, we followed the strategy in Recipe 3-10 and used a nested from clause and the DefaultIfEmpty() method to get a left-outer join between the tables. The DefaultIfEmpty() method ensured that we have rows from the left side (the Associate entities), even if there are no corresponding rows on the right side (AssociateSalary entities). We project the results into an anonymous type, being careful to capture null values for the salary and salary date when there are no corresponding AssociateSalary entities.

For the Entity SQL solution, we use the outer apply operator to create unique pairings between each Associate entity and AssociateSalary entity. Both the cross and outer apply operators are available in SQL Server.

Problem

You want to group the results of a query by multiple properties so as to group by multiple columns when the query executes against the database.

Solution

Let’s say that you have a model with an Event entity type like the one shown in Figure 3-16. Event has a name, city, and state. You want to group events by state and then by city.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-31, we create the entity classes.

Listing 3-31.  Event Entity Type

public class Event
{
    public int EventId { get; set; }
    public string Name { get; set; }
    public string State { get; set; }
    public string City { get; set; }
}

Next, in Listing 3-32, we create the DbContext object, which is your gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 3-32.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString") {}
  
    public DbSet<Event> Events { get; set; }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Event>().ToTable("Chapter3.Event");
        base.OnModelCreating(modelBuilder);
    }
}

To get all of the events grouped by state and then city, follow the pattern in Listing 3-33.

Listing 3-33.  Grouping by Multiple Properties

using (var context = new EFRecipesEntities())
{
    // delete previous test data
    context.Database.ExecuteSqlCommand("delete from chapter3.event");
    // add new test data
    context.Events.Add(new Event { Name = "TechFest 2010",
                                         State = "TX", City = "Dallas" });
    context.Events.Add(new Event { Name = "Little Blue River Festival",
                                         State = "MO", City = "Raytown" });
    context.Events.Add(new Event { Name = "Fourth of July Fireworks",
                                         State = "MO", City = "Raytown" });
    context.Events.Add(new Event { Name = "BBQ Ribs Championship",
                                         State = "TX", City = "Dallas" });
    context.Events.Add(new Event { Name = "Thunder on the Ohio",
                                         State = "KY", City = "Louisville" });
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("Using LINQ");
    var results = from e in context.Events
                 // create annonymous type to encapsulate composite
                 // sort key of State and City
                 group e by new { e.State, e.City } into g
                 select new
                     {
                         State = g.Key.State,
                         City = g.Key.City,
                         Events = g                     };
    Console.WriteLine("Events by State and City...");
    foreach (var item in results)
    {
        Console.WriteLine("{0}, {1}", item.City, item.State);
        foreach (var ev in item.Events)
        {
            Console.WriteLine("	{0}", ev.Name);
        }
    }
}
 
using (var context = new EFRecipesEntities())
{
    Console.WriteLine("
Using Entity SQL");
    var esql = @"select e.State, e.City, GroupPartition(e) as Events
                 from Events as e
                 group by e.State, e.City";
    var records =
        ((IObjectContextAdapter)context).ObjectContext.CreateQuery<DbDataRecord>(esql);
    Console.WriteLine("Events by State and City...");
    foreach (var rec in records)
    {
        Console.WriteLine("{0}, {1}", rec["City"], rec["State"]);
        var events = (List<Event>)rec["Events"];
        foreach (var ev in events)
        {
            Console.WriteLine("	{0}", ev.Name);
        }
    }
}

Following is the output of the code in Listing 3-33:

Using LINQ
Events by State and City...
Louisville, KY
        Thunder on the Ohio
Raytown, MO
        Little Blue River Festival
        Fourth of July Fireworks
Dallas, TX
        TechFest 2010
        BBQ Ribs Championship
 
Using Entity SQL
Events by State and City...
Louisville, KY
        Thunder on the Ohio
Raytown, MO
        Little Blue River Festival
        Fourth of July Fireworks
Dallas, TX
        TechFest 2010
        BBQ Ribs Championship

How It Works

In Listing 3-33, we show two different approaches to the problem. The first approach uses LINQ and the group by operator to group the results by state and city. When using the group by operator for multiple properties, we create an anonymous type to initially group the data. We use an into clause to send the groups to g, which is a second sequence created to hold the results of the query.

We project the results from g into a second anonymous type getting the State from the group key’s State field (from the first anonymous type) and the City from the group key’s City field. For the events, we simply select all of the members of the group.

For the Entity SQL approach, we can only project columns used in the group by clause, a constant value, or a computed value from using an aggregate function. In our case, we project the state, city, and the collection of events for each grouping.

Problem

You want to use bitwise operators to filter a query.

Solution

Let’s say that you have an entity type with an integer property that you want to use as a set of bit flags. You’ll use some of the bits in this property to represent the presence or absence of some particular attribute for the entity. For example, suppose you have an entity type for patrons of a local art gallery. Some patrons contribute money. Some volunteer during gallery hours. A few patrons serve on the board of directors. A few patrons support the art gallery in more than one way. A model with this entity type is shown in Figure 3-17.

We want to query for patrons and filter on the type of sponsorship provided by the patron. To do this, follow the pattern in Listing 3-34.

Listing 3-34.  Using Bitwise Operators in a Query

static void Main()
{
    RunExample();
}
 
[Flags]
public enum SponsorTypes
{
    None = 0,
    ContributesMoney = 1,
    Volunteers = 2,
    IsABoardMember = 4
};
  
static void RunExample()
{
    using (var context = new EFRecipesEntities())
    {
        // delete previous test data
        context.Database.ExecuteSqlCommand("delete from chapter3.patron");
        // add new test data
        context.Patrons.Add(new Patron { Name = "Jill Roberts",
                   SponsorType = (int)SponsorTypes.ContributesMoney });
        context.Patrons.Add(new Patron { Name = "Ryan Keyes",
                   // note the useage of the bitwise OR operator: '|'
                   SponsorType = (int)(SponsorTypes.ContributesMoney |
                                       SponsorTypes.IsABoardMember)});
        context.Patrons.Add(new Patron {Name = "Karen Rosen",
                   SponsorType = (int)SponsorTypes.Volunteers});
        context.Patrons.Add(new Patron {Name = "Steven King",
                   SponsorType = (int)(SponsorTypes.ContributesMoney |
                                       SponsorTypes.Volunteers)});
        context.SaveChanges();
    }
 
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("Using LINQ...");
        var sponsors = from p in context.Patrons
                       // note the useage of the bitwise AND operator: '&'
                       where (p.SponsorType &
                              (int)SponsorTypes.ContributesMoney) != 0
                       select p;
        Console.WriteLine("Patrons who contribute money");
        foreach (var sponsor in sponsors)
        {
            Console.WriteLine("	{0}", sponsor.Name);
        }
    }
 
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("
Using Entity SQL...");
        var esql = @"select value p from Patrons as p
                     where BitWiseAnd(p.SponsorType, @type) <> 0";
        var sponsors = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Patron>(esql,
           new ObjectParameter("type", (int)SponsorTypes.ContributesMoney));
        Console.WriteLine("Patrons who contribute money");
        foreach (var sponsor in sponsors)
        {
            Console.WriteLine("	{0}", sponsor.Name);
        }
    }
}

Following is the output of the code in Listing 3-34:

Using LINQ...
Patrons who contribute money
        Jill Roberts
        Ryan Keyes
        Steven King
 
Using Entity SQL...
Patrons who contribute money
        Jill Roberts
        Ryan Keyes
        Steven King

How It Works

In our model, the Patron entity type packs multiple bit flags into a single integer property. A patron can sponsor the gallery in a number of ways. Each type of sponsorship is represented as a different bit in the SponsorType property. We represent each of the ways a sponsor can contribute in the SponsorTypes enum. We are careful to assign integers in power of 2 increments for each sponsor type. This means that each will have exactly one unique bit in the bits of the SponsorType property.

When we insert patrons, we assign the sponsorship type to the SponsorType property. For patrons that contribute in more than one way, we simply use the bitwise OR (|) operator to build the bit pattern representing all of the ways the patron contributes to the gallery.

For the LINQ query, we use the bitwise AND (&) operator to extract the bit for the ContributesMoney flag from the SponsorType property value. If the result is nonzero, then the patron has the ContributesMoney flag set. If we needed to find patrons that contribute in more than one way, we would OR all of the SponsorTypes we’re interested in together before we used the AND operator to extract one or more set bits.

The second solution demonstrates the same approach using Entity SQL. Here we use the BitWiseAnd() function to extract the set bit. Entity SQL supports a full complement of bitwise functions.

Problem

You want to join two entity types on multiple properties.

Solution

Let’s say that you have a model like the one shown in Figure 3-18. The Account entity type is in a one-to-many association with the Order type. Each account may have many orders, while each order is associated with exactly one account. You want to find all of the orders that are being shipped to the same city and state as the account.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-35, we create the entity classes.

Listing 3-35.  Account and Order Entity Types

public class Account
{
    public Account()
    {
        Orders = new HashSet<Order>();
    }

    public int AccountId { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}
 
public class Order
{
    public int OrderId { get; set; }
    public Decimal Amount { get; set; }
    public int AccountId { get; set; }
    public string ShipCity { get; set; }
    public string ShipState { get; set; }
    public virtual Account Account { get; set; }
}

Next, in Listing 3-36, we create the DbContext object, which is your gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 3-36.  The DbContext Object

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString") {}
  
    public DbSet<Order> Orders { get; set; }
    public DbSet<Account> Accounts { get; set; }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Account>().ToTable("Chapter3.Account");
        modelBuilder.Entity<Order>().ToTable("Chapter3.Order");
  
        base.OnModelCreating(modelBuilder);
    }
}
  

To find the orders, follow the pattern in Listing 3-37.

Listing 3-37.  Using a Join on Multiple Properties to Find All of the Orders Being Shipped to the Account’s City and State

using (var context = new EFRecipesEntities())
{
    var a1 = new Account { City = "Raytown", State = "MO" };
    a1.CustomerOrders.Add(new CustomerOrder { Amount = 223.09M, ShipCity = "Raytown",
                              ShipState = "MO" });
    a1. CustomerOrders.Add(new CustomerOrder { Amount = 189.32M, ShipCity = "Olathe",
                              ShipState = "KS" });
  
    var a2 = new Account { City = "Kansas City", State = "MO" };
    a2. CustomerOrders.Add(new CustomerOrder { Amount = 99.29M, ShipCity = "Kansas City",
                              ShipState = "MO" });
  

    var a3 = new Account { City = "North Kansas City", State = "MO"};
    a3. CustomerOrders.Add(new CustomerOrder { Amount = 102.29M, ShipCity = "Overland Park",
                              ShipState = "KS" });
    context.Accounts.Add(a1);
    context.Accounts.Add(a2);
    context.Accounts.Add(a3);
    context.SaveChanges();
}
  
using (var context = new EFRecipesEntities())
{
    var orders = from o in context.CustomerOrders
                 join a in context.Accounts on
                  new {Id = o.AccountID, City = o.ShipCity, State = o.ShipState }
                 equals
                  new {Id = a.AccountID, City = a.City, State = a.State }
                 select o;
  
    Console.WriteLine("Orders shipped to the account's city, state...");
    foreach (var order in orders)
    {
        Console.WriteLine("	Order {0} for {1}", order.AccountID.ToString(),
                            order.Amount.ToString());
    }
}

Following is the output of the code in Listing 3-37:

Orders shipped to the account's city, state...
       Order 31 for $223.09
       Order 32 for $99.29

How It Works

To solve this problem, you could find all the accounts and then go through each Orders collection and find the orders that are in the same city and state as the account. For a small number of accounts, this may be a reasonable solution. But in general, it is best to push this sort of processing into the store layer where it can be handled much more efficiently.

Out-of-the-gate, both Account and Order are joined by the AccountId property. However, in this solution, we form an explicit join by creating an anonymous type on each side of the equals clause for each of the entities. The anonymous construct is required when we join entities on more than one property. We need to make sure that both anonymous types are the same. They must have the same properties in the same order. Here, we are explicitly creating an inner-join relationship between the two tables on the database, meaning that orders to other cities and states would not be included due to the join condition.