Zeeshan Hirani

Contributions to Entity framework community

2008-12-18T18:39:37Z

If you had been reading my blog, you must be wondering where did he go after publishing such awesome blog entries(jk). I had gotten lot of good feed back on the examples I had done on linq to SQL but like all good things must come to an end so did linq to SQL for me. My company was committed in moving forward with EF so I decided to move forward with Entity framework as well. During the process I learned so much stuff and felt the pain most developers went through with this technology. No doubt EF has a learning curve like any other technology but mapping scenarios offered by EF are simply great.

May be the reader base can get the same perception about EF and the mapping scenarios it offers even in version 1 as I did, after reading the PDF that contains 500 pages of content about entity framework. I wish I could complete the whole thing and discuss every aspect of EF that I had learned. Time is definitely a concern for me because I have to move to other cool things that I have been wanting to get to but never made it.

Below is a link to the PDF document that contains examples of various mapping scenarios and concepts that I have learned about entity framework

Entity Framework learning guide

I have updated the project to include the console app that executes the models. In addition, the zip also contains all the databases required for the projects to run.

Complete Project With databases

Due to time constraints, I have not been able to do editing or modify the English to improve its readability. My intent was to use this document as learning guide for me and my co-workers but I felt that content is useful enough to benefit rest of the community.

I hope everyone enjoys the content. If you have any feedback, please be sure to send me an email at zeeshanjhirani@gmail.com

Linq To SQL Optimizes Eager Loading

2008-08-23T03:47:00Z

If you have read my previous blog posting about the Load operator in entity framework and how it works in terms of lazy loading object regardless if it object has been tracked earlier, you must be keen to know how does this behavior work with Linq to SQL. I must say Linq to SQL has always surprised me in terms of how efficient it is in terms of dealing with retrieving data from the database. When you lazy load a particular entity reference, Linq to SQL will first track its tracking repository to see if it can find the object there. If an object is found it by passes database and simply returns the object from its cache. This is an optimized behavior as compared to how entity framework tackles this problem. For example if I have much of orders and I want to lazy load its customer, Linq to SQL will not make a database call for every Order to get its customer. If those orders belong to a total 5 distinct customers, than only 5 database calls will be made. Below is an example that shows this behavior.

In the above code, I am retrieving Orders shipped in the city of London and than lazy loading its customer to print the customer for each order. Although orders returned numbered to 33 but the total database calls made was 5. This is because those 33 orders belong to total of 5 customers. Linq to SQL was smart enough to use its tracking service to figure out that if it can find an object in the cache, there is no need to make a call to the database.

Avoiding Roundtrip with Load in Entity Framework

2008-08-23T02:07:34Z

If you have queries in your classes that you are reusing across your entire business layer, it becomes really hard sometimes to do eager loading of certain child entities on those queries. For example, if you have method that runs a complex linq to entity query to return you a collection of Orders. What if in certain scenarios of your application, you would like to retrieve Customers for those orders as well. Either you can create another method just like your previous method which has that complex query duplicated but along with that query you support the concept of eager loading of Customer entities as well. Other option you have is if the orders returned from the method are not too many and in manageable size, than you may end up wanting to reuse the method and incur the cost of lazy loading of the Customers. Below is an example that shows how I would lazy load Customers for an Order.

In the above code, I have a method called GetOrdersBycity which retrieves the Orders for the city of London. Just to keep you in loop, the total orders in the database for the city of London is 33. Since we also want to print the CustomerId for those orders, I am explicitly calling CustomerReference.Load to load the customer for that order. We need to call load on the customer reference otherwise we will end up with a null reference because entity framework does not load anything automatically from the database. Since there are 33 orders that meet the criteria we end up making 33 database calls to retrieve customers for those orders. Now the question is, do we really think that entity framework should have made 33 database calls. Personally I think, entity framework, should make database calls equal to the number of unique customers for those orders. Here is the reason why. Since we already have orders in our hand and each order knows what CustomerId it belongs to, I think entity framework should look into its object tracking service to see if its tracking this object. If the object is found in the tracking repository, it should not make an extra database call to retrieve the same customer that was already being tracked in the tracking service. But the reality of the matter is, if you turn on your profiler, you would see 33 database calls being made.

The question is how can we prevent the entity framework from making extra round trips to the database? Entity framework exposes an additional property called IsLoaded which you can check to see if the reference to the object is already loaded. If the object is already from a previous database query, than do not call load on the object again. Below is an example that shows to achieve that.

In the above code, I am checking the IsLoaded property to check if customer reference is already loaded. Only when the customer is not loaded I call Load. By applying a simple tweak in my query, I end up only making 5 database calls which means that I had 5 unique customers for all 33 orders. With simple checking we managed to improve our loading experience of related entitles by orders of magnitude.

Comparing Dates in Linq To SQL

2008-08-22T06:25:28Z

If you want to compare dates in Linq to SQL query, you are free to use normal operators available in C# and Linq to SQL provider will translate the date comparison into appropriate SQL which makes uses of date functions available on SQL server. To demonstrate the usage of Date functions let's look at an example.

In the above example, I am getting an instance of Date Time object by parsing date from a string. I have an event table which has an event date that defines when the event will happen. To find an event that matches the date we have selected, I am comparing my date in the lambda expression to the date available on the Event Timing table. Linq to SQL is smart enough to convert the expression into appropriate SQL that SQL server can execute. Above query generates the follow SQL query to be executed by the database.

Above SQL query is the translation of our comparison of dates in Linq query. From the query, we can assert that this comparison was by no means complicated. It simply compares two fields ensuring that the parameter passed in to the SQL query is of Datetime data type.

But sometimes we don't really care about the time portion of the date and all we care is about finding an item in the database that matches a specific date with no time. Well, if you are using SQL server 2008, than I would highly recommend that you make use of the new data type called date which only captures the date portion of a date time. If SQL server 2008 is not an option, you can do your regular comparison of just the date portion in your query and Linq to SQL provider would gladly convert your query into appropriate SQL translation that only compares the date portion of a date. Below is an example of a query where I am only comparing date portions of the query.

In the above example, I make use of Date property available on a Date instance to just get the date portion of my selection date. To ensure that both sides of query make use of just the date portion, in my Linq query, I am getting just the date portion from my event date and comparing that to my selected date. When I run the above query, I get the following SQL query.

In the above SQL query, we are just grabbing the date portion from EventDate column by making use of convert operator passing in date as one of parameter. This confirms that when we make use of Date property on the Datetime object as part of our Linq query, Linq provider will translate the query which will make use of Convert function to convert Datetime instance to just date. It might be surprising that value portion of our date still consists of date and time. This is because Date property on Datetime object return the date and a default time and since the convert function in SQL server also returns a date with default time, the criteria matches based on dates with default time.

Many To Many Mappings in Entity Framework

2008-08-21T04:26:58Z

So far in my previous blog postings, I have discovered goodness with linq to SQL as I travel the path of migrating from linq to SQL to entity framework. This is not to deny that entity framework also has plus points which cannot be ignored. Among them is support for many to many relationship. Many to Many relationship is a concept that is very common in most OR mappers so one would wonder why it did not make it into the service pack release for sp1 for linq to SQL. I am sure Danny Simmons will have a better answer to this question than me.

Many to Many tables are used in the database when both sides of the tables has many relationship to the other side of the table. It is easier to explain the concept through an example. For instance if you have Employees and Projects. An employee can be part of many projects and in a single project there can be many employees participating. A scenario like this requires a third table which can include relationship from both tables. That table can be called ProjectAssignment. Below shows how the database diagram looks like.

In the above example, I have a table called Employees, which has all the employees in the database. Another table Projects which lists all the projects in the database. Than I have the connecting table called ProjectAssignment which defines how each employee is tied to a project. Notice in the ProjectAssignment, I have marked combination of EmployeeId and ProjectId to be the primary key. because you cant have the same employee be repeated for a given project. When I run the entity framework designer on the database, I end up with the following entity framework diagram.

When you look at the above generated entity diagram, you must be wondering where did my third table ProjectAssignment go? Well you don't need it. ProjectAssignment was a table in the database only used for normalization because database tables do not directly support the concept of many to many tables and therefore the need to bring in the third table arose. However in the OO world, many to many relationships can be mapped easily between objects using navigation relations where you have reference to Employee object, you can use Projects property to get access to all the projects an employee is part of. Similarly Project entity exposes an entity set called Employees which you can use to traverse to get a collection of all the employees participating in the project. Following code shows how to achieve that in the code.

In the above example, I am accessing the projects collection of the employee to find out the total projects an employee belongs to. Same is the case with getting all the employees that are part of the project by accessing the employees entity set available on project entity.

