Recent posts about LINQ and related topics by ThinqLinq

Using Excel Query for end user reporting against an Azure database

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.

[Microsoft Office]

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 data to Excel, and specify where you want the resulting table to be sent. Make sure to include space for parameters that you want the user to supply. For example in this case, let's allow a couple lines to let the user specify the claim number that they want to filter by:


We're almost there, next we need to wire up the parameter that they input with the query. Right click anywhere inside of the table and select Table -> Parameters.


In the parameters window, let's set the "param" parameter that we named in the query editor to "Get the value from the following cell" and "Refresh automatically when cell value changes" as shown.


Now our parameters should be set and values will update when you change the values in the parameter input fields.

Update connection properties

In order to share the workbook with others, let's make a couple minor tweaks to the connection to ensure that data is loaded when the workbook is opened and not to save the underlying data when the workbook is closed. Also, although we set the password in the .dsn file in the first step, It isn’t persisted in your workbook for security concerns. We’ll need to set it again here if you want it persisted with your solution.

From the "Data" ribbon, select the Connections option. Find your connection name and click "Properties". In the Usage tab, select the options to "Refresh data when opening the file" and "Remove data from the external data range before saving the workbook”.


Next on the definition tab, notice that the connection string retained the driver, server, database and user from the .dsn file, but the password is not retained by default as that is less secure. If it is valid for your environment, you can specify the shared password for a security limited user by setting the PWD parameter as appropriate.


At this point, save your excel file and you should be ready to share your work with other users. All they need to do is to set the parameter to a valid value and they will start seeing live data streaming directly into Excel.

There's plenty more you can do using Excel as a reporting engine, but this should get you started with the critical steps of connecting Excel to Azure.

Posted on - Comment

Build 2016 top 10 announcements

imageOnce 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 function, but acknowledge that they can reduce the amount of boilerplate code that would be required with return value classes otherwise. For a more comprehensive list of language features being considered, head over to the Github issue tracking them and provide your feedback.

5 – Visual Studio “15” beta

C#7 has a dependency on a new version of Visual Studio which is tentatively called Visual Studio “15” (not to be confused with Visual Studio 2015 which is version 14). The preview is available for download both as a full iso and with a smaller streamlined installer. In fact, several presenters demonstrated the ability to install the core of VS 15 on a virgin machine in less than 180 seconds. It will also allow for side by side installs with other copies of itself due to the virtualized registry management that’s used in the Centennial project. Among the many features demonstrated, the one that got the most attention (in several presentations) were the new “How do I …?” feature in code completion lists. With this you could search for code samples online to do standard tasks (like converting XML to JSON). After pasting the sample code into your project, you can resolve references not only to already referenced assemblies, but also to nuget assemblies and have them added to your project directly from nuget. Additionally, GoToReferences on selected nuget assemblies can dynamically load the source from GitHub and even allow you to set breakpoints and debug into the downloaded files.

4 – .Net core

.Net core is the re-written base class library which works on Windows, Linux and OSx. While we weren’t given ship dates yet, they did make an effort to show off the performance improvements that the revised core contains. ASP.Net Core is ~4x the speed of Node and EF Core is 70-80% (faster on bulk inserts over the wire) as compared to EF6. During the day 2 keynote, they demonstrated Age of Ascent running Azure App Fabric with ASP.Net Core to support a game of up to 50000 concurrent users and >2 million requests per second. Check out the Age of Ascent videos for some truly impressive performance examples.

3 – UBow - Bash via Ubuntu on Windows

Ok, so it’s not called UBow (yet) but with your help dear readers, maybe we can get this term coined as it’s surely better than Bash on Ubuntu on Windows. They made it clear that this is not Ubuntu (Linux) running in a VM on windows, but is true user mode Ubuntu bash running natively on Windows. This 20 minute video goes over some of the capabilities that this offers the Windows developer better than anything I could explain here.  In addition to Ubuntu, Redhat was on stage with the .Net team to demonstrate .Net running via Mono on Redhat Linux and even has a developer page for that you can sign up to get more information from.

