Subscribe: Thinq Linq
Preview: Thinq Linq

Thinq LINQ

Thoughts about LINQ served up with LINQ


Enabling LINQ to SQL in Visual Studio 2017

Sun, 05 Feb 2017 01:42:00 GMT

One of the benefits that Visual Studio 2017 brings is a light weight installer. Instead of spending hours to download and install Visual Studio as was typical in the past, the Visual Studio team worked hard to componentize the installation process and limit the amount of components you need to install for typical development tasks. This makes the installer take minutes rather than hours as we were accustomed to in the past.

The down side is that some features that you may have been used to out of the box now require separate manual installs. This includes two tools I use frequently including the Class Designer and LINQ to SQL tools. If you used these in the past and can’t find them now, fire up the Visual Studio Installer again and click the “Modify” button.


The workloads tab appears by default, but this won’t help enable the tools mentioned above. For those, you need to click the “Individual Components” tab and scroll down to the “Code tools” section. From there you can select and install the LINQ to SQL, Class Designer, and a host of other useful tools.


As you can see from the screen shot above, these screens and instructions apply to the Release Candidate. The actual screens and steps from the final version may change as the product release is finalized. Your mileage may vary.


Using Excel Query for end user reporting against an Azure database

Wed, 22 Jun 2016 12:35:00 GMT

In my recent project for a government agency, I’ve been evaluating options for reporting solutions that don’t require developers to build the reports and empower the business users to view and transform data to meet their needs. I looked at a number of typical off the shelf solutions, including Excel, PowerBI and Tableau. Because this solution used a government Azure tenant, PowerBI was out of the running since it is not current available for the Microsoft government cloud. PowerBI desktop and Tableau were both options, but would likely prove too much of a learning curve for my target customers. As a result, I landed on recommending Excel because of its ease of use, availability of learning resources, and the fact that many of my target customers already use Excel for day-to-day operations anyway. By default, Excel can connect (via Power Query or the query option in Excel 2016) to SQL Azure, however the ability to include end user parameters for the queries is quite cumbersome and limited. Excel Query has been in the product since 2003, but connecting to an Azure SQL instance is not straight forward because the OBDC dialog screens don't allow you to pass critical information to enable the connection. Below are the steps to create a connection between Excel Query and a SQL Azure instance and allow parameters to be passed from values entered in an Excel sheet. The instructions and screen shots below were created with Excel 2016, but many of the dialog boxes and connectivity methods (OBDC) have not been touched for over 10 years, so the concepts presented here should work for most Excel versions in the wild at this point. Connect Excel to Azure As I mentioned above, Excel does not currently offer an out of the box solution to connect to Azure via Excel Query. We’ll need to resort to a bit of hackery to overcome this limitation. Start by creating a dsn file to set up the default connection information to your server. This file will be used once to kick off the connection. After that connection information will be managed directly in Excel and embedded directly into the spreadsheet rather than relying on distribution of the dsn file with the Excel file. In a text editor, enter the following substituting your environment information for the highlighted items. Make sure to use the address as you won't be able to connect if it isn't set to the secure url from Excel.[ODBC] DRIVER=SQL Server SERVER={SERVERNAME} APP=Reporting WSID=NA DATABASE={DATABASE} [Microsoft Office] UID={USERNAME} PWD={PASSWORD} Encrypt=yes TrustServerCertificate=yes Save this file anywhere on your computer. You'll only need to access it once if all goes well. At this point, we can start working in Excel. Open Excel with a new blank workbook. On the data tab, select "From Other Sources" and then "From Microsoft Query". The "New Query" option does allow you to connect to SQL Azure out of the box, but doesn't provide an easy way to set parameters on screen to filter the data to the database without using the "M" modeling language. From the "Choose Data Source" window, "Browse" to find the dsn file that you created above. At this point you can step through the query wizard as you would to connect to any other source setting the fields, filters, sort orders, etc. that you need for your connection. At the end of the wizard, select the option to "View data or edit query in Microsoft Query" so that we can configure the parameters. Configuring parameter passing New we need to configure the parameter passing. In the query editor make sure the "Show/Hide Criteria" button is selected. Now you can drag your criteria fields from the table to the criteria field area. For the value, specify a string wrapped in square brackets ([param]) and make sure that the name is unique and not a field in your table(s). If prompted for a value, just ignore the prompt unless you want to preview the data. At this point you are ready to send the query results to Excel. Select File -> Return[...]

Build 2016 top 10 announcements

Sun, 03 Apr 2016 20:52:00 GMT

Once again I was able to make my way out to San Francisco for Microsoft’s \\build\ conference which highlights the latest advances in Microsoft development technologies. I use it as a judge to see what technologies are receiving focus and how well they are being received. there’s no way that I can cover everything that was shown due to the abundance of sessions. I did try to keep my ear open as to things that people were talking about outside of sessions  on things that were buzzworthy (positively or negatively). If you want to dig into the sessions and judge for yourself, head on over to Channel 9 for the slides and videos or GitHub for the labs. With that out of the way, here is my top 10 list. I’ve also posted a slide deck with some additional links for this list. If you don’t agree, fill free to Disqus below. 10 – HoloLens While this wasn’t a new announcement, the start of the conference did mark the start of shipping development kits to people willing to shell out $3000 bucks. They did have a number of sessions and labs around the HoloLens, including bringing Mars to the exhibit floor for those with enough free time to wait in line to try it out. I choose to spend my time otherwise and passed on this one, but did hear from some that the limited field of vision continues to be a challenge. I’ve heard from some that have tried the devices outside of the dark test environments have indicated that they do work reasonably well in regular light situations, but have limited battery life. I’ll keep my eye on this space (pun intended), but will hold my money as well until the space settles down a bit. 9 – Xbox developer mode While there was much less time spent on the new Xbox Developer Mode, I did find it noteworthy because it indicates a shift in philosophy from a closed/curated ecosystem, back to one that more openly embraces the hobbyist and indie game developers. In the past we’ve struggled to get access to platforms and frameworks to build apps for the Xbox, and now we may finally be able to start building some of those systems for customers again. 8 – Azure functions While it’s been easy to create services for Azure, those solutions required full .Net or Node projects. With the new Azure Functions, Microsoft is directly attacking AWS Lambda and Google’s Functions by offering a light-weight mechanism to write small script components in C# or JavaScript that can respond to a number of system events, including messages on a message bus, adding items to blob storage, etc. With this, it should be even easier to use Azure for Micro services. 7 – Centennial The Centennial project should be of interest to anyone still working on Windows client applications (Win32, Winform, WPF) that wants to simplify deployment and discovery through the store (public or private) and/or wants to light up windows 10 features like live tiles, notification services, etc inside of their existing application. The process can replace existing WIX or InstallShield installers with the store appx packaging system. When Windows 10 Anniversary edition arrives this summer, users will be able to open the appx just by double clicking it from the desktop. As an additional feature, the new packaging will enable a virtualized registry redirect to allow for storing registry settings in the local executable path rather making installation and removal easier and more reliable. 6 – C# 7 It wouldn’t be a Microsoft developer conference without some discussion of the future of C# and this year produced a number of oohs and aahs including support for binary literals, Digit separators, Tuples, Local functions, and pattern matching. In all honesty, the local functions feature had some scratching their heads trying to figure why Microsoft wanted to make your C# code look like JavaScript with nested functions. The others received a fair amount of love with the greatest applause coming from named tuples. Honestly, I find them to be a lazy way to enable multiple return values from a[...]

Roslyn resources

Mon, 04 May 2015 21:33:00 GMT

Today I’m giving a hand’s on hack session on using Roslyn to create code Diagnostics. As part of that, I put together a listing of some resources that might be helpful and thought I’d share them with everyone. Realize that these links are based on the versions for the Visual Studio 2015 RC and are subject to change when the product releases. With that in mind, here are the links: Download Visual Studio versions: Github repository for the Roslyn compilers: Github repository for the .Net core: Code Cracker analyzer project: VB Team Blog: Posting of the new language features for C# 6 and VB 14: LLILC (LLVM for .NET Core): Roslyn Visualizer: Roslyn SDK Templates: Of course, I’d be remiss if I didn’t include this presentation: There are also some older hand’s on walkthroughs that might be helpful, but know that they are based on even earlier versions and you will need to figure out how to change them to get them to work. However, sometimes the best ways of learning is to fix something that is broken, so think of it as a growth exercise. MSDN article on how to write an analyzer: MSDN article on how to write a code fix: Hand’s on Labs from the Roslyn Documentation: [...]

Microsoft Connect conference links and key takeaways

Sun, 16 Nov 2014 02:07:00 GMT

This week, Microsoft ran an online conference which included a number of significant announcements. In case you missed it, the sessions are available for streaming on demand on Channel9. The major announcements were accompanied by a number of blog posts. In this post, I wanted to summarize the most important items that I noticed and include links to the detail pages where appropriate. •.Net Core open source – Perhaps the most significant announcement was the open sourcing of the .Net core. This does not mean that the entire framework is open sourced, but represents a leaner core part of .Net upon which other components can be added (including ASP.Net, WPF, Windows Forms, Entity Framework, etc). The intent here is to open the development process and encourage a more active and engaged open source community. To that end, the team accepted it’s first pull request within the first hour of the announcement. Perhaps the most ironic post regarding the open source announcement was @PVandenheede’s statement, ".NET open source? On Mac and Linux? Hah! Only when spacecraft start landing on freakin' comets!" In addition, the new framework will shift from numbered versions to year names and it will be installed local to your solution rather than installing to the GAC that we’ve had a love-hate relationship with the original 1.0 release came out 12 years ago. Visual Studio versions – Closely tied to the .Net version information, Microsoft also announced two new versions of Visual Studio. The Visual Studio Community Edition will contain the functionality of VS 2013 Pro (including supporting plugins) but will be free for open source, charity, students and companies with under 250 employees and 1 million annual revenue. This will potentially replace the express versions and reduce the need for the BizSpark and DreamSpark programs. In addition, Microsoft officially named the next version as Visual Studio 2015 many of the new features that I will talk about below are also included in the release notes. •ASP.Net vNext – Among the many features of VS 2015, perhaps the most significant is ASP.Net vNext including MVC 6 and EF 7. This version is a drastic re-thinking of the ASP stack including a JSON based project and configuration files, direct inclusion of Grunt, Bower, and a revised Nuget. The new .Net core is only 15 meg and can run natively not only on Windows, but also natively on OS X and Linux servers. At this point, I wouldn’t recommend writing production code in vNext, but it is worth keeping an eye on and kicking the tires a bit. •Entity Framework 7 – Perhaps even less stable that ASP.Net vNext, is the completely re-written EF7 which among other things removes the use of the EDMX  and reduces the API surface from previous versions. It is such a large change, that the team even posted recently the as to whether EF should be a v7 or v1 of a different product. •Typescript 1.3 – Typescript continues to mature and quietly updated to version 1.3 with the 2015 release. This version takes advantage of the Roslyn language services to support a number of new analytics and refactorings. If you want to configure ASP.Net vNext to automatically transcode your Typescript into JavaScript, check out  Marcin Juraczek’s post on configuring grunt-typescript. Cross platform – These days of Mobile first application development, most companies are trying to figure out how to reduce costs and increase code reuse. Visual Studio 2015 offers a number of new features to try to address these concerns. •Xamarin – Visual Studio 2015 includes increased partnership with Xamarin which allows developers to write C# code to natively target iOS, Andriod, Windows Phone, and Windows RT using a single code base. •Apache Cordova support – VS 2015 also includes enhanced tooling for creating mobile apps with HTML/JavaScript using the open source Apache Cordova (PhoneGap) tools including debugging iOS directly fr[...]

ThinqLinq Samples on Github

Tue, 28 Oct 2014 00:57:00 GMT

When I first created this site, I used it both as a test bed for technologies as I played with them and as a repository for others to use for my presentations and other insights. Over the years people have been free to grab these samples, but haven’t had good ways of giving back to help improve the samples.

In the mean time, there have been quite a number of technology changes. While I may have been late to the party on some, there’s only so many things one can focus on. One of the advances we’ve seen is the explosion of use of web based distributed source control systems. In that light and as an effort to both make it easier for you to access the sample projects, and help you give back, I’ve started publishing my samples on GitHub. While you’ll still be able to download the samples from my downloads page, I won’t be updating them as new versions ship and instead will plan to keep the samples updated on GitHub instead.

At this point, the following projects are available via my Github repository:

  • RX Samples – Includes Windows Phone, Silverlight, Windows Store App, WebForm, HTML/RxJs, and SignalR samples with code in VB, C# and JavaScript.
  • WebAPI Samples – Includes Sample recipe EF 6 models and Web API endpoints.
  • Signalr RX Samples – Includes Chat samples in Web and WPF, Reactive Sensors in Web and Console projects, and Drag/Drop with MVC and WPF

As always, please let me know what you Thinq. Does Git make accessing the samples easier? Is there something else I should add to the samples?


Reactive Extensions RX in Action the movie

Tue, 23 Sep 2014 01:30:00 GMT

I’ve been giving presentations for some time now. By far, my favorite talk to give is my Reactive Extensions in Action. This past summer, I had the talk recorded at the Code on the Beach conference. If you haven’t had a chance to see it live, now’s your chance. The focus of this talk is to focus on some of the practical uses of Rx rather than the underpinnings of how it works. I hope you enjoy and this sparks ideas from you on how you can use it in your applications.


(object) (embed)
Rx in Action recorded at Code on the Beach.

Build Impressions

Mon, 07 Apr 2014 00:51:00 GMT

