Note On <Programming Entity Framework 2nd Edition> -02: How to Use SqlExpress Profiler 2.0

Chapter 3: Querying Entity Data Models

Example 3-3. Querying with Entity SQL

The example code of Entity SQL in this chapter in page 57 doesn't work in Entity Framework 6.0. And in EF5.0, you need to change it in this way:

var queryString = "SELECT VALUE c " +"FROM PROGRAMMINGEFDB1Entities.Contact AS c " +"WHERE c.FirstName='Robert'";ObjectQuery<Contact> contacts = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Contact>(queryString);foreach (var contact in contacts){Console.WriteLine("{0} {1}",contact.FirstName.Trim(),contact.LastName);}

If you compose the query with multiple items(Page 59), like this:

var queryString = "SELECT c.LastName, c.Title " +"FROM PROGRAMMINGEFDB1Entities.Contact AS c " +"WHERE c.FirstName='Robert'";

The author didn't elaborate how to invoke the methods to make it work immediately. You couldn't find the answer until Chapter 5:

ObjectQuery<Contact> contacts = this.objEntity.CreateQuery<Contact>(query);

Example 3-6. Entity SQL query builder method

And the example in page 65 which demonstrating Entity SQL query builder:

var contacts = context.Contacts.Where("it.FirstName = 'Robert'").OrderBy("it.LastName");

This also doesn't work in EF5.0+ any more. In order to make it work, you need to do it in this way:

ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;ObjectSet<Contact> objectContact = objectContext.CreateObjectSet<Contact>("Contact");var contacts = objectContact.Where("it.FirstName = 'Robert'").OrderBy("it.LastName");

Chapter 4: Exploring LINQ to Entities in Greater Depth

Example 4-3. Anonymous types as properties

And, in order to prevent Exception when running the sample code at Page 83: (because self referencing loop is detected when serializing the entities to JSON format)

var contacts = from c in context.Contactswhere c.FirstName == "Robert"let foo = new {ContactName = new {c.Title, c.LastName, c.FirstName},c.Addresses}orderby foo.ContactName.LastNameselect foo;

You need to add the line to WebApiConfig.cs

config.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;

Example 4-30. Filtering related data in a query using projections

Sample code at page 99 fails in LINQPad:

from pr in ProductRegistrationsselect new { pr, pr.RegistrationApproveRecords.Where(ra => ra.status = 1) }

Error:

Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.

Chapter 5: Exploring Entity SQL in Greater Depth

Example 5-10. An aggregate in a subquery

string query = "SELECT e.invoice_number, (SELECT VALUE MAX(a.status) FROM e.RegistrationApproveRecords as a) " +"FROM samsunginternalEntities.ProductRegistrations AS e ";ObjectQuery<DbDataRecord> records = this.objEntity.CreateQuery<DbDataRecord>(query);foreach (DbDataRecord record in records){Trace.WriteLine("model_code: " + record[0]);var statusList = record[1] as List<short?>;//var statusList = (record[1] as List<short?>).Where(o=>o.HasValue).Select(o=>((int)o)).ToList();foreach (var x in statusList){Trace.WriteLine("x: " + x);}}

Chapter 6:Modifying Entities and Saving Changes

Install and use SQL Server Profiler:

Go to its official download page to download the package, its called Express Profiler now, and its current version is 2.0: https://expressprofiler.codeplex.com/

After unzipping the file, you will see:

Run ExpressProfiler.EcosystemInstall.msi. And then you are ready, run the exe under /Standalone.

Now you see it:

Click File->Start Trace->Run/Run with filters:

And you can execute queries from Entity Framework against the SQL Server: