Sometimes you really, really, really want to see the SQL that LINQ generates when working with the Entity Framework. For instance, in a comment to a recent tip
, a reader mentioned that a LINQ to Entities query generated a SQL statement that joined more than 40 tables. The application's performance was, to say the least, disappointing. If you're not getting what you expect from your LINQ queries, being able to see the SQL statement can give you real clues in tracking down the problem, as it did with that column's reader.
There are a couple of free Visual Studio Add-ins that, among their other features, allow you to see the SQL statement for a LINQ query (The LINQ to Entity Query Visualizer, Entity Visualizer). However, developers have had trouble getting those add-ins up and running in some versions of Visual Studio (especially Visual Studio 2010). Fortunately, if all you want to do is see your SQL statement, you can do it in Visual Studio's debug mode using a little code in the Immediate Window.
The first step, of course, is have a program with a LINQ query as in this example:
Dim en As New northwndEntities
Dim res = From cust In en.Customers
If you cast the variable holding the output from the query (res, in this case) to Objects.ObjectQuery then you get access to the ObjectQuery's ToTraceString property. That property returns the SQL statement that will be issued by your Entity Framework Model. In Visual Basic, you'd type this in the Immediate window:
In C#, you'd type this:
For my sample query, the result looked like this in Visual Basic (the C# version includes escape characters for newline and carriage returns):
SELECT [Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax],
[Extent1].[Timestamp] AS [Timestamp]
FROM [dbo].[Customers] AS [Extent1]
Posted by Peter Vogel on 11/03/2011 at 1:16 PM4 comments
In a recent article
on using LINQ with Entity Framework, I recommended using Entity Framework's navigation properties in your LINQ statements instead of joins. The code is easier to write and the SQL generated for you will automatically include the necessary joins.
However, the following won't generate an SQL statement that joins the Orders table to the Employees table because the LINQ query doesn't reference the Employees objects:
Dim res = From o In nw.Orders
Where o.Freight > 0
For Each ord In res
EmpName = ord.Employee.FirstName
'…code using the employee name
In this case, the navigation properties aren't being used until the For…Each loop that follows the LINQ statement. The compiler isn't smart enough to spot that, so the SQL statement that's generated from the LINQ query won't include a join to the Employee table. Instead, Employee rows will probably be fetched one by one from the database as the code loops through the collection of Order objects retrieved through the LINQ query. This would be terrifically inefficient if, as this code does, every Order is going to have its related Employee object processed.
The solution is to give LINQ some advice about what you intend to do. In the same way that ADO.NET will process requests faster if you tell it more (for instance, if you know that only a single row will be retrieved, using ADO.NET's SingleResult option will give you better performance), LINQ will work faster for you if you tell it what you need. In this case, using the Include keyword tells LINQ that it should add a join to the SQL statement to fetch the related Employee rows:
Dim res = From o In nw.Orders.Include("Employees")
Where o.Freight > 0
As a developer, you'll have to make some decisions here. If, for instance, the loop in the previous example had an If statement that ensured that only some of the Employee records were going to be processed, it might make sense to omit the Include keyword. With the Include keyword gone, only the Employee rows that were actually needed in the loop would be fetched. You may need to run some tests to see if using Include makes your application run faster or slower. Fortunately, as with SQL, switching between the two versions of the LINQ statement is easy to do (delete or add the Includes), so it's relatively easy to do some time trials and see what gives you the best performance.
Posted on 10/26/2011 at 1:16 PM5 comments
The ASP.NET DataViews, when connected to a DataSource, generate all the buttons a user needs to edit, update and delete the data that the DataView displays (assuming that you enable edits, updates and deletes).
But what if you want to add some additional functionality to the form? If, for instance, you want the user to be able to click a button to run a credit check on the currently displayed customer? Or, for some change that requires coordinating changes to several values, you want to give the user the ability to implement the update with a single button click? Or, by clicking a button, delete not the currently displayed record but some other, related set of records (i.e. "Delete all orders for this customer")? You can put that code where it belongs -- associated with the DataView -- rather than scattered through several Click events by using the FormView's ItemCommand method.
All you have to do is drop a Button of your own onto the form and set the Button's CommandName property to some string. When the user clicks on any Button on the DataView, the DataView fires its ItemCommand event. The e parameter to that event has a property called CommandName that's automatically set to whatever was in the CommandName property of the button that triggered the event. You can use that value to check to see which Button was clicked and execute your code. Typical ItemCommand code will look like this:
Select Case e.CommandArgument
'CommandArguments for other buttons
This event has two other properties useful in this scenario. The CommandArgument property contains the value of the CommandArgument property on the Button the user clicked. You can store data in the CommandArgument that will be passed to the ItemCommand event when the Button is clicked. Less useful is the CommandSource, which points at the Button that fired the event (the sender parameter passed to the event points to the DataView).
And, because the ItemCommand event fires for every Button on the page and fires before any of the events triggered by that button, the ItemCommand event lets you preview all other Button clicks on the page. This allows you to use the ItemCommand event as a central point-of-control for every Button on the DataView.
Posted by Peter Vogel on 10/24/2011 at 1:16 PM2 comments
In my recent review
of Document! X 2011, a tool for generating MSDN-style documentation for your projects by scanning your assemblies, a reader pointed out that you can achieve many of the same results by using Ghost Doc and Sandcastle. I've discussed Ghost Doc
(a free tool for generating XML comments in your source code) before. Both Document! X and Sandcastle will incorporate your XML comments into the documentation they generate through scanning your assemblies.
Sandcastle is a set of command-line utilities that's free from Microsoft. Sandcastle generates the inputs to Microsoft's various Help compilers, which you'll also need to install. In addition, using Sandcastle would be painful were it not for a free GUI, the Sandcastle Help File Builder. But don't panic -- you can download a Sandcastle package from CodePlex that includes a great "guided installation" wizard. The wizard will help ensure that you get everything you need: Sandcastle, the GUI, and the necessary Help compilers. Just make sure that you download from the CodePlex site -- a simple Google search can lead you to a Sandcastle site with outdated links.
Using Sandcastle with the GUI is easy: Select Sandcastle from the Windows Start menu, create a new project, and add to the Sandcastle project the assemblies you want to document (you can also add "reference" assemblies, which are not documented but which your documentation refers to). Once you've added all your assemblies, it's just a matter of clicking the Build button and waiting for your Help files to be generated. After the files are generated, you can view the resulting output from the GUI's view menu.
There are several plug-ins for Sandcastle. For instance, the Wildcard References plug-in allows you to add as references to your project whatever assemblies in a folder (and optionally, its subfolders) meet a filespec. Sandcastle also includes a tool for gathering up the XML comments from ASP.NET Web Applications and Websites (since ASP.NET projects may have multiple DLLs) so that they can be incorporated into your documentation.
Sandcastle doesn't integrate with Visual Studio 2010 or your project's file structure, though there's an alpha release of a package that has begun adding that feature. The package also has the odd limitation. For instance, you can't add a reference to a COM DLL through the GUI.
Does Sandcastle do everything Document! X does? No. Currently, Sandcastle lacks Document! X's integration with Visual Studio, doesn't support documenting databases or Ajax components, and lacks Document! X's source control features, among other options. However, if what you want to do is generate MSDN-style documentation for your assemblies, Sandcastle will do that -- and the price is right. It's entirely possible that Sandcastle will meet your current needs for documenting your applications and should you, eventually, need to do something that Sandcastle doesn't support, you can purchase a more comprehensive package.
Posted by Peter Vogel on 10/19/2011 at 1:16 PM0 comments
Through the WebClient and AssemblyPart.Load method, Silverlight lets you download DLLs dynamically at runtime from the server to the client. This lets you defer dragging the DLL to the client until you know you need it.
To use the DLL you'll download at runtime, you still need to add a reference to the DLL in order to get IntelliSense support at design time (and also to get your application to compile). But because you're going to download the class through code at runtime, you don't need to include that DLL in your project. So, after adding a reference to the DLL, you can set its Copy Local to False.
Using the DLL in your Silverlight application is a little awkward. While you instantiate the class with the New keyword, as usual, you must do it in a dedicated method decorated with MethodImpl attribute, passing the NoInlining parameter. Something like this, for instance:
Private Function CreateMyObject() As Object
Return New MyDynamicClass
But you can't actually use the resulting object in that method. Instead, to make Silverlight's type checking happy, you need to defer using that class to a completely separate method:
Private Sub UseMyObject(dc As Object)
Dim dc As MyDynamicClass = CType(dc, MyDynamicClass)
Attempting to instantiate and use the class in the same method will just generate some bewildering error messages.
Posted by Peter Vogel on 09/28/2011 at 1:16 PM2 comments
Retrieving data from the GridView can involve passing integer values to the GridView's Rows and Cells properties—resulting in code that's not only hard to read but brittle: it will break as soon as you add, remove, or reorder the columns in the GridView. But there's an easy way to pull out data if you use the GridView's SelectedValue property. You've probably used the SelectedValue property with a listing control (e.g. the DropDownList, the RadioButtonList).
The issue with SelectedValue is that it returns a single value. That's not an issue with a listing control because each item has only one value. With a GridView, however, the user selects a whole row; so typically, there are multiple values returned. To take advantage of the GridView's SelectedValue property, you need to set the GridView's DataKeyNames property to specify which field will have its value returned in the SelectedValue property.
The DataKeyNames property accepts a string of comma-delimited field names (presumably, the field(s) that make up the primary key for the rows displayed in the GridView). The DataKeyNames property performs multiple functions, but as far as SelectedValue is concerned, it's the first field you specify in DataKeyNames that matters: that field's value turns up in the SelectedValue property when the user selects a row in the GridView. So, if there's some column that you typically need to retrieve from the GridView, make sure that that's column's field is the first one in the list in the DataKeyNames list. Once you've done that, you can pull that field's value from the SelectedValue property when the user selects a row (I've assumed that you've enabled selection from the GridView's smart tag).
When using the SelectedValue property, you should always check that a row's been selected before attempting to use the property. Typical code for working with SelectedValue looks like this:
Dim res As Object
If Me.CustomerGrid.SelectedRow IsNot Nothing Then
res = Me.CustomerGrid.SelectedValue
If you need more than just one value, you can use the GridView's SelectedDataKey property. The Values property on the SelectedDataKey, for instance, returns a Dictionary of all the fields you specified in the DataKeyNames property. You can retrieve the values of any of those fields by position or by name (as this example does):
res = Me.CustomerGrid.SelectedDataKey.Values.Item("CustomerID")
Posted by Peter Vogel on 09/09/2011 at 1:16 PM1 comments
Generally speaking, developers hate nulls. While an integral part of relational database theory (where they represent an unknown value), eventually nulls get passed to some variable that won’t accept them and your code blows up. Coalesce operators allow you to check for nulls and return a default value when one is found—and do it in a single line of code. In Visual Basic, the coalesce operator is If(), in C#, it’s ??.
This example checks to see if a nullable integer value is, in fact, set to null and, if it's not, returns datatypes's minimum value:
Visual Basic version:
Dim x As Integer?
x = Nothing
Dim y As Integer
y = If(x, Integer.MinValue)
x = null;
y = x ?? int.MinValue;
In some ways, you can think of this as providing a default value when a null value is provided.
Posted by Peter Vogel on 09/08/2011 at 1:16 PM3 comments
YouTrack is JetBrains' Web-based issue-tracking system. The latest version supports customizable workflows (using the free workflow editor) and better support for mobile devices, among other goodies. For developers who hate taking their hands off the keyboard, YouTrack supports both a command language and a search language that allow you to bypass the UI and just do what you want. YouTrack doesn't integrate with Visual Studio, but you can submit issues from e-mail (and also get notifications through Jabber, the open source instant messaging standard).
This is a full-featured issue tracker and (the best part) the only limitation on the free version is that you can't have more than 10 users.
If you're already using some other tool to track your "issues", YouTrack will import existing data from a variety of sources, including several of the popular issue-tracking tools. If you don't want to support YouTrack locally, JetBrains also has a hosted version (you can try it out for free until the Fall of 2011). Based on my experience, though, installation is a snap, so why bother?
Posted by Peter Vogel on 09/06/2011 at 1:16 PM0 comments
When you have a method that could do more than one thing, you have two ways to implement the method: Enclose the different processes in If…Then or Select…Case statements, or implement the strategy pattern. With the strategy pattern, you pass to the method not just data but also the processing. As a result, the method’s code loses all those ugly logic statements, making it easier to test and maintain. One way to pass processing to a method—and, probably, the simplest—is to pass a lambda expression.
To create a method that accepts a lambda expression, you define a parameter using the Func data type. When defining a Func, you pass parameters to the Func declaration that specify the input parameters, and return type of the function that the lambda expression defines. A Func data type can accept up to 17 parameters: The last parameter specifies the return type, while the previous parameters specify the input parameters. For instance, this declaration specifies an expression that accepts two parameters (a string and an integer) and returns a Boolean:
Dim MyFunc As Func(Of String, Integer, Boolean)
A method that allows the developer to pass a lambda expression that specifies a test to perform within the method might look like this:
Public Function MyTestMethod(ProcLambda _
As Func(Of String, Integer, Boolean)) As Boolean
A lambda expression that fits this declaration might test the string to see if it's longer than a specified length. Calling MyMethod with that expression would look like this:
res = MyTestMethod(Function(st, len) st.Length > len)
Within the method, to process the lambda expression, you call its Invoke method, passing the necessary parameters, like this:
Return ProcLambda.Invoke("Peter", 4)
Posted by Peter Vogel on 09/06/2011 at 9:03 AM0 comments
Sometimes there is some processing that needs to be added to every page on the site. For instance, I had a client who wanted to add code to every WebForm's PreInit event to set Page's Theme property dynamically. Changing every PreInit event in every page sounded like the definition of no fun at all.
But there's a better solution. One place to put code required on every page is in an HttpModule. HttpModules process every page request that comes into the site. You can create your own HttpModule so that, when a request for a page comes to the site, you can add to code to any event on the page.
The first step is to create a class that implements the IHttpModule interface:
Public Class ManageTheme
In the class' Init method, tie a method of your own to the PreRequestHandlerExecute event that's fired by ASP.NET before processing a page. You'll use your method to customize the page's processing. You can get to the PreRequestHandlerExecute event through the HttpApplication object passed to the Init method:
Public Sub Init(Context As HttpApplication)_
Your method will now be called whenever a page is processed. In your method, you can add code to any of the page's events. This code ties a method called sitePreInit to the Page's PreInit event:
Sub WireUpPreInitEvent(sender As Object, e As EventArgs)
If TypeOf HttpContext.Current.CurrentHandler Is Page Then
Dim p As Page
p = CType(HttpContext.Current.CurrentHandler, Page)
If p IsNot Nothing Then
AddHandler p.PreInit, AddressOf SitePreInit
Finally, you can add the code you want to run on every page on your site to the event you've wired up:
Sub SitePreInit(sender As Object, e As EventArgs)
If TypeOf sender Is Page Then
Dim p As Page
p = CType(sender, Page)
If p IsNot Nothing Then
p.Theme = "MyTheme"
Public Sub Dispose() _
The last step is to update the httpModules element to incorporate your module into your site's processing pipeline:
<add name="MyHttpModule" type="ManageTheme"/>
Posted by Peter Vogel on 08/12/2011 at 1:16 PM0 comments
When I have lots of documents open in Visual Studio, I find it difficult to scan the tabs to find the document I want (I recognize that this may not be a problem for everyone). I often find myself double-clicking on files in Solution Explorer to pop it to the front of the tabs, even though I know the file is already open -- somewhere.
Or, I used to. Lately, I've added a new key stroke combination to my limited repertoire: Ctrl_Alt_Down Arrow causes the list of open files in the upper right corner of the editor window to pop open all by itself. That list is searchable, which means that if you start typing your file name, the focus shifts to the first file with a name that matches what you've typed so far. Since I (usually) know my file's name, I can press Ctrl_Alt_Down, type as much of my file name as necessary to highlight it in the list, and hit the enter key to open the file in a tab.
By the way: Once a tab is open, you can close it with Ctrl_F4.
Posted by Peter Vogel on 08/11/2011 at 1:16 PM7 comments
So you've got this long running application that gradually takes over all the memory on the computer that it's running on. You've got a resource leak somewhere; but how do you track it down?
Task Manager should be your first stop in determining if your application has a problem with leaking memory. After you've started Task Manager and switched to the Processes tab, go to the View | Select columns menu choice. The dialog box that pops up gives you a list of columns you can add to the display, seven of which have labels beginning with "Memory." The first five (all but the two containing the word "Paged") can provide useful insight to what your application is doing with memory. If you see, for instance, a process that has its Peak Working Set constantly increasing, it's a clue that the process is in trouble.
To get a handle on where your problems really are, there's an even better tool. To run it, right-click on Computer in the Start menu and select Manage. Drill down in the TreeView on the left to (in Vista) Reliability and Performance Monitor or (in Windows 7) Performance Monitor and open it to start tracking key data about your computer.
As with Task Manager, after bringing up the monitor, you'll want to add the counters that will help you find your problem. To see what's available (and there are a lot of counters available), right-click on the monitor's graph and select Add counters. At the top of the list of counters are some .NET CLR counters that can be very helpful.
Under .NET CLR Memory, you'll find counters that track the memory used by Generation 0 objects (objects that haven't been around very long), Generation 1 objects (objects that survived one garbage collection because they were in use), and Generation 2 objects (objects that have survived more than one garbage collection).
Most applications have many Generation 0 or Generation 1 objects and fewer Generation 2 objects (most .NET objects have short lifespans). If you have a large amount of memory in the Generation 2 memory pool, it may indicate that either (a) objects that should be short-lived are hanging around longer than they should or (b) that more objects are being created than you expected. If the counter showing induced garbage collections is high, I'd look at whatever process is creating objects first. Garbage collection is triggered when the instantiation of a new object causes the memory budgeted for a generation to be exceeded and the induced garbage collections counter can tell you if that's happening too often.
Another set of counters tracks the Large Object Heap (LOH), which holds objects that need 85K of storage or more. If you know that you shouldn't have any objects in the LOH or, if the amount of storage seems out of proportion to the number of large objects you expected, you can focus on those objects in your application that require a significant amount of storage.
There's more. Expanding the .NET CLR Data section, for instance, reveals a set of counters that let you track how your connection pools are being used. Good debugging is driven by real data, and the monitor is a good place to get that data.
Posted by Peter Vogel on 08/04/2011 at 1:16 PM3 comments