Once again, I had the opportunity to attend Microsoft's //build// conference in person. This year did not disappoint. In these days of fast internet and streaming/recorded conference sessions, I still find it beneficial to attend in person, not so much for what is said on stage, but also to see the audience reaction to announcements. In some of the informal gatherings like at lunches or at the airport, I try to make it a habit to ask people what the best thing they saw was to gain a feeling for the atmosphere that was present in rooms that I was not able to attend in person. I also try to pay attention to not only what is said, but also what is conspicuously not said at the conference. In this post, I'll try to share my thoughts from the conference on what was hot and what to keep an eye out for based on the reactions I saw. In the opening keynote, Microsoft announced to of the most important advances: Universal Apps and the Windows Phone 8.1 with the Cortana voice based digital assistant. With Universal Apps, Microsoft is attempting to move closer to a unified programming model across devices and platforms including Desktop, Tablet, Phone, and XBox. Adding in partners like Xamarin and Unity 3D, these universal apps can also extend to Apple and Andriod as well. There is not 100% API parity across the platforms, but these changes promise to get us much closer to the silver bullet of write once-run anywhere that so many customers are looking for. At this point they indicated that there is a 90% API parity between Windows Store Apps and Windows Phone 8.1. While you can use Xamarin, Miguel did indicate that you will still need to write separate UI tiers for the Andriod and iOS platforms reusing the core shared business logic. Speaking of platforms, Microsoft took the wraps off of the next major update to Windows Phone. Contrary to some reports, this is a major update both from a developer and end user perspective and generated quite a bit of buzz. The changes include significant lock screen enhancements, a new notification center, and an impressive voice controlled digital assistant called Cortana that learns from your use and integrates with your online activities. It's like Siri and Google Now got together and had a prodigy. I didn't get to try it out first hand, so it will be interesting to see how much the demos were scripted or if it really works as well as promised. Integrating it with your apps also appears to be relatively painless so it will be interesting to see how well this takes off. The new UX features on the phone along with a unified WinRT model really make the platform something to contend with and signals the convergence that had long been rumored. Beyond the advances in UX and platforms, developers were pleasantly surprised by the language teams with some important advances in managed languages including the release of the Roslyn compiler. After a long 5+ year effort, the teams have finally completed the rewrite of the compilers in managed languages adding in extensibility hooks for the community to build extensions to the language services. This also allowed the team to add a number of language features that had long been on the back burner, but they were now finally able to incorporate with a more manageable code base. Perhaps the biggest news item of the conference trending on Twitter and at the top of Hacker news as Anders open sourcing the Roslyn compilers. Hopefully as a result, we'll now see a greater community involvement in extending the languages in much the same way that F# has thrived from their small but enthusiastic community. In addition to the managed languages teams, the developer division has been hard at work on a number of other things that were debuted at Build to largely positive reactions. Among these, the ones that got the most reaction were the release of Typescript 1.0, SIMD support, and [...]

RxJs Select or Map

Thu, 20 Mar 2014 19:37:00 GMT

While reviewing the recent updates to RxJs and looking at some of the tutorial information, I started to get perplexed at the difference between select or map, selectMany or flatMap, and where or filter. I’ve typically used the LINQ/SQL methods of select, selectMany and where, but while reading the learnrx tutorial I noticed he was using map, flatMap, and filter and became confused as to how these are related. A quick peek at the source (which is easy with JavaScript libraries) reveals how. Here’s the signature of select and map for example: = = function (selector, thisArg) {
    // Do amazing stuff       
So we can see that select and map both extend the Observable prototype and alias the same function. The same is true of selectMany/flatMap and where/filter. As a result, which should you use? It doesn’t matter. Use the syntax that you’re more comfortable with. As a LINQ guy who started with Access in my professional career, I thinq I’ll probably stick to the SQL like methods, but don’t be surprised to see map/filter sneak in from time to time.(image)

Reactive Translator for RxJs

Thu, 20 Mar 2014 19:05:00 GMT

For several years, I’ve been demonstrating using Reactive Extensions to handle taking user input and providing real time translations into multiple languages. This code sample has been so popular that even Soma posted an example using the Bing translator in his introducing Rx post. In most cases, they only issue a single request or issue multiple individual requests for multiple languages. To add a bit of fun to my demo, I decided to spice it up a bit and turn the list of destination languages into another observable that I can join with as well. I’ve had this sample available in my Rx downloads for some time including one for RxJs, but haven’t updated them to newer versions (curently 2.2.2) until now. I figured it was about time that I shared my version. To begin, let’s create a UI. We’ll keep it simple to allow us to focus on the code that comes later:
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } As they say at Staples, “That was easy.” Next comes the fun part. Before you can do anything though, you do need to sign up to get a free Bing App ID. I’m not going to share mine . Once you have that, your ready to code. But what are we going to do in our code? This example combines three observable sequences: User key presses, destination languages, and service request/responses. In order to work with the observables and to make it easy to work with our DOM, we need to start by importing two JavaScript libraries: jQuery and Rx (we only need the rx.lite version for this example). .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Of our three observables, the second one is the easiest, so let’s get it out of the way first. To turn an array of languages into an observable sequence, we use the fromArray generator on a simple JavaScript array.var destLanguages = ["de", "es", "zh-CHT", "fr", "it", "ar", "ht", "tlh"]; var languagesObs = Rx.Observable.fromArray(destLanguages); With that out of the way, Let’s take a look at how we are going to issue the service requests to Bing and turn them into an observable stream. We’ll use the jQuery ajax method to issue our request to the Bing translator using jsonp and pass in the required app id, destination language (to), source language (I speak American English, so ‘from’ is ‘en-us’) and the text we want to translate (text). We can then use JavaScript promise to access this invoker and us[...]

Tools and Apps

Mon, 17 Mar 2014 15:49:00 GMT