So what are the use cases that actually do not turn into many to many relationship in the entity diagram? In cases where many to many table is not only used for relationship, but also carries the responsibility of capturing data specific to that relationship. For example, if we we decide that for each employee in a specific project, we need track how much percentage of the project has been completed and also how much money an employee has spent on that project. The best place to keep track of these additional attributes would be the ProjectAssignment table because it is a table that manages the relation of an employee to a project. The screen shot below shows how the database diagram looks like after adding additional attributes.

Above screen shot shows 2 more new columns, Amount Spent and PerComplete that we are tracking inside of ProjectAssignment table for each employee on a specific project. Since these attributes are an important attributes to a relationship, it is no longer converted to many to many tables relation. Converting this relation to simply many to many relationship would cause it to loose attributes that we are tracking for that relation. Hence entity framework keeps this table intact in the entity framework designer and if you have to access the projects for an employee, you have to travel your way through the ProjectAssignment table to reach either projects or employee table. Code below shows the how to get all projects for an employee and all employees for a project.

In the example above, to access all the projects for an employee, I have to navigate to all the ProjectAssignment and for each ProjectAssignment access its Project. I do the similar navigation when I have to get access to all the employees for a particular project.

Entity Framework Does not support object comparison in Queries

2008-08-20T13:55:41Z

As I continue to migrate my linq to SQL code over to entity framework, I am discovering more constraints that I never faced working with linq to SQL. For instance in one of my linq query, I was able to do object comparison to apply filter to queries. For example, If you wanted to retrieve customers who are in the city of London and also customer that you currently hold in your hand and find out the count of customers that meet this criteria or probably find out how much total sales those customers have given us, you probably want to perform this query on the database. In the past I could apply object comparison in my query rather than applying comparisons based on column values. You could actually use column values to dictate your filters, but sometimes being explicit on how you are applying a filter puts noise in your code as compared to just saying make sure this customer is also part of the filter.

Below is an example of code, that demonstrates running a query to get the count of customers who are in the city of London or customer filtered using object comparison.

In the above example, I am first retrieving a customer based on primary key column. In the next query, I am use the object that I obtained from my previous query to filter the customers to only customers that are in City of London or matches the customer that has the same object reference as the reference passed in the query. Although it may seem that you are doing object comparison but in reality linq to SQL transforms the object comparison to filter being applied on database based on primary key. You can confirm this behavior by looking at the SQL query that was generated. Interesting point to consider is, the entire query got execute on the database including the count operation and filter based on primary key column.

The concept of object comparison does not work in entity framework. In fact applying this concept will give you a runtime error because entity framework cannot transform object comparison to filter being applied on the database. Below code shows the same example being written in entity framework which causes runtime exception.

When you run the above piece of code, you will get a runtime error saying Unable to create a constant value but if you get rid of object comparison in the above entity query, you will not get this error.

Different Implementation of Cast in Linq To SQL And Entity Framework

2008-08-18T04:58:11Z

To set the stage of what I am going to blog about, I would recommend that you read by earlier blog posting here where I have talked about how the cast operator works. In short when you use cast operator with Linq to SQL to fetch a particular concrete type, you end up making a call to the database that fetches all records and than cast is performed in memory to cast the objects to the type passed in as a generic type. If the object cannot be cast to the correct type, the object is assigned a null value. The implementation is very similar to as operator in C# where if the object cannot be cast to right type, the reference of the object is set to null. Important point to understand is, even though your Linq query may not give the results you desire, but the query wont crash. However if you were to write the same query in Linq to entities or entity framework, you get a runtime exception stating that a cast is not valid. Below is an example that shows the behavior?

Running the above code with entity framework throws an exception.

Unable to cast the type Employee to type HourlyEmployee. LINQ to Entities only supports casting Entity

When I run the same piece of the code in Linq to SQL, I don't get exception but I do get 2 items in my collection, 1 being Hourly Employee and second is null because that employee is a Salaried Worker.

I am not sure which implementation is correct but I do think that which ever implementation is correct should be correct in both providers so that at the very least when we are migrating code from Linq to SQL to entity framework, implementation does not change and cause weird errors at runtime.

Entity Framework does not support Custom Properties in Queries

2008-08-17T20:45:10Z

As I am starting to learn more and more, I think linq to sql first release is much more mature that v1 release of entity framework. For instance, in linq to sql, if I would create custom property in my partial class, I could actually use that custom property in the projection of my query. One of the obvious reasons for creating custom property is to reuse code functionality such as creating a property called Name which combines, FirstName and LastName. You really don't to write that code all over you class diagram. Exposing the function as a property and being able to use that property in a projection is a valuable functionality added to the framework. Below is the code that shows using custom property in a projection of a query.

