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.
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.
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.
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.
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.
As I have mentioned in my previous blog postings, Linq provides a great querying model which hides the complexities of different domains such as Linq to objects, Linq to SQL and Linq to XML. However it is important to understand how a particular query behaves and runs on a particular domain.
When you use OfType operator with Linq to objects, you will only retrieve objects that are of that particular type. If the collection contains other object that are of different type, they will get filtered and Linq query would not throw exception. Same is true if you are using OfType operator in Linq to SQL query to query for a concrete type such as where Employee is an Hourly worker. In that case Linq to SQL query would use the discriminator column in the query send to the database. The discriminator column filter would ensure that you only bring rows that are of type HourlyEmployee. Below is an example that demonstrates this usage.
In the sample above, when applying the OfType operator to Linq to SQL query, Linq provider translated the query to a filter with discriminator column to bring only records that are of type hourly employee.
There is another operator in Linq called Cast. Cast operator works very similar to OfType operator but it will throw an exception if you try to cast a collection that does not have all the items of the type that you are casting to. However there is a slight peculiarity if you apply cast operator to Linq to SQL query. If you apply Cast operator in a Linq to SQL query, you will get all the items in the collection back but items that are not of the correct type would be marked as null. For instance if you have 4 employees in the database and only 2 employees are hourly employee, than running the Linq to SQL, you will get 4 employees back with no filter being applied to the query send to the database. However objects that are not HourlyEmployee would be set to null. This could be deceiving because if you were to do count on the collections, you will get a value of 4 but there are only 2 objects that are HourlyEmployee. Below is an example that demonstrates this usage.
In the above example, you can see that when we run the query with Cast operator, the results comes back with employee count of 4 because the cast operator does not have a query translation in Linq to SQL. However when data comes back, the cast operator gets applied on the in memory data and only objects that are truly Hourly Employee are set properly and other objects are marked as null.
If you have been using StringBuilder or String.Format to build strings, they provide nice syntax to replace placeholders such as {0}, {1} etc with the values passed in the second parameter to the method. Although I have been using this syntax to print and create customized displays, I still feel that there are sometimes harder to read because of the noise the method and placeholders add to the code. With the awareness of XML into the vb language, you can leverage embedded expression to build customized strings from XML by calling the Value property on the XML literal. Below is an example that demonstrates this usage.
In the above example, I am creating an XML literal called display. Display XElement has embedded expressions for product name, unit price and unitsinstock. All 3 literals create one big string which I can extract by calling Value property available on the XElement. Using XElement this way makes code more readable, allows embedding expressions and also preserves white spacing and carriage returns.
Comparing this example to the string.format that I have used later down the code, you can can see that I have to deal with placeholders which later get replaced making it harder to read because part of the code is down below. Also I have to manage my own spacing and carriage returns by explicitly using Environment.NewLine.
Personally if I had been doing vb, I would prefer XML syntax to create large strings because they make code more readable and intent becomes more clear. Hopefully C# will get this support soon.
With Vb, building an XML Document on the fly from a list in the database is very simple. Vb supports the concept of embedded expressions which is similar to asp.net concept of embedding code expressions in aspx page. This allows you to build you XML file as it should look when its opened. XML literals also allows you to replace sections of the file with data from anything that is IEnumerable. Example below builds an XML document from a list in the database.
In the example above, I am creating an XDocument object from list of products in categoryId of 1 which you can see as my query is filtering the products to return only products in category of 1. Next I create 3 elements inside my Products element tag. Notice I am using the class asp.net syntax of putting code expressions inside of each of 3 elements. This results in getting us a well formed XML that has all the products in an XML format we desire. Output below confirms the result.
This is a cool trick that I discovered in Vb when working with XML. Vb has full support for XML in the language so much so that you can type in XML write in the code and IDE would do color highlighting to differentiate it from the code in the page. It would also do correct indenting of the code so XML is more readable. By default when you type in XML in Vb.net code, it is inferred as XElement. Below example shows this behavior.
In the above XML code, when I hover over the element that is assigned the XML content, the inferred type is returned as XElement. If you need to return XDocument that complies with all the XML specification, all you have to do is put some processing instructions on top of the XML element and visual studio would fix the type to be XDocument. Example below shows this behavior.
In the above screen shot, I have added XML processing instructors which causes the type inference to change to XDocument which we can confirm by hover over customers variable.
If you have been making use of DataLoadOptions to eagerly load 1 to 1 entity or 1 to many entities such as child collections; you have to make sure that you apply all these operations before assigning the DataLoadOptions to the DataContext. For example, when you use LoadWith method to Load Oders for a customer, you need to ensure that you call LoadWith method before you assign DataLoadOptions variable to DataContext. Any changes you make to the DataLoadOptions after it has been assigned will result in runtime exception by the compiler. Example below shows the exception raised by the compiler in an correct usage of the datacontext.
Above example shows the exception raised by the compiler which clearly states that LoadWith operation is not permitted once DataLoadOptions is assigned to the DataContext.
More Posts
« Previous page -
Next page »