Now that I’ve been at Slalom Consulting for 3 years, it’s about time for a hardware refresh. In preparation for that, and inspired by the recent list by Rocky Lhotka of top Win8/RT apps, I thought I would share the list of software/tools that I have found indispensible over the last several years creating software for customers. This is by no means as extensive as the impressive list of tools that Scott Hanselman puts out each year. A while back, I published a list of LINQ tools if you’re looking for some of those. This list is primarily intended as a list to help me when I build my next laptop and offered here in case you find it helpful as well. It is by no means an endorsement of any specific product and I’m not paid to publish any of these. Feel free to add to this list in the comments as I may well have forgotten something along the way. System apps and tools Windows Home Server I use this to seamlessly backup all of the computers on my home network. It’s saved my rear multiple times and makes trying out new builds of Windows and other applications a painless task. I’m still using the original version and it’s been grinding along smoothly for years now. Windows Live Writer The best tool I’ve found for offline creating/editing of blog posts. So great, I’m using it to write this post. Paint.Net Impressive free image editor that includes many of the features of Photoshop without the license costs. Includes multiple layers and special effects. WinDirStat Do you ever wander what’s taking up so much space on your drives? WinDirStat is a nifty way to visualize your space usage and can break it down by folder/file type/etc. It’s an easy way to find those screen capture videos, vhd images, and audio files that can get scattered around the drive over time. TouchFreeze This simple utility blocks the annoying mouse jump that can happen if your thumb accidently brushes the touchpad while typing. Syncfusion Metrostudio Over 2500 metro-style icon templates that are free (in exchange for giving them your email address that they will use to spam up) and customizable either as XAML or png images. I’ve used these a number of times when needing an icon for a customer and changed the colors to match their brand. FileZilla The best free FTP client I’ve found yet. EasyBCD GUI to manage your boot image. Helps out in a pinch when the boot sector gets corrupted or you want to configure dual boot to a VHD image for testing new OS images. It has a free non-commercial version as well as a minimal cost commercial version. Oracle VirtualBox Free virtual machine tool. I’ve found this to be lighter weight and easier to configure than HyperV or VmWare. I’m sure there are features that it doesn’t include, but so far I haven’t had the need for the bigger tools except for running the Windows Phone emulator which does require HyperV. ProcessMonitor Low level debugging tool for when you want to see in minute detail what your drives are doing. The information that it provides can be overwhelming, but I’ve found it useful for tracking down pesky file not found issues where a system is looking for a file in a different path than where it actually exists. Metrotwit This has been my go-to twitter client for years. Unfortunately due to Twitter’s access token limit, they recently announced that they will no longer support new users or upgrades. I may have to switch back to Tweetdeck or Tweetium once the new computer comes but wanted to leave this on my list in tribute to the app that was. Foxit Reader Much smaller and faster than Adobe Reader. Handles 95% of my pdf reading needs with less overhead. Development Tools Visual Studio The best development IDE out there. I spend most of my time working in the .Net world, but have had to work[...]

Left Outer Joins in LINQ with Entity Framework

Thu, 06 Mar 2014 16:25:00 GMT

As I spend more time reviewing code with clients and on public forums, I’m constantly seeing cases where people have issues with Outer joins in LINQ and the various flavors of LINQ. In this post, I’m going to look at a couple options from a syntax perspective that you can use to make working with outer joins easier with LINQ. Naturally, we have a much deeper discussion of outer joins in our book that you’re welcome to dig into as well. Typically, if you do a join in LINQ, it will perform an inner join where it only returns the records that contain results in both sides of the evaluation (in both tables). If a child table doesn’t have a record for a parent row, that result will be excluded. Consider the case in Northwind where an Order record exists, but there are no order detail lines. The following LINQ query won’t return that record as part of its result set:from o in Orders join od in OrderDetails on o.OrderID equals od.OrderID select new {o, od} .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } This translates into the following SQL statement (Note to DBA’s that are concerned by the * below: EF does list the individual columns in the actual SQL. I’m reducing them to * for the purposes of this post to focus on the join clauses):SELECT [Extent1].*, [Extent2.* FROM [dbo].[Orders] AS [Extent1] INNER JOIN [dbo].[OrderDetails] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID] .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } If you want to include the orders regardless of whether it has any detail lines, you would need to turn this inner join into an outer join using the DefaultIfEmpty extension method. LINQ only supports left outer joins. If you want a right outer join, you need to flip the logic of your query to turn it into a left outer join. In order to use the DefaultIfEmpty, you typically need to push the joined set into a temporary value first and then select from it using the DefaultIfEmpty method:from o in Orders join innerOD in OrderDetails on o.OrderID equals innerOD.OrderID into Inners from od in Inners.DefaultIfEmpty() select new {o, od} This generates the expected LEFT outer join as shown below:SELECT [Extent1].*, [Extent2].* FROM [dbo].[Orders] AS [Extent1] LEFT OUTER JOIN [dbo].[OrderDetails] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]The problem that I have with this is that the syntax seems overly verbose to accomplish this change. As is often the case, Microsoft often gives multiple ways to accomplish the same goal. One method that I’ve started to find helpful is to revert to more of an ANSI 8[...]

Custom Logging with Entity Framework EF6

Tue, 11 Feb 2014 20:44:00 GMT

