LINQ and Entity Framework Errors

Possibly save 4 hours of your time: When working with LINQ and Entity Framework 6, you may encounter the following errors.

  1. Only parameterless constructors and initializers are supported in LINQ to Entities.
  2. The ‘Distinct’ operation cannot be applied to the collection ResultType of the specified argument. Parameter name: argument
    This happens on Union operation of two entities with different schemas
  3. An error occurred while preparing the command definition. See the inner exception for details.
    This happens on Union operation of Concat operation on two entities with different schemas
  4. System.InvalidOperationException : The specified cast from a materialized ‘System.Int32’ type to the ‘System.String’ type is not valid.
    This happens on Union operation of Concat operatoin on two entities with different schemas

First one is obvious, you must have parameterless constructor when selecting new object transformation.

from i in dbcontext.TableName

select new EntityObjectName() { ID = i.ID }

Second and Third one is similar. When you select new object with properties that are in array form, you may get these errors. One idea would be to simply change this to IEnumerable or IList.

Fourth, this one took me awhile to figure out. It basically happens when the property being transformed came from two different tables and have different data types on the database backend. A simple fix is to check for null and turn into empty string on both the union/concat select. This seems to fix the issue.

from i in dbcontext.TableName

select new EntityObjectName() { Value = i.Value ?? “” }


from j in dbcontext.TableName2

select new EntityObjectName() { Value = j.Value2 ?? “” }

Make methods accessible to unit tests

Possibly save 1 hour of your time: If you would like to create unit tests for your library, you can create the unit tests in a different project to separate the tests and the code.

1. Typically, a good convention is use the same project name and append dot Tests. i.e. MyProjectName.Tests

2. Then you will need to add reference to your project.

3. To access the methods in your project from the tests project, you will need to mark them internal access unless it is already marked as public access.

4. In the AssemblyInfo.cs of your project, you need to add the following the make your project visible to your tests project.

   [assembly: InternalsVisibleTo(“YourProjectName.Tests”)]

There are other methods of testing your protected or private methods, but I find this solution the easiest to configure and maintain.

Publish a Nuget package to your own server

Possibly save 1 hour of your time:

1. Download Nuget and set environment variables path where the nuget.exe is found

2. Open command prompt and cd to the .csproj folder.

Run the following command to create a nuget specification template.

nuget spec

3. Open command prompt. Then change directory to go to the folder where the YourProjectName.csproj is. Then enter the following to package your csproj.

    nuget pack YourProjectName.csproj

Next, you will push this to your server location

    nuget push YourProjectName. -Source \\serverLocation

The version number is based on your assembly info file.

Resource Link:



Execute PowerShell Script

Possibly save 1 hour of your time:

  1. Use ./ (dot and slash and PowerShellScriptName) to run the command in the current path folder. You don’t need to enter .ps1

Often times you will type cd to change directory to the folder where the PowerShell script exists. Then you type the script name and it gives you error that the command is not recognized.

PS C:\Scripts> HelloWorld

PS C:\Scripts> ./HelloWorld

Pass Function as parameter to Method

Possibly save 3 hours of your time: There are times when you would like to refactor code but realize a piece of that code is different based on the caller. The caller may also used different parameters, but return the same object.


  • Caller1 calls Method1 with parameterA and returns objectZ
  • Caller2 calls Method2 with parameterB and returns objectZ
  • Method1 and Method2 has same code except for the piece of code that returns objectZ


  • code…
  • some code that uses parameterA and returns objectZ
  • code…


  • code…
  • some code that uses parameterB and returns objectZ
  • code…


You can use Func delegate as parameter. Func can return an object. The example below shows the use of this Func delegate as parameter.

You can also use Action as paramater, but Action does not return an object.

First, refactor the Method1 and Method2 that takes in a Func delegate with different parameters.

Method(Func<string, objectZ> methodToExecute)


same code…

var objectZ = methodToExecute(null); //arguments here do not matter

same code…


Second, create the method that takes in parameter A or B and returns objectZ.

         objectZ MethodToExecute(parameterA, parameterB)


if (paramaterA != null)

return ObjectZ(parameterA);

return ObjectZ(parameterB);


Finally, caller will pass in to this method the function to execute.

Method( (x) => methodToExecute(parameterA) )

Generic Method

Possibly save 2 hours of your time: There are times when you want to refactor your method. This happens when you have duplicate code that acts on two similar objects.


  • You have ObjectA
  • You have ObjectB
  • You have Method1(ObjectA)
  • You have Method2(ObjectB)
  • Both Method1 and Method2 has exact code that operates on the similar Object.


  • First, make ObjectA and ObjectB inherit same interface IObject

                class ObjectA : IObject

                class ObjectB : IObject

  • Then, change your Method1 and Method2 to

                 Method<T>(T object) where T : IObject

  • Third, refactor your method 1 and 2 so that your caller will just call this method with ObjectA or ObjectB as parameter





