Month: March 2016

Display SQL for LINQ to Entities

If you’re fluent in SQL and want to display SQL for LINQ to Entities in your .NET application, there are ways to do it. It heavily depends on the version of Entity Framework you’re using

I was looking for a way to display the SQL behind a LINQ to entities query in the immediate window. Lots of sites have some code, except they didn’t seem to work for me ☹.

In Entity Framework version 6.0, you can use the following code. You have to make sure that you don’t have your LINQ query ending with anything that will cause the query to execute, such as .ToList().

((System.Linq.IQueryable) %queryvar%).ToString()

You can use this right in the immediate window. Here is another that I found on the web that didn’t work for me for whatever reason. I think ObjectQuery is not in that library any longer:

((System.Data.Objects.ObjectQuery) %queryvar%).ToTraceString()

If you don’t mind changing your code, there is a way to display the query in the Output windows (after it executes) with this code Added before your SaveChanges().

context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

For Entity Framework Core, version 6.x, they have added a DebugView property in Visual Studio so that you can inspect the query at a stop point. If you want to get the query in code, you can use this:

string _myQuery = _entityrows.ToQueryString();

Keep on LINQing!

Multiple App.Config files

Visual studio has had the ability to have multiple Web.config files for some time now. What about the need for multiple App.config files. Many posts online talk about Unit.Test apps, which is a great application for this, but some of us old guys still automate business processes and could really use multiple app.config files for deploying Windows Services too.

The technique here is is not the same as a Web.config transformation. In a transformation, you only put the exceptions in your sub Web.config’s using a replacement line in your Web.Release.config such as:

<appSettings xdt:Transform="Replace">

This technique is different, in that you must provide a complete copy of the .config file that you want for each type of build. While this is a maintenance issue, I highly suggest that you put comments in your .configs to remind folks to add their new or modified entries to each config. I think this is less of an issue then continually releasing a Windows Service that has the wrong file paths for your file import service.

You’ll need to edit your .csproj file manually. Make sure you don’t accidentally add this to the .csproj.vspscc file.  In many version of Visual Studio, you’ll already see a section at the bottom that is commented out.

<!-- To modify your build process, add your task inside one of the targets below and uncomment it.
Other similar extension points exist, see Microsoft.Common.targets.
<Target Name="BeforeBuild">
</Target>
<Target Name="AfterBuild">
</Target>
-->

Replace the “AfterBuild” section with the following entry, un-commenting it of course:.

<Target Name="AfterBuild">
<Delete Files="$(TargetDir)$(TargetFileName).config" />
<Copy SourceFiles="$(ProjectDir)\Config\App.$(Configuration).config"
DestinationFiles="$(TargetDir)$(TargetFileName).config" />
</Target>

Now open your project and create a “Config” folder to match the entry we just put in the .csproj file. If you already have an App.config file, copy it to the \Config folder and rename it App.Debug.config. Then copy it to the other build types that you have, for instance App.Release.config and App.UAT.config.

Edit each one and remove any commented entries you have that are not appropriate for the particular release that you’re building.

I recommend putting something like this in the top of each .config file:

<!-- This file is copied and renamed by the 'AfterBuild' MSBuild task -->

<!-- Make sure that any changes you make to this file you also propagate to
the other file in the \Config folder -->