Coming from LINQ to SQL, I’ve long been a fan of its logging simplicity where you just set the context.log to a stream (console.out) to see the stream of sql statements being issued to the database. Prior to EF6, this has been a rather frustrating omission that has finally been rectified with EF 6. Now, you can log SQL statements just as simply, but with a slightly different implementation. In EF, you set the DbContext’s Database.Log property to an Action delegate that takes a string as input parameter. Thus to log requests to the Diagnostics Trace implementation, you simply set the following:context.Database.Log = Sub(val) Diagnostics.Trace.WriteLine(val) This is fine as long as you are willing to accept the default logging implementation. If you want to customize the log output, things get a bit trickier. In my example, I only want to log the calling method and elapsed time that the query took to execute. I’m not as concerned in my current need for the SQL string, but that is easily included as I’ll point out below. To start, I need to set the calling method from my code that I’ll be able to access in the logging implementation. Since the calling code is actually in ASP.Net Web API calls returning IQueryables, and the database is not being executed until long after my application code has completed. I need to explicitly identify the application calling method rather than the last method from the call stack which would otherwise be one of the Web API internal methods. To handle this and centralize my set-up logic for the context, I’ll create a factory method which configures the context and use that instead of relying on the context’s constructor. Thanks to partial classes, I can extend the generated entity class and include this new property and factory constructor. To make matters even easier, I’ll take advantage of the CallerMemberName attribute to automatically pull the member name from the method that is calling the factory.Public Class NorthwindEfEntities Public Shared Function ContextFactory( Optional memberName As String = "") As NorthwindEfEntities Dim context = New NorthwindEfEntities() context.CallingMethod = memberName context.Database.Log = Sub(val) Diagnostics.Trace.WriteLine(val) Return context End Function Public Property CallingMethod As String End Class Now, to create the context we call this new ContextFactory method, but don’t pass the memberName explicitly. The compiler will add that for us automatically. Public Function GetCustomers() As IQueryable(Of DTO.DtoCustomer) cn = NorhwindEfEntities.ContextFactory ' Do some amazing query and return it. End Function Now that we’ve set up the logger, we need to customize the output that is generated. To do this, we need to add a new class that derives from DatabaseLogFormatter (in the System.Data.Entity.Infrastructure.Interception namespace). If you don’t have this namespace, you may need to upgrade to EF6 on order to access the logging functionality. Since the base class doesn’t have a default parameterless constructor, we need to make a new one and simply delegate to the base implementation. With that out of the way, we can supply our own logging. The base implementation gives us hooks to the following interception points. Method Description LogCommand Writes the SQL Statement to the action implementation prior to executing the statement LogResult Writes when the SQL statement has completed LogParameter Writes the parameter(s) used in the query Executing/Executed Called before and after the database request is made NonQueryExecuting/NonQueryExecuted Called for queries that don’t return results (insert/update/[...]

Logging ASP.Net WebApi elapsed time for IQueryables

Tue, 11 Feb 2014 19:03:00 GMT

I’ve recently been doing a fair amount of work with exposing queryable structures using ASP.Net Web API. In a nutshell, Web API allows you to expose not only the data from a service request, but also the query structure that can be extended. By default, the Web API scaffolding generates Get(id), Get, Post, Put and Delete methods. Of these, the most interesting, from a query perspective, is the Get method that returns a list of values: Public Function GetCustomers() As IEnumerable(Of Customer) Dim query = cn.Customers Return query End Function .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } By returning the default IEnumerable, the database will return everything that matches the service query definition and the consuming application isn’t able to customize this view to meet their needs. If you apply a couple changes, you can enable much greater service flexibility and allow customers to access your resources the way they want rather than just the way you originally intended. Of course with great power comes great responsibility. To make our GetCustomers method queryable, we simply change the output type from IEnumerable to IQueryable. Public Function GetCustomers() As IQueryable(Of Customer) Dim query = cn.Customers Return query End Function In addition, we need to enable the query support for Web API in the Web Api Config’s Register method in the App_Start folder:Public Module WebApiConfig Public Sub Register(ByVal config As HttpConfiguration) ' Web API configuration and services ' Web API routes config.MapHttpAttributeRoutes() config.Routes.MapHttpRoute( name:="DefaultApi", routeTemplate:="api/{controller}/{id}", defaults:=New With {.id = RouteParameter.Optional} ) config.EnableQuerySupport() End Sub End Module Now, if we run the service, we can not only get the full list of Customers, but also add querying options like filter (where), orderby, top, skip, etc. For example, the following query gets the first two customers from London ordered by the Company Name. For a complete list of the various url query options, refer to the OData Query Specification URI Convention page. http://localhost:17413/api/Customer?$filter=City%20eq%20'London'&$orderby=CompanyName&$top=2 Now, how does this fit with the question of logging the requests? If we were to use a traditional logging mechanism, we could just wrap the beginning and end of the service request with a timer and output the difference. Public Function GetCustomers() As IQueryable(Of DTO.DtoCustomer) Dim sw = New Stopwatch sw.Start() Dim query = cn.Customers sw.Stop() System.Diagnostics.Trace.WriteLine("GetCustomers completed in " + sw.ElapsedMilliseconds.ToString) Return query End Function This outputs the result: “GetCustomers completed in 1”. Why is the time to execute so short? Remember that with LINQ, the database request is deferred until the results are iterated over (GetEnumerator). In the case of an IQueryable with [...]