In the above example, I am using the custom property Name available on Employee partial class to project the output in an anonymous type.

Something like, is currently not supported by entity framework. When I run code similar to to above in entity framework, I get the following exception.

In the above code, I am making use of custom property Name with entity framework and I am greeted with an exception stating that Name being a custom property is not supported.

Single Table Inheritance in Entity Framework

2008-08-16T23:42:34Z

In this blog posting, I will do a walk through of how to apply single table inheritance using entity framework. Entity framework supports 3 different models of inheritance.

Table Per Hierarchy (Single Table Inheritance)
Table Per Type
Table Per Concrete Class

Of all these supported inheritance models, the most simplest and easiest to implement is Table Per Hierarchy (Single Table Inheritance). To implement this inheritance, you store all concrete types in one table. In Entity framework to identity a row as a specific concrete type, you define a discriminator column which identities which concrete type a specific row gets mapped to. From a usability point, I have found Single table model to be very easy to get started. However from the database perspective, the model doesn't seem to favor a clean approach. The reason is, you are storing all different concrete types in a single table. Some concrete types would need certain columns where as others won't. To accomplish flexibility at the table level, you have to mark all columns that are specific to their concrete implementation as allow nulls. Some database developers may find this approach not a good solution because it does not efficient use of disk space. Let's walk through an example to see how we can use the entity framework designer to apply single table inheritance.

To demonstrate the example, I will create a simple table called Employees. In the employees table we will capture two kinds of Employee, Hourly Worker and Salaried Employee. For both employees, we would like to know about their Name, Phone,Email. For Hourly Employee, we also want to capture their hourly rate and the number of Hours worked. For salaried employee, we would like to know their Salary.

The above employee schema shows that we have Name, Phone and Email as required because that is required for both types for Employees. However Salary, Rate, Hours are not required as they are specific fields for each concrete type. The last column type is the discriminator column that identifies what type of row it is; is it Hourly Employee or Salaried Employee?

We will start with adding a new Ado.net Entity data model.

2. Select the database and save the connection string to app.config file.

3. Select the Employee table.

After clicking finish, you end up with a single table Employee on the Entity framework designer. Now we will add two more entities that inherit from Employee entity; Hourly Employee and Salaried Employee.

4. Add Hourly Employee to the designer, inheriting from Employee class.

4. Add Salaried Employee to the designer, inheriting from Employee class.

5. After creating both concrete classes your entity diagram should look like this.

From the above diagram, you will notice that Salary, Rate and Hours are not attributes specific to Employee class. So let's move those properties to their respective class by cutting and pasting the properties to each of their classes.

Now that we have our properties in their respective classes, we can map Salary property on Salaried Employee class to Salary column in Employee table. We will also specify how Salaried Employee will map to Salary table by using condition column.

In the above example, I am setting a condition that when Type column on the Employee table has a value of SE (Salaried Employee), it should be mapped to Salaried Employee entity. Additionally I am also mapping Salary property to Salary column in the Employee table.

5. Next we will map Hourly Employee class Employee table using the discriminator column and mapping additional columns specific to Hourly Employee.

In the above diagram, I am setting a condition of Hourly Employee where type is equal to HE. Furthermore, I am also mapping additional fields specific to Hourly Employee class.

At this point we are not done because if you try to compile the project you will get build errors and those build errors would be something in the line of Type property not being mapped. The error is not very clear but in order to clear this error you have to remove the Type column from the base class because the column that you are using as discriminator column cannot be mapped.

After removing the Type column from Employee base class, you are still not done. If you try to compile the project, you will get the error below.

Once again the error is very vague and does not explain how to overcome this error. What it means is, we have mapped our concrete classes, Hourly Employee and Salaried Employee using the Type column attribute but we have not identified how to map the base Employee class. Either we can map the Employee class to a specific value in Type column but in my case, Employee class is really a base class for both Hourly and Salaried Employee and it does not map to any specific implementation. Therefore I can mark my Employee class as an abstract class to get rid of compile time error as shown below.

Once we mark the Employee class as abstract, the error goes away because we are explicitly stating that Employee class cannot be instantiated therefore it cannot be mapped to any concrete implementation.

Now all this required is to write a query that fetches Hourly and Salaried Employee as shown below.

In the above code, to access the Hourly Employee, I am making use of OfType operator specifying Hourly Employee as a generic type to retrieve only Hourly Employee. To fetch the salaried employee, I pass in Salaried Employee as my generic type. Output below shows the results of running the query.

Changing Namespaces in Entity Framework

