July 2008 - Posts
Today at work, I was given a requirement to randomize our featured products on the home page. Basically every time you reload the page, you get different set of products. The maximum number of random products you could display on home page must not be more than 20. I could have done the implementation in C# by bringing more products than I need and randomly picking up 20 from the list. Once I implemented the solution, my results were truly not randomized. I ended up with same records quite often because the randomizer function did not have enough products to randomize and plus I was bringing more data than I needed. Meaning to randomly pick 20 products, I had to bring 100 products and apply random function on there. After having done that I realized that if I were to do the randomizing on SQL server I truly would get a random behavior. SQL server has a function called NewID that always give you a random generated number. So if I can order my collection by the random generated Id, my top 20 collection of products would always give me random 20 products from products table. Here is the view and a function that returns a random Id.
In the code above, I have a random function which gets the random Id from the view called RandomView. I could have called NEWID from inside the function but SQL server was not allowing me to call the NEWID function from my function so I ended up creating a view and selecting from the view to get the randomId. Now that I have my function created, I can drag the function in the Linq to SQL designer and Order all my products based on this function and take the first 20 products to get my 20 random products that I have to display on the home page. Below is the Linq query that gives 20 random products from the database.
Notice in the above code, I am ordering my products by the randomId and than than taking the first 20 products. The Linq to SQL provider converts that into top 20 products ordering the products by randomId generated by the my function.
Just out of curiosity i decided to create expression trees from scratch instead of using lambda statements to get my expressions trees. In all my projects, I haven't really found any significant need to create expression trees from scratch. Most of the time lambda statements are sufficient for me to create any complex expressions. However to understand lambda expressions better, it think it is essential to comprehend, how compiler converts lambda statements to expression syntax. In the example below, I am going to create a very simple expression tree that takes two parameters, multiples both parameters and adds two it.
In the above example, I am first creating a constant expression of type integer. Than I am creating two parameter expressions that my final expression needs. Both parameter expressions are defined as integer type. The third parameter in Expression.Parameter represents the alias that you will use to refer to the parameter in the expression tree. Next I create my first expression which is simply a multiplication of the X and Y parameter. I obtain my final expression by adding the constant with multiplied expression. This is an example where I am combining two expression by using Add operand. Now in order to build my final expression I make use of Expression.Lambda static method that takes the body of the expression which we created earlier follow by the parameters needed by the expression trees.
From the output window, you can see that we have the same output which ever path we choose to create our lambda expressions from. However I think creating even a simple expression tree is a high learning curve. An example such as above which I think is fairly non trivial took me 45 minutes to write. I hope this small tutorial gave you in sight in how to build lambda expressions from scratch.
Linq to XML provides numerous ways of modifying XML which includes adding, updating and deleting nodes. I happen to work on an XML document from which I had to remove some xelments. Surprisingly I found numerous options that facilitate deleting of various xelements from XML loaded in memory. Depending on where you are in the XML tree, you might find one method easier than the other. Here is a simple example that illustrates various ways I discovered of removing xelements from XML loaded in memory.
In the above example, I have some XML elements nested inside of book element. I start of with deleting title element by first getting access to the title element by using element method. Once I have the instance of the element that I want to remove I simply call remove on the element to remove the element from the tree.
Next, I am deleting ISBN number by calling SetElementValue. Basically SetElementValue takes an element and the value for the element. If the element does not exists, it gets added. If element is present in the tree, the value gets updated. However if the value is set to null, the element gets removed from the tree.
In the last option, I am removing the totalchapters element by first getting a reference to the element and calling the replaceWith method passing null for the element parameter. ReplaceWith method takes an element that you want the existing element to be replaced with. Passing value of null indicates that you want to simply remove the existing element.
From the output, you can confirm that title, ISBN and totalchapters elements are removed from the book node and therefore not printed on the output screen.
I am sure people who do XML on a day to day basis would not find surprising but I think it was pretty cool to see that when I call ToString on my XML, I get back my entire XML with whitespace preserved and also all text is encoded for me automatically. I created my XML using .net 2.0 api and saw the same behavior so I guess this was not a new feature but definitely a good default option to encode your text before outputting it to the user. Here is simple example that illustrates this behavior.
As you can see from the above code, my title and author value have some html tags that gets encoded when i output the content to the console.
This problem actually troubled me for hours until I accidentally figured out how to get around it. What I learned is try to avoid impurities in Linq statements. For instance, if you lambda expression's filter is applied by indexing an array value, do not access the array directly inside the lambda value. Instead get the value from the array assign it to a variable and use the variable in the Linq to SQL filter statements. I am not sure why I have to declare a variable to avoid an impurity. But if you do not follow this technique, more than likely you would end up with runtime error in your Linq statement. Even if you get pass the runtime error, you would end up with a wrong filter value being applied on your lambda expressions. Let's take a look at an example to understand the problem I am trying to describe.
In the above example I grab the top 2 order for every city in my array list which happens to be London and New York. In order to accomplish that I am looping through my array of cities and for each city, I am creating a lambda expression filtered by city. On the next iteration of the loop I am going to the else statement and combining my existing Iqueryable of orders with the old one. Basically in terms of SQL, I am simply applying Union operator to union Order records for both cities. However if the query were to execute the sql generated has city filter that set to New York as shown in the diagram. We would have expected that first part of the query would have the filter for the city of London and next part of the query would have a filter of New York. However in this case both portions of the query had a filter of New York. This is the side effect of accessing cities array directly inside of a lambda expression. Since we accessed cities array directly, the last value the array had on the last iteration of the loop, is the value that Linq to sql uses for all the filters in the query.
In order to fix the issue simply grab the value from the array and store it in a variable and use that variable inside of the lambda expression. Here is the correct version of the query that applies the correct filter.
I spent nearly two hours trying to figure out what was wrong with my Linq to SQL query. Basically I was trying to calculate the sum of sales generated by each sales agent. In order to accomplish that I had to travel from employee table to Orders which had the employeeid linked to it. From the Order table I navigated to its order details and calculated the sum of Quantity ordered multiplied by Unit Price. The query works fine when each agent has put in an order in the database. But when there is no order record for an agent the sum operation performed on SQL server returns null. When Linq to SQL tries to assign null values to data types that is not defined as null, it throws an exception. Below is an example that demonstrates this issue.
In the above query, I am displaying the Employee's First name and for each employee getting its orders. For every order, I grab its order details and than flatten all order details for all orders for a particular customer using SelectMany operator. Once I have all the OrderDetails, I apply sum operator operator to get TotalRevenue generated by each employee. The above query causes exception because there are some employees that do not have any orders and Linq to SQL raises exception complaining that null value cannot be assigned to a member with type System.Decimal which is non-nullable. What this means is in our lambda expression od.UnitPrice and od.Quantity is defined as decimal and short where as employees that dont have orders and thus does not have any order details, the value for unit price and quantity is null. Since you cannot assign null value to a data type that is non-nullable, Linq to SQL throws an exception.
To solve the problem, simply do an explicit cast to nullable type and your query would execute fine. Here is the corrected version of the query written in two different ways. Both ways require an explicit cast to nullable type.
In the above example, I have two different queries that return the same result. I was simply trying to force Linq to SQL to generate two different query plans and sure enough modifying the query generates unique SQL queries but they both met the met same fate and caused exception unless you do a correct cast to nullable type.
In one of the project that I am working on, my client requires me to use WCF service to talk to Linq To SQL datacontext. The infrastructure guys would not open up port for me to access SQL server directly. Therefore I created a WCF project in my solution, added a reference to Business library and exposed my Linq to SQL entities using WCF service. By default you cannot expose your Linq to SQL entities, you have to mark SerializationMode on the datacontext to Unidirectional. Once you configure the datacontext for wcf serialization, the generated entities are marked with DataContract attribute and properties on the entity are marked with Datamember attribute. If Linq to SQL entity have child entities those also get serialized. However child entities do not get lazy loaded, you have to explicitly call LoadWith to immediately load child collections for them to be serialized. Here is a simply example that illustrates the behavior.
In the above screen shot, I am marking the serialization mode on the data context to Unidirectional which causes entity classes to be serialized using WCF service. In the above example, I also have a WCF service that exposes an operation called GetOrder. GetOrder method simply retrieves an order based on orderid passed. Since we want the OrderDetails to be also available on the client and serialized along with the Order, I am loading OrderDetails ahead of time.
In the above example, I am creating an instance of my wcf service and getting the order from the service. After retrieving the order, I am printing the orderid and the total of all the orderdetails for the order. But when I access the Customer property of the Order object I don't see any Customer property available, although my Linq to SQL entities had Customer property on my order object. For some reason, the Serialization mode does not take care of serializing entity refs.
I am still researching the problem. If anyone had seen this behavior and knows a workaround to the problem, please write a comment to let others know.
As I am starting to do work on both linq to SQL and linq to entities, I am finding lots of subtle differences on both implementations. Some implementations I would consider as being better over the other.
In Linq to SQL, if I have a customer object and want to get access to its orders collection, all I simply have to do is navigate to the Orders collection property and linq to SQL lazy loads the orders for that particular customer instance. But what happens if I already have orders in the memory and some of the orders do actually belong to the customer that is loaded. Although using primary keys, linq to SQL can associate those orders to the customer based on the mapping definition but it will not do so. However in entity framework, if you have loaded some orders from the database randomly based on some criteria and if there is a customer in memory whose orders are part of that randomly selected orders, than entity framework would automatically associate those orders to that customer with out you making a request for the Orders for that customer. It could be considered a plus and minus point. Plus because it automatically uses the associations of the customer with orders based on entity key to tie those orders to that customer. The minus is because those subset of orders in memory may not represent all the orders for that customer and that could be very confusing and hard to debug problems when the Orders Collection does not show all the orders for the customer. Here is an example that illustrates this issue.
In the above example, I am turning off deferred loading on linq to SQL datacontext. The reason I am doing is because when I apply the count operator on the Orders collection, I dont want linq to SQL to go and fetch the orders for the customer instance. In the linq to SQL portion of the code, I am first retrieving all the orders in the database and loading it up in the memory. I then go ahead and retrieve the ALFKI customer from the database and print its Orders. Based on the results on the output window of 0, you can surmise that linq to SQL did not associate the orders in the tracking service with customer based on the primary key value.
Next portion of my code does the same thing except using entity framework. I first retrieve the customer and than I am retrieving all the orders for the employee id of 4. Next I am printing the Orders for the ALFKI customer. By default we would expect a value of 0 based on how entity framework is implemented because in entity framework the child collections do not get loaded until you call Load method. However we still see 2 in the output window. The reason is, the orders we loaded earlier based on employeeid of 4, has 2 orders that belong to ALFKI customer. This result is misleading because there are actually 6 orders of ALFKI customer but only 2 of them are loaded in memory.
Once again I am comparing different behaviors in linq to SQL and entity framework. If you want to eagerly load child entities in Linq to SQL, you would use LoadWith operator available on DataLoadOptions. LoadWith method retrieves related objects for the main object. You have the option to call LoadWith method as many times as you need depending upon how many related objects you want to eagerly load. If you want to eagerly load related objects in entity framework, you you can call include method. Include takes a path which represents the name of the related entity that you want to load. In the path parameter you can also use traversal by using dot notations. For instance for a particular customer, if you want to load its Orders and for each order want to load its OrderDetails, instead of making of two Include calls you can call include with a traversal like cust.Include("Orders.OrderDetails"). This tells entity framework to load Orders and for each Order load its OrderDetails as well. As with linq to SQL, you also can make more than 1 calls to Include to load multiple entities at the same time. Below is an example that illustrates different usage of eager loading with entity framework and linq to SQL.
In the above example, I start off with showing how to eagerly load child collections with linq to SQL. I am using DataLoadOptions class and calling LoadWith method to load its Orders and for each order load its Order Details. I am also loading the addresses for the customer as well. Notice that I had to make use of LoadWith method 3 times but all my code is strongly type and I am not having to type in strings in the load with parameter. Instead I make use of lambda expressions to specify what sub collections to load.
Now coming back to entity framework, in order to eagerly load sub collections I am using Include operator. Notice in order to load order and its order detail, I am not calling Include operator several times. Instead I am traversing the entity path which tells entity framework that I want to load order and its order details. As I mentioned earlier, you have the option to call Include several times, therefore I am calling Include again to load addresses for the customer as well. Over all I think Include syntax is better than Linq's LoadWith except Include is not strongly type meaning my string does not evaluate until runtime and I dont get design time compilation which is offered by LoadWith operator in Linq to SQL.
Below is the output from the above query. The results from linq to SQL and entity framework is the same.
It's pretty interesting to see different implementations of lazy loading in both linq to SQL and entity framework. In linq to SQL, if you want to access the Orders collection of a particular customer, all you do is navigate to the Orders Collection Property and linq to SQL will run the SQL in the background and fetch the orders for that customer instance. The operation is pretty implicit. However in entity framework you have to explicitly tell that I intent to load the orders of the customer. The way you would do this is by calling Load on the Orders collection of the customer. If you forget to call load on the Orders Collection for the customer, you would end up with an empty collection of orders. This may be confusing and hard to troubleshoot bug and you would wonder why you have no orders for that customer when you could see orders in the database for the customer. Here is an example that illustrates this behavior.
In the above code, I am fetching the orders for alfki customer and printing it to the console. All that was required was simply to navigate to the Orders property and linq to SQL runs the query in the background and gets the orders for the customer. When I try to do the same thing with entity framework, it does not work. You can see that first time I try to print the orders for the customer using entity framework, I get a 0 count and that is because I have not explicitly told entity framework that I want orders for that customer. Next time around I call Orders.Load which ensures that orders for the customer are loaded and sure enough after that when I access the orders collection I get 6 Orders.
More Posts
« Previous page -
Next page »