Book Recommendations

Sun, 22 Sep 2013 23:52:00 GMT

I’m often asked to give book recommendations. While it’s been too long since I looked at beginner books, I have had some opportunity to benefit from some of the more intermediate-advanced books out there. When putting together the prizes for this year’s Atlanta Code Camp, we had the opportunity to hand pick books using some of the budget. I reflected a bit on the books that I’ve read and came up with the following list which should be a descent should read list for people interested in programming related topics. I’m sure that the list isn’t all inclusive and apologize to any of my author friends who may feel slighted by not being included here. I welcome you to add your favorites in the post comments if you see any glaring omissions.  Without further ado, here’s my top recommended list: General Programming Books Design Patternsby Erich GammaAlso known as the Gang of Four (GOF) book, this is the book which defined a set of common code design patterns to give programmers a common language to understand and design object oriented programs. Patterns of Enterprise Application Architectureby Martin FowlerMartin catalogs an extensive list of typical application architectures and how they fit together. Pair this with the GOF and you’ll be amazed how your applications can benefit from these patterns (and how often you used them without having a concrete name for them). Test Driven Development: By Exampleby Kent BeckIf you’re not testing your code, it is invariably brittle for refactoring and subject to breaking as new enhancements are added. This book will get you up and running with TDD and show you why and how you should be doing it. Clean Code: A Handbook of Agile Software Craftsmanshipby Robert C. MartinFrom the man that brought us the SOLID Pricipals, Uncle Bob gives us this guide to building pride in the code you write resulting in more maintainable and understandable code. Code Completeby Steve McConnellSteve includes examples from a number of programming languages along with case study results and metrics to help guide you toward building more quality code. Refactoring: Improving the Design of Existing Codeby Martin FowlerAs the only author appearing twice on this list, Martin’s book offers excellent examples of how and when to improve an existing code base by refactoring it safely.  As a consultant, I’m typically brought in to work on an existing code base often in some state of disrepair (hence why I’m brought in). Unless you are careful when working on these code bases, you can risk breaking everything. Martin helps to show the right way to do it. User Experience Books Why Software Sucks...and What You Can Do About Itby David S. PlattDavid uses humor and anecdotes to help developers understand why their applications are not as successful as they may have envisioned (hint: users don’t want to use your system, they want to HAVE used it). About Face 3: The Essentials of Interaction Designby Alan CooperAlan helped to pioneer the study of interaction design. While this book may be somewhat dated, many of the guiding principals he outlines are still pertinent to today’s applications. Javascript Books JavaScript: The Good Partsby Douglas CrockfordPerhaps the most detailed discussion of the JavaScript language, Douglas points out the parts of JavaScript that you should, and perhaps more importantly should NOT use. A must read for anyone serious about JavaScript programming. Secrets of the JavaScript Ninjaby John ResigFrom the person who brought us jQuery, John shares his experience along with valuable tricks and tips on how to improve your JavaScript programming. jQuery in Action, [...]

CodeLens for Visual Studio 2013 configuration

Fri, 13 Sep 2013 15:01:00 GMT

One of the new features of Visual Studio is the ability to automatically see statistics about your code while you are looking at it via a new feature called CodeLens. For example, you can see how many method calls are referencing your method, how many bugs and what tests are accessing the method. While this information can be helpful when reviewing code, it sometimes gets in the way when writing it. For example, you can’t just click the 0 references line and start adding attributes and comments, you have to add them in a blank line above the CodeLens injected text (included as images here because you can’t copy and paste the CodeLens text). If you don’t want the CodeLens information getting in the way, simply right click one of the tags and select CodeLens Options. Alternatively, in the Quick Launch window (Ctrl+Q), just type CodeLens to find the menu in config. From here, you can tweak which values are displayed inline with your code or disable CodeLens entirely to get it out of your way while writing code. [...]

Rx for Windows Phone article now available

Wed, 11 Sep 2013 21:06:00 GMT

A couple of years ago I gave a presentation at the last MIX for doing asynchronous programming using Reactive Extensions (Rx) on the Windows Phone. The video of the presentation is still available for watching. Around the same time, I was approached by Code Magazine to write a similar article, which I was happy to oblige. It took a while but I was happy to see the article finally published in the Sep/Oct 2013 edition of Code Magazine.

In the article I demonstrate how to use Rx to build a dice rolling game (like Yhatzee) for the Windows Phone 7 or 8 and communicating with a service tier for rolling the dice (hiding the fact that you may be loading the results). While the particular solution was targeted to Windows Phone, the concepts are valid across the wide variety of platforms that Rx supports. Give the article a read and let me know what you thinq.


Multi Lingual LINQ and Rx

Tue, 23 Jul 2013 16:10:00 GMT

“Imitation is the sincerest [form] of flattery” – Charles Caleb Colton When dealing with technology, or nearly any other creative endeavor, one of the ways that you know if you’ve discovered a successful creation is to see how much it is embraced by not only your peers, but also your competitors. We can see this in quotes by Shakespeare, the music of Bach, parodies of Peter Schickle,  Hip Hop sampling, and countless others. This blog is based on one of the more significant technologies that many peers and competitors embraced – LINQ. As developers on both the Microsoft stack, and others saw the power of LINQ and worked to incorporate the concepts into their native languages, including JavaScript, C++, Scala, Java, Ruby, Python, and the list goes on. While the ideas in concept are not totally unique in LINQ, the way that they were put together provided the simplicity and elegance that drew many to desire to replicate it in their native tongues. We’re starting to see a similar wave of imitation with the Reactive Extensions as we did with LINQ. While Rx primarily extends the ideas and concepts from LINQ as can be seen by the nickname “LINQ to Events”, the way it puts them together is forging it’s own way. Indeed, it is already creating it’s own movement with the Reactive Manifesto. What began as a mechanism to bring asynchronous LINQ to C# and VB, has become a powerful mechanism to declaratively process real-time streams of data in a multitude of environments. Microsoft got the ball rolling by creating not only .Net implementations of RX, but also JavaScript and subsequently C++. More recently, Brian Benz announced the availability of Rx for Ruby and Python. We’re also seeing a number of efforts around Functional Reactive Programming (FRP) including Haskell and F#. Others have joined the party with the Scala based Reactive Web and ReactiveMongo (a scala based driver for MongoDB). Netflix has even contributed their own RxJava. In many ways, I’ve found LINQ and Rx to be disruptive technologies (in a good way). The amount of effort around manipulating core language constructs to enable the concepts only speaks to the impact that they’ve had on developer’s lives. I’m not saying that Erik Meijer is a combination of JS Bach, Shakespeare, and Sir Mix-a-Lot, but I’ve definitely noticed his handprint on many as the father of this disruption. I can only hope that we’ll see this kind of continued energy and creativity far into the future. I’m sure I’ve missed some language implementations of LINQ or Rx and can’t anticipate future versions that are inevitably yet to come. Please do others a favor and comment back with links to your favorite implementation so that we can build a complete list together. [...]

SignalR and Rx talk materials

Mon, 15 Jul 2013 16:17:00 GMT

Last night I gave a talk for the Gwinnett Georgia Microsoft User Group. There were plenty of questions and discussion. Everyone seemed interested in learning about SignalR at least even if most hadn’t heard of Rx (gasp.) The slides and demos from the presentation are available for download here. Update – 7/18/2014:  The source code and slide deck is now available on Github for these projects. Head on over to to check them out. Anyway, for those that want to dig deeper into these technologies, here are some resources that you might want to check out: SignalR landing page Github site for SignalR source JabbR ShootR Free SignalR Ebook Build talk from 2013 - Scaling the Real-time Web with ASP.NET SignalR David Fowler, Damian Edwards: Under the covers with ASP.NET SignalR RX dev center RX Team Blog RX open source project Free Rx Ebook [...]

HTML5 mobile apps with RhoMobile

Mon, 15 Jul 2013 16:16:00 GMT

It seems that every customer I talk with these days are chasing the elusive write once run anywhere. Typically, the best answer is to go with HTML5/JavaScript because it is the most ubiquitous option across devices. One recent customer that I was working with has a fleet of Windows Mobile 6.5 devices with native applications that they are looking to upgrade to a more modern hardware device. Unfortunately, the browser on the Windows Mobile 6.5 devices do not support HTML5, or the necessary app packaging mechanism to keep their employees from using the device browser to access non-company web solutions. Initially, we looked at using the popular PhoneGap solution for packaging applications built with HTML. While PhoneGap does have wide support for a number of platforms, they do not support the older Windows Mobile operating system. Motorola does offer a solution that does work with Windows Mobile as well as newer platforms, including iOS, Android, and Windows Phone 7+. Their RhoElements suite provides the ability to build mobile applications either using Ruby or even plain HTML5. They offer a descent tutorial for getting started including a sample which can access the native barcode scanner on the Motorola devices. So how are they able to support HTML5 on a device based on an OS with IE6? They have essentially ported WebKit to Windows compact allowing them to render HTML5 reasonably well. This appears to work fairly well for relatively simple layout options and does appear to support relatively complex JavaScript frameworks, including jQuery, backbone, underscore, require. One issue that I did run into is the performance can leave a bit to be desired. Don’t expect to see V8 level performance from their JavaScript interpreter. They are still restricted by the memory and operating system limitations that come with an OS and hardware that are over 5 years old. For example, I tried to implement the iScroll plug-in to allow for flick and finger drag support over a list of items. Just adding 100 items to the demos that come with iScroll brought the UI to a screaming halt (actually, the UI paused briefly and then would immediately scroll to the bottom of the list regardless of how far I swiped with my finger). This caused the list to become utterly unusable. I did find the RhoMobile platform to be an interesting option for Windows Mobile and beyond, you do have to be careful what you try to get it to do. If you’re not careful, your result may still lead you down the road of write once, suck everywhere. [...]