2 – Conversations as a Platform

This item was somewhat confusing and took up 1/3 of the day one keynote, but is a bit clearer now. Essentially, this Microsoft’s attemt to let bots run wild across all devices and works in conjunction with their Bot framework, natural language processing(LUIS) and machine learning tools to enable you to build automated chat bots that can register for certain key phrases and facilitate a conversation including the ability to order a pizza or reserve a hotel room directly from chat (in Skype). Since this was relatively new, we’ll see how much love it gets going forward and if it is a fad or will simply fade away.

1 – Xamarin

Since Microsoft acquired Xamarin, everyone was expecting some announcement regarding the purchase and Microsoft did not disappoint. They lead off the day to keynote with the announcement that Xamarin would now be FREE for all users including support for the Visual Studio Community edition thereby eliminating the main hurdle to adoption. Xamarin was previously $1000/developer/platform which added up quickly. With this change, I see many more people getting involved in this option for mobile development. In addition, the Mono framework was relicensed to the more open MIT version allowing for reuse and open sourcing the remaining proprietary APIs. Beyond this announcement, the only new features that were demonstrated focused on the new Workbooks (markdown based documentation which can include debuggable code samples) and a visual inspector for debugging XAML UI layout issues. I suspect that they were holding off on other announcements for their Xamarin Evolve 2016 later this month.

0 – Helping the blind to see

imageSince I used Option Base 0 in this post, I reserve the right to include one more item which received the greatest applause for the entire conference, even bringing some of the attendees to tears. They ended the opening keynote with a video showing how one of their developers used the machine learning and cognative services capabilities to literally enable the blind to see the world around them. If you only watch one video, make sure to take 2 minutes and watch this video. Don’t forget to have a tissue handy in case the emotions get the best of you.

Posted on - Comment

Roslyn resources

imageToday 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:

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.

Posted on - Comment
Categories: VB - C# -

Microsoft Connect conference links and key takeaways

imageThis 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.

imageCross 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 from Visual Studio.

Cross platform C++ – Microsoft also added support for writing cross platform C++ if that’s your language of choice or you need access to device native features that aren’t available using the other platforms.

•Andriod emulator – The official Andriod emulator from Google is widely acknowledged as being quite slow and problematic. Microsoft now offers an alternative emulator based on HyperV similar to their own Windows Phone emulator. – Not only can you deploy your ASP.Net vNext applications on Linux or OS-X, you can now use your favorite text editor to create the code in Sublime/Emacs/etc. Instructions on how to hook up your favorite editors are available at

Visual Studio 2015

Instead of detailing the changes in VS 2015, below is a quick summary of some of the more interesting ones that I found. Drill into each hyperlink below for details on the feature you’re interested in.

  1. New compiler for C#/VB(. Net Compiler Platform) (what’s new vids)
  2. New language features
  3. Code focused IDE enhancements
  4. Lightbulb refactorings (extensible)
  5. PerfTips
  6. Memory usage tool
  7. LINQ/Lambda expressions in debug/watch windows
  8. Bigger and filterable error list
  9. WPF roadmap tooling and debugging enhancements for WPF
  10. Auto generate smart unit tests
  11. .Net Native

VS OnlineimageWhether you prefer TFS or Git, you can use VS Online. I’m not going to try to summarize the new features here since they stated that they update the site every 3 weeks. By the time you read this, there is a high likelihood that they may have already added more features.

Application insights – One new feature worth mentioning is the Application Insights that can allow you to add analytics and diagnostic monitoring to your app and offer a helpful dashboard to use directly inside of VS Online.

Did you watch the Connect sessions and noticed something important that I missed here? Feel free to let me know what you thinq below.

Posted on - Comment
Categories: Visual Studio -

ThinqLinq Samples on Github

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?

Posted on - Comment
Categories: C# - Entity Framework - JavaScript - LINQ - Rx - RxJs - SignalR - VB - WinRT - WP7 -