Windows Authentication – user cannot login

Possibly save 2 hours of your time: When your new application uses windows authentication, typically you will authorize against a specific windows security group. Once you launch your application and users are starting to access this website, but they try to login with their windows credentials but still have no access.

First, go to the website on IIS, right click on the website and choose Edit Permissions. Then go to Security tab and click on Edit. Then Add the windows security group with the Read permission and any other required permissions.

Second, you can get them to logout of their windows machine and then log back in.

Third, if that doesn’t work, then you can send them the following instructions and have them check if they are part of an assigned windows security group.

  • Run command prompt
  • Type: GPRESULT /R > gp.txt
  • Type: notepad gp.txt
  • Search for windows groupname in the txt file

On Windows 10, use the following command instead:

  • Type: GPRESULT /USER username /V > gp.txt
  • Type: notepad gp.txt


Clear web application server cache

Possibly save 2 hours of your time: Most applications have cached data. When business updates reference data or some cached data, one needs to clear the cache directly so that the cache can be refreshed. There are multiple ways on how to clear the web server cache.

One way is to recycle application pool for that website. When application pool starts again, the cache has been cleared.

  • Open Internet Information Services (IIS), under the server connection click on Application Pools. Then you will see the list of application pools. Find the one that is assigned to your application. Then right click on the application pool and choose Recycle.
  • There are ways to use Microsoft.Web.Administration.ServerManager.ApplicationPools.Recycle() to recycle the application pools; however this requires an elevated IIS administrator rights. So this is not considered since the target user are business administrators.

Second way is to drop an app_offline.htm file. And then remove it right after. This file will put the application offline and you can customize this file with your company logo embedded as svg image (see bullet point).  This is usually used during maintenance. This file triggers application pool for this website to stop and then when this file is removed, the application pool will start with previous cache cleared. Your application will handle the refresh of the cache when it has been cleared. See below for this code.

  • background: url(data:image/svg+xml;base64,[…image binary goes here…])

Third way is an application specific solution in which the application will provide the ability to clear the cache.

For local development, run iisreset on command prompt will recycle all application pools.

public Exception RestartApplicationViaOffline(string offlineFile, string targetLocation)
Exception exception = null;
var appOffline = “app_offline.htm”;
if (string.IsNullOrEmpty(offlineFile))
throw new Exception($”app_offline.htm [actionId] is not valid at {offlineFile}.”);

if (!offlineFile.EndsWith(appOffline))
offlineFile = Path.Combine(offlineFile, appOffline);

if (string.IsNullOrEmpty(targetLocation))
throw new Exception($”app_offline target folder [target] is not found at {targetLocation}.”);

if (!targetLocation.EndsWith(appOffline))
targetLocation = Path.Combine(targetLocation, appOffline);

// Will overwrite if the destination file already exists.
File.Copy(offlineFile, targetLocation, true);


catch (Exception ex)
exception = ex;

return exception;

Technology stack:

  • Windows Server 2012 R2
  • Internet Information Services (IIS Version 8.5.9600.16384)

Trigger and execute SQL Server Agent Job

Possibly save 2 hours of your time:

Recently, I was asked to find a generic way for non-technical administrators to be able to trigger or execute SQL Server Agent Job on their own on demand.

One solution is to build a windows authenticate administration website within our network domain. This web application will use windows authentication authenticate against specific windows user/group that has permission to trigger/execute a back end job.

public Exception ExecuteSqlAgentJob(string connectionString, string jobName)
Exception exception = null;

var dbConn = new SqlConnection(connectionString);
var execJob = new SqlCommand();
execJob.CommandType = CommandType.StoredProcedure;
execJob.CommandText = “msdb.dbo.sp_start_job”;
execJob.Parameters.AddWithValue(“@job_name”, jobName);
execJob.Connection = dbConn;

using (dbConn)
using (execJob)
catch (Exception ex)
exception = ex;

return exception;

The application pool identity that runs this application should have access to:

  • the database which the SQL job targets
    • Go to specific database >> Security >> Users >> add login
  • msdb database
    • Go to msdb database >> Security >> Users >> add login
    • And give it SQLAgentOperatorRole membership
    • This gives permission to execute dbo.sp_start_job
    • Note: Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.

Mock HttpRequest

Possibly save 2 hours of your time:

We often want to unit test our controller layer with the input request URL. Here is an example of unit test that can mock the HttpRequest within the HttpContext constructor that you can set on your controller.

private HttpContextBase MockCurrentContext()
var sb = new StringBuilder();
TextWriter w = new StringWriter(sb);
var context = new HttpContext(new HttpRequest(“”, “ “, “”), new HttpResponse(w));
return new HttpContextWrapper(context);

public void OneTimeSetUp()
controller = new YourController();
controller.ControllerContext = new ControllerContext(MockCurrentContext(), new RouteData(), controller);