July 2008 - Posts
Depending on the approach you take when writing linq query, you may be surprised that some of the operations are getting performed in memory instead of the call being translated to sql and executed on the database. When you apply aggregate operators on association relationship on an entity in the context of a query syntax, the aggregations is performed on the database. However the same syntax when executed outside of the query syntax would force the entire association relationship to be brought from the database and aggregate operation performed in memory. In figure 30, I have two different version of the same query that returns the customerId, Total number of orders they have placed and TotalAmount they have spent on orders so far. In the first query, I am using association relationship Orders available on customer to get the total orders placed. To get the total amount spent, I first used the association relationship Orders on the customer and for each order, I navigate to its order Details association to calculate the cost of each Order. Since I am using association relationship inside of a linq query, the entire query is converted to sql and send to the database for execution. Second query in Figure 30, uses the same association relationship on the customer entity to perform calculations. Since the calculations are not part of an existing query, linq to sql has to bring all the orders for the customer and for each order bring down all its OrderDetails to calculate the Sum and Count operation. Bringing all the Orders and OrderDetails for a customer is an expensive operation which need not to be performed if all you are want ing to do is get the count and sum. Those operations can easily be done on the database. As a developer, it is important to understand the tradeoffs and know which option may be better suited for your scenario. For instance if you already have the order and OrderDetails for a customer in memory, than it may be more efficient to perform these operations in memrory instead of making a database call to the server.
Figure 30: aggregate operators are applied on the database if used inside of a linq query.
AsQueryable is a method that allow the query to be converted to an instance of IQueryable. When you use AsQueryable operator on an existing query and apply further transformations such as applying a filter or specifying a sort order, those lambda statements are converted to Expression trees. Depending on the provider you are using, expression trees will be converted into the domain specific syntax and than executed. In the case of Linq to SQL provider, the expression tree would be converted to SQL and executed on SQL server. However if you use AsQueryable operator on a query that does not implement IQueryable<T> and only implements IEnumerable<T>, than any transformations that you apply on the query would automatically fall back on IEnumerable<T> specification. What this means is by tagging a query with AsQueryable you get benefits of both Linq to SQL and Linq to object implementation. If your existing query happens to implement IQueryable, the query is converted to SQL by the Linq to SQL provider, otherwise the query is executed in memory in the form IL code.
This offers an excellent benefits in real word scenarios where you have certain methods on an entity that return an IQueryable of T and some methods return List<T>. But then you have business rule filter that needs to be applied on all the collection regardless if the collection is returned as IQueryable of T or IEnumerable of T. From a performance stand point, you really want to leverage executing the business filter on the database if the collection implements IQueryable otherwise fall back to apply the business filter in memory using Linq to object implementation of delegates.
In Figure 23, I have a method called TopSellingProducts which returns an IQueryable of products. I am defining that all products which have been ordered more than 50 times is considered our top products. Since the TopSellingProducts method returns an IQueryable, I have not executed the query, I have simply declared the query of how to get top selling products. I have another method, called MostProfitableProducts that returns List of Products which have a UnitPrice greater than 60. I arbitrarily came up with this rule and stated that any products which have a UnitPrice of more than 60 dollars is considered profitable to the company. Looking further in figure 23, I have another method called DisplayProducts which is responsible for displaying Products. One of the business rules states that we can only display products that has either quantity greater than 0 or the product is not a discontinued product. To apply our business filter, I am sending the result of both TopSellingProducts and MostProfitableProducts to our filter method called FilterNonDisplyableProducts. FilterNonDisplayableMethods simply removes the products that violates our business rule discussed above. Notice that FilterNonDisplaybleProducts takes in an IEnumerable because this is the lowest common denominator that we can work and is implemented by all generic collection in one form or another. Before applying the business filter I am converting the IEnumerable of T collection passed in as a parameter To IQueryable of T using AsQueryable operator. We are doing this because we want the filter to be applied on the database for collections that implement IQueryable. If the collection does not implement IQueryable, the filter is applied in memory. This way you can apply the business rule to both IQueryable and IEnumerable and get the benefits of both worlds.
Figure below shows the SQL capture when I execute the code above. Based on SQL query send, we can confirm that our business filter was applied on the database for TopSelling Products because it was using IQueryable where as for the MostProfitableProducts Collection, the business filter was applied in memory because it implemented only IEnumerable of T.
Asp.net Ajax offers variety of ways to handle error that occur when the page is posted back asynchronously. Every time an error occurs in an asynchronous page request, script manager will raise an onasyncpostbackerror event. If you need to customize the error that get sent to the user, you can register with the event and set the a friendly error message on AsyncPostBackErrorMessage property available on ScriptManager. Code below shows an example where I am setting a friendly error message on the AsyncPostBackErrorMessage instead of displaying the exact error thrown on the server side code.
On the client side, you get a JavaScript error when server side code throws an exception on an async request. Notice the error includes our friendly exception that we set on the ScriptManager property.
ScriptManager also honors error setting defined in the web.config of how to handle errors. If you have defined customerror section and have the mode set to on than you will be redirected to the custom error page defined by defaultRedirect when an error occurs in an async page request. For some reason, if you decide that for errors that happen asynchronously, you do not want it handled by a custom error page, you can turn off the setting explicitly on the ScriptManager by setting AllowCustomRedirects to false. By default it is set to true so if an error occurs, ScriptManager would honor the setting defined inside of web.config file.
I have been using TODO comments in visual studio since vs 2003. It helps me keep track of all the items that I eventually want to get to but don't have the time to do it on time to meet my release date. You can access the todo list from the view menu and clicking Task List. One of problems I had in the past with todo list was, it only showed todo tasks for files that are opened in visual studio. In a big project, it is not feasible to open all the files to see all the the todo tasks for the entire solution. In vs 2008 service pack 1, C# IDE team finally got around to fixing this issue. Now you get todo list for all the todo tasks in your solution regardless if the file is open or not.
In the past, to navigate around bunch of files opened in vs 2008, I tend to use Cntrl + Tab to reach a particular file. It certainly is a good option but when you have large number of files opened in the project, you end up spending enormous amount of time just tabbing through the list of files until you get to the one that you want to open. Recently I discovered that when you use Cntrl + Alt + down array key, a small navigation window pops up at the top. The windows allows you to navigate to all the files opened. The best part about this window that I really like is, you can actually start type in the name of the opened file that you want to reach to and visual studio will automatically highlight the file that matches the name you are typing. Below is a screen that demos the feature.
I am not confirmed but I think this keyboard shortcut is new add on with service pack 1.
I finally have to embrace Silverlight as my team decided to use silver in building part of our community site. I had been trying to avoid Silverlight since 1.0 version, feeling that my Ajax skills and asp.net concepts would be all I need to do my daily activities. But with the upcoming release of Silverlight which supports .net runtime, world is changing over. We no longer have to program in JavaScript and can be comfortable writing code in our domain specific language such as C# or Vb.net. Today, I decided to begin my journey to learn Silverlight. The best way to learn any technology is to create a hello world application. Below is a step by step tutorial to create a hello world application.
To get started go to File New Project and create a new Silverlight project.
On clicking ok you are asked if you would like to create a Web application to test the Silverlight application. You also have the option to just create a simple html page to host the Silverlight application. If you want more flexibility and easier debugger experience, you would be better of creating either a website or web application project. For this blog posting, I will choose creating a website project to host my Silverlight application as shown in the screenshot below.
On Clicking Ok you get two projects, one is the website project and other one is the Silverlight application. WebSite project references the Silverlight application and on building the Silverlight application, the Silverlight application gets copied over into the ClientBin folder of the WebSite project in the form of xap extension.
To get started with creating my hello world app, I modify my page.XML file in my Silverlight application as follows.
In the above screenshot, I have a simple grid which has 3 rows. By declaring 3 RowDefinations, I get a grid which has 3 rows. First row in the grid has a textbox. We are not specifying which row the textblock belongs to because by default first content belongs to first row in the grid. I am also assigning the name to the textbox, so I can reference the textbox in the code behind. TextBox is followed by a Button. I am registering a click event handler with the button so that when you click the button I am taking the text put in the textbox and setting it to the label that I have created in the third row of the grid. I am creating the label by declaring a TextBlock in the third row of the grid by setting Grid.Rows equal to 2. I am also creating some margins around the textblock following by horizontally and vertically centering the content in the center of the row of grid. Below figure shows the code behind that is wired to the click event of the button that gets the value from the textbox and assign it to label's text property.
When you run the application, and enter some text in the textbox and on clicking the Click Me button, the text is copied into our label. Below figure shows how our final output looks like.
Today, I was working with expression trees and had the need to combine two expression trees. It appears that when you try to combine two expression trees, it doesn't work. Compiler raises an an exception and does not allow combining two expression trees. Instead you need to convert expression tree into delegate by compiling the expression tree and using the compiled delegate with the existing expression tree to get the new expression tree. Here is an example that C# compiler does not allow.
In the above example, compiler raises an error when I try to merge two expression trees, complaining variable is used like a method. Error is not very meaningful but essentially if you compile the square expression tree into delegate and than use it with squareplus2 expression tree, you get a new expression tree. Here is an updated example of merging two expression trees together.
In the above code, you will notice that I am first compiling my square expression tree to a delegate and than passing in the n parameter expected by the delegate. Later I am getting the ToString version of squareplus2 expression tree which simply prints the expression tree invocation to the output screen. In order to execute my expression tree, I have to first compile it therefore I am compiling my new expression tree, squareplus2 and than passing in the value of 4 to get the value for the entire expression. From the result in the output screen, you can see that our new expression combines both expression to get the correct value.
As I have started to play more with expression trees, I decided to learn how to modify expression trees. Well expression trees cannot be modified because they are read only. In order accomplish modification, you have to create a brand new tree, copy the existing expressions that are still valid, replacing the expression that you want changed. Below is an example that demonstrates changing a constant value from 1 to 3.
In the above example, I have a simple expression that adds 6, divides by 2 and adds one. All we are trying to accomplish is change the value of 1 to 3. I am first grabbing the body of the entire expression by calling Body on the expression and casting it to BinaryExpression. A BinaryExpression has a left and right operand. Looking at the graphic view of the expression, you can notice that in order to get to the value of 1, we need to access right side of the expression and cast it to a node type of ConstantExpression. Based on the diagram, I am accessing the right side of the body expression, casting it to ConstantExpression and printing the value of the expression. However as I mentioned earlier, expressions are read only, therefore cannot be changed. When I try to assign new value compiler does not allow. Here is the modified version of the code that replaces the constant expression with new expression.
In the above example I am creating a new expression and grabbing the left portion of the existing expression and replacing the right portion with the new constant expression I have created. You can confirm that the new expression has a value of 3 from the expression tree output printed on the screen.
Just to get a little more complex, I am going to change the value of 6 in the expression tree to 4. Below is an example that illustrates how to achieve the change.
In the above example, in order to get to the value of 6 I have to do two lefts on the binary expressions. Further more I have to recreate all the expressions that are above the expression that I want changed and rebuild the entire tree. From the output, you can confirm that our value of 6 has been updated to new value of 4.
I made a blog post earlier, where I discussed how to use AssociateWith method on DataLoadOptions to sort child collections on SQL server before they are loaded into memory. This option is only available in linq to SQL. In entity framework, in order to accomplish sorting on child collection, you have to use CreateSourceQuery method. CreateSourceQuery returns an objectquery that gets converted to SQL and send to the database. So if you want your child collections to be loaded differently such as sorted in a different order or apply filter, than you have to get access to ObjectQuery instance that is responsible for loading the child subcollection and modify the query to add ordering before ObjectQuery gets executed. CreateSourceQuery query method is available on EntityCollection and EntityReference.
Below is an example that shows how to do that
var context = new NorthwindEntities();
var cus1 = context.Customers.Single(c => c.CustomerID == "ALFKI");
foreach (var order in cus1.Orders.CreateSourceQuery().OrderBy(o => o.ShipCity)
{
Console.WriteLine(order.ShipCity);
}
Thanks Danny for the help.
I have been working with OR mappers for more than 3 years and had the luxury to work with many different ones such as Wilson, NHibernate, Sonic and Linq. One of the constraints that I have encountered in most ormappers is, there is no clean way to define how to sort child collections based on certain column. For example if I have a customer instance in my hand and I want to get access to its Orders, I can simply navigate to Orders property of the customer. What if I want those orders to be sorted by ShipCity. Well in Linq queries you can apply OrderBy operator on Orders collection for the customer. But does that order by operation gets executed on SQL server. The answer is no. As soon as we access the Orders property of the customer, Linq to SQL fetches all the orders for the customer in memory. From there on any operations you perform will get executed in memory. Below is an example that confirms the behavior.
In the screen shot above, we can see that our order by clause has fixed the ordering issue on our child collection orders and our orders are sorted by ship city. However the ordering is performed in memory. As I mentioned earlier, as soon as we access Orders collection the orders are fetched from the database and brought in memory. You don't get a chance to give any hints to retrieve the orders from the database in a specific order such as ship city. However Linq to SQL offers DataLoadOptions which has AsscoiateWith method that takes in a generic type and allows you to put your sorting requirement in the form a lambda statement. Below is the code that shows how to perform ordering on child collections on the database server.
In the code above, I am creating an instance of DataLoadOptions and passing in the lambda expression to AssociateWith operator. According to msdn documentation you can use Associate with operator to filter any child collection. You can use AssociateWith operator to apply any kind of transformation that Linq to SQL query engine will allow which includes sorting the child collections as well. From the SQL capture, you can confirm that indeed the sorting got performed on SQL server and are results are printed in the correct sort order.
More Posts
Next page »