2008-08-16T03:10:39Z

If you are like me, migrating to entity framework from linq to SQL, I must say you have been spoiled. What seemed to be very simple has gotten much more complex. I find that Entity framework solved my persistence ignorance problem but brought me much more complicated problems to deal with. Here is one that took ages to find out

In linq to SQL, I can not only control the namespace for datacontext but also the code generated classes for entities generated by linq to SQL. Bellow is the snap shot that shows where I can make these changes.

In the above screen shot, you can see that I am given two options to namespace generation. First, I get to specify which namespace my datacontext gets generated in. Second to avoid name collision, I am also given the opportunity to specify where my code generated entities are created. The option of Entity Namespace can be used to specify the namespace where entities are generated. On changing that you can also confirm that linq to SQL designer performed what you told it do by going to the designer.cs file. Below example shows the preview.

When using entity framework, you get only one option for changing the namespace and that is changing the namespace for entity datamodel. Not sure what namespace changes it makes because when I change the value for the namespace, I still saw my NorthWindEntityDataContext in the same namespace and also my code generated entities in the default namespace. One change I noticed after changing the namespace is, some of attributes defined in the code generated classes were now pointing to my new namespace declared but I don't know how does that help me much because entities are still in the same namespace.

Below is an example that shows this behavior.

After struggling for half an hour, I finally decided to right click the edmx file and sure enough i was greeted with another namespace change that actually allowed me to define which namespace my CLR objects be generated in.

Hopefully some of you will save sometime if you cant see an option for changing the namespaces for code generated entities.

MultipleActiveResultSets in EntityFramework

2008-08-14T07:03:15Z

MultipleActiveResultSets was a feature introduced in the connectionstring with ado.net 2.0. In prior versions of ado.net, you can only execute a single query at a time on a particular open connection. When you set MultipleActiveResultSets to true, you can execute multiple commands on a single open connection, resulting in better performance. It's interesting that when you generate your linq to SQL classes, by default it does not make use of MultipleActiveResultSets in the connectionstring. However when you generate entities using entity framework, the connectionstring added to web.config or app.config has explicit setting for MultipleActiveResultSets = true to enable the feature. If you set MultipleActiveResultSets to false in the connectionstring most of the queries in the entity framework fail. It appears that you have to set MultipleActiveResultSets to true in the connectionstring if you want to use EntityFramework.

Lazy Loading Entities in EntityFramework

2008-08-14T06:49:06Z

In my previous blog posting we learned how to eagerly load entities in entity framework. Eager loading causes lots of entities to be loaded ahead of time which may not be required and feasible in all scenarios. In those cases you can call Load Method on an entity or collection of entities to lazy load entities on demand. This ensures that you do not bring lots of data from database and only loading entities when you need them. The problem with this approach is, your querying process would be very chatty and you would end up making too many database calls. For example if you have collection of customers and you want to lazy load customer's address, than depending on the number of customers you have in your collection, linq to entities will make that many number of calls to get addresses for all customers in the list.

So we basically covered two scenarios. The first scenario where you want to eagerly load entities such as Orders for customer, second scenario where you lazy load addresses for a customer only when you need them. What if you have a situation that comes in a middle where you want to lazy load an immediate collection but when you lazy load the immediate collection, you want to eagerly load its nested collection. For example you want to lazy load Orders for a customer but when you are loading the Orders, you want to immediately Load all the OrderDetails for those Orders. In a situation like this, you can get a reference to CreateSourceQuery method on the lazy loaded child collection. CreateSourceQuery method contains the query which would be sent to fetch that particular lazy loaded entity. Since CreateSourceQuery returns an ObjectQuery, you can call Include method to specify additional sub collections that you want loaded with the lazy loaded collections.

In the example below,I am lazy loading Orders for a customer. For each Order, I am eagerly fetching its OrderDetails.

In the above example, I could have called cust.Orders.Load to lazy load my Orders for the customer. However this would not give me an opportunity to load its OrderDetails. To ensure I can load my OrderDetails along with the Order, I am getting a reference to CreateSourceQuery method on the Orders collection and than appending my includes that tells what other tables include when loading the Orders Collection. In the case of CustomerDemographics, I had no need for loading additional tables, so I am making use of the Load option to tell entity framework to load CustomerDemographics entities. If I do not call Load, you do not get any errors but the collection count returns a value of 0. One could argue that collection count of 0 means customer did not have any demographics but in this case they do have a value of 1. The way you can differentiate between a true value of 0 and 0 being not loaded is by checking the IsLoaded property on a collection to see if the collection was loaded from the database or not.

Eager Loading entities in Entity Framework

2008-08-14T05:39:47Z

As I am starting to move forward with entity framework on all my projects, I am discovering new ways of writing queries that works with entity framework as compared to in the past with Linq To SQL.

In entity framework, if an entity has another entity or child collections based on 1 to 1 mapping or 1 to many or many to many mapping, by default those associations do not get loaded. Everything in entity framework has to be explicit. If you want to load a specific entity or entity set, you have to explicitly tell entity framework by calling Include method passing in the name of the association or navigation property that you want loaded. Below is an example that shows eagerly loading 1 to many relationship Orders for a customer.

In the above code, I am using Include method,passing in the name of the association property that I want loaded immediately when I am retrieving a customer. Include method is pretty flexible if you want to load hierarchy any level deep. For example if I have a customer and I want to load Customer, its Orders and also the OrderDetails for those orders, you dont have to make separate include calls for loading Orders and OrderDetails. Simply traversing the hierarchy inside the include method would cause the both entities to be immediately loaded with the customer. Also there is no limitation on the number of times you can call Include with an object query. so you can basically load a many to many entity along with two 1 to many relationships. Below is an example that demonstrates the usage.

In the above example, I am eagerly loading 3 entities. First I am traversing the hierarchy for Order and OrderDetails to load both entities immediately. I am also calling Include again to load CustomerDemographics entities which happens to be a many to many relationship.

Migrating from Linq To SQL to Linq To Entities

2008-08-14T04:08:13Z

I am not sure if it is a sound idea to move all the stuff over to Linq to entities from Linq to SQL. However this current portion of the project that I had started few days ago felt like a really good place to test with Linq to entities. So quickly migrated over my code from Linq to SQL to Linq to entities. Surprisingly except for changing few namespaces and datacontext rest of my Linq queries just complied fine against Linq to entities. After compiling the code, I went ahead and tried to run the application and I met my first exception.

The method 'Single' is not supported by LINQ to Entities. Consider using the method the Method first.

I have been using Single operator in Linq to SQL for ages to search for objects based on primary key value. It always worked I am not sure why Linq to entities does not support querying based on single operator. The error is pretty self explanatory and that using the first operator solves the problem. Just a common gotcha to be aware that if you have any existing Linq to SQL queries that you are migrating to Linq to entities, for the most part they will compile fine but sometimes Linq to entities may not have the implementation available by the provider.

Constraints With LoadWith when Loading multiple 1:n relationships

2008-08-11T06:35:20Z

This problem totally came as a surprise when one of our pages in production was taking too long time to load. Obviously to trouble shoot the problem, I opened up SQL profiler, started looking at the various SQL statements being sent to the database. I was surprised to see that LoadWith operator was not working as advertised when eager loading more than one, one to many relationship. To demonstrate the problem, let's start with a bare bone example where I eager load Orders for the customers in the city of London.

In the above example, I am using LoadWith operator to eagerly load Order for my customer. Looking at the query sent to the database, you can confirm that there was not extra query sent to the database to fetch the orders, it came along with the customers.

When I change the DataLoadOptions slightly to also bring OrderDetails, for each Order when you are loading Customer, the query plan changes radically. Linq to SQL first brings all the customers that match the criteria and there are no other joins added to the query to attempt to bring orders and OrderDetails for all those customers. When you iterate through the customer, for each customer in the list, Linq to SQL makes a separate database call to fetch all the Orders for that customer bringing along OrderDetails tied to those orders. I am not sure what criteria Linq to SQL uses to determine which table it needs to bring individually and which tables needs to be brought together in one query. Example below shows separate database calls made for each customer to fetch its orders and order details.

In the above example, you can see that I have added another option in my DataLoadOptions to load OrderDetails for my Order as well. When we run the example above, a separate database call is made to fetch orders and OrderDetails for each Customer as shown by the SQL capture.

The obvious question is, this is not the advertised behavior of DataLoadOptions which is supposed to eagerly load all the Child Collections. In a side project that I am working on that uses NHibernate, I have not come across this issue. May be readers can shed some light on why this is happening and if someone has found a work around to this solution, please post the solution so that others can benefit.

One bad solution that I have done to fix the problem temporarily is, turn off deferred loading and run separate queries to fetch customers, orders and orderdetails in 3 database calls and assign their collections manually on the client side. This way I had more control of how many calls were being made. However the solution is not very clean but at the end of the day, it does the job.