Find Duplicate Database Entries with LINQPad

A co-worker and I were chatting about a code problem he was having that was likely due to duplicate entries in a database table. He thought that records in the table were unique based on two columns, but that didn’t seem to agree with what was happening in code. He wanted to write a SQL query to identify any duplicates, but he didn’t know how to do it. He doesn’t write a lot of SQL and wasn’t comfortable with it. He uses LINQ everyday, though, so I suggested he do it through one of my favorite tools: LINQPad.

Regardless of whether you’re doing it in SQL or LINQPad, the approach is the same: group by the fields and filter to show only groups with more than one item.

So, let’s do that in LINQPad. Here are the quick-steps to get you caught up to the point of writing your query:

  1. Open LINQPad
  2. Configure connection
  3. Configure query to use the connection
  4. Write query

There are two things to keep in mind when writing queries in LINQPad. First, the table names will be pluralized. “SomeObject” becomes “SomeObjects.” Second, field names will be case-sensitive and always start with a capital letter. Now let’s get to business…

SomeObjects.GroupBy(x => x.FirstColumn + "|" + x.SecondColumn)
    .Where(x => x.Count() > 1)

Boom! That’s all there is to it. I concatenate the columns to create a group key, and filter the results to only show groups with more than one item.

Advertisements

Remote Desktop Over Wifi

I’ve been having a problem for the past few months with Remote Desktop Connection when connecting through the wireless network at work. I didn’t think it was a configuration problem because I had no problem connecting through the wired connection. Actually, I could connect through the wireless adapter with no problem, too. As soon as I logged in, though, the session would freeze, and I’d get disconnected.

Today, I was googling around looking for a solution, and I came across this forum post. One responder said that they’d had the same issue, and it was a problem with the Wifi network profile. It was created for one user and not for everyone. I had seen this solution before and had verified that the network availability for my wireless connection was “All users.” The responder went on to say that it needs a different profile even when you log on with the same account.

I decided to give it a shot. I logged in through Remote Desktop Connection using the computer’s wired IP and found that the wireless adapter was not connected! I reconnected from the remote session, and guess what? Now everything works! I’m having trouble wrapping my brain around why the wireless adapter would disconnect when I log in remotely, but I’m happy to have it working.

Make the Invisible More Visible

A co-worker sent me a link to an article from the Embedded Linux Conference (ELC) where the author spoke with Robert Rose of SpaceX about lessons learned, primarily related to software development. The article references an essay titled Make the Invisible More Visible, from the book 97 Things Every Programmer Should Know. I really like some of the points the essay makes about the danger of invisibility in the development process. I particularly like these two points:

  • If you’re 90% done and endlessly stuck trying to debug your way through the last 10% then you’re not 90% done
  • Lack of visible progress is synonymous with lack of progress

These two statements really go hand in hand. In my experience, developers will often say they’re 80 or 90 percent done when they feel like they’re close to done (or that they should be close to done). In many cases, “90% done” is an arbitrary measure of progress, manufactured to give the illusion that things are on track. Typically in these situations, we’re looking at projects with loosely-defined requirements and insufficient planning. The developer was given a task and hit the ground coding without thinking about the big picture.

Without understanding what’s left, developers can’t be held accountable. When I ask a developer whose been working on a project for two weeks how far along they are, it seems pretty good when they report 80 percent. When I come back a week later and ask for another update, maybe they’re at 90 percent. “Progress has slowed down,” I think to myself, “but at least we should be done next week.” Next week comes along, and now we’re up to 95 percent. This is a perfect illustration of the points above.

In this scenario, the developer reported being at 80% when clearly they weren’t. There was no visibility to what they had accomplished or had yet to accomplish, so the measure was based on feelings—which proved to be completely inaccurate. But how can this be avoided? Here’s my advice: know what you’re coding before you start coding. I’m not talking about classes and methods, though; I’m talking about features and requirements. If you follow agile methodologies, you should be familiar with the concept of user stories. Before you start working on a project, take the time to understand and document the stories required to complete the project. This brings visibility to the project as well as giving you a calculation-based measure of completion percentage. There’s no question about what’s been accomplished and what’s left. As long as you keep completing stories, consistent progress is all but guaranteed.

But what if it’s just a lot of stuff that comes up during testing? If you’re writing good stories with solid completion criteria, you shouldn’t be finding a lot during testing. Additionally, you should be creating automated unit tests to verify what you’re doing and what you’ve done. The automated tests ensure that functionality added early in the development process isn’t affected by functionality added later.

Other features keep popping up; how do I manage those? This depends on the source of the new features. If it’s something that you missed, you just need to be aware of this and improve over time. With practice, you’ll get better and learn what types of things you commonly miss. Peer review can really help with this, too. Sit down with a buddy and walk them through what you’ve got, or work together to come up with the requirements from the beginning. With two of you, you’ll be less likely to miss something. If the source is external—like bosses and customers—your best bet is open, honest communication. This is textbook scope creep, so let them know what the impact of their request will be. “Sure, we can do that, but it’s going to take us an extra two weeks.” If it’s not in the budget, you have to push back or barter. “I’m sorry, but that’s out of scope, and we don’t have the capacity to add that requirement” or, “We can only implement that functionality if we leave out these other two things.”

Mail, Calendar, People, and Messaging App Update

When I hopped on my Surface this morning, I noticed there was an update available to the Mail, Calendar, People, and Messaging app. I love getting updates, particularly to core system apps that I use every day. I wanted to know what actually changed, though!

I headed to the internet and found this post from on the Windows Experience Blog. It’s got the information I was looking for, but it wasn’t in the format I wanted. I just wanted a bulleted list, not a narrative about how apps can be used that I need to read through to pick out changes. So, I’ve done the legwork for you—here’s what’s new.

  • Filter mail to see only unread emails
  • Create and delete mail folders
  • Select all mail items in a folder and move or delete them
  • Flag emails
  • “Smart contact suggestions” in the To line when composing emails
  • Draft messages show at the top of inbox
  • Add, edit, and delete hyperlinks and edit numbered or bulleted lists in emails
  • Search server email messages
  • View Exchange details for contacts
  • Solid blocks of color removed from calendar; now colored bar is used to determine which calendar
  • “Work week” view added to calendar
  • Forward invitations and email all attendees from calendar
  • Improved navigation in people app by swiping from the top
  • Filter “What’s New” by source (e.g., Facebook, Twitter)

The article makes it sound like there may be more than this, but these were the changes that I’m now aware of. Let me know if there’s more that I missed.

Refactoring If-Else and Switch Statements

The notion of code smells is often brought up when talking about refactoring. Code smells are somewhat of a gut-check for your code; if you have a “smelly” block of code, it probably needs some refactoring. So, code smells are essentially bad patterns. My favorite list of code smells can be found here. In reviewing this list, there’s one smell that was surprising to me: switch statements.

The case against switch statements is that they are a violation of the DRY principle. A complex If-ElseIf-ElseIf-etc.-Else statement exhibits the same issue. It’s the same pattern repeated over and over. But it’s so simple, how can it be handled differently? There are two techniques that I recommend: polymorphism and delegate mapping.

Refactor Using Polymorphism

Polymorphism is a perfect solution for many switch statements. In the following sample code, we have a Mammal class with a property that indicates what type of mammal is represented. The SayWhat method uses this property to decide which output to produce.

public class Mammal
{
	public MammalType MType { get; set; }
	
	public void SayWhat()
	{
		switch (MType)
		{
			case MammalType.Human:
				Console.WriteLine("Hello!");
				break;
			case MammalType.Dog:
				Console.WriteLine("Bark!");
				break;
			case MammalType.Cat:
				Console.WriteLine("Meow!");
				break;
		}
	}
}

In order to use polymorphism, we must first refactor the Mammal class. Rather than using a property to represent type, we can create additional types. (Makes sense, doesn’t it?) Making the SayWhat method abstract in our base class will force the new derived classes to provide their own implemtnation.

// refactored!
public abstract class Mammal
{
	public abstract void SayWhat();
}
public class Human : Mammal 
{
	public override void SayWhat()
	{
		Console.WriteLine("Hello!");
	}
}
public class Dog : Mammal 
{
	public override void SayWhat()
	{
		Console.WriteLine("Bark!");
	}
}
public class Cat : Mammal 
{
	public override void SayWhat()
	{
		Console.WriteLine("Meow!");
	}
}

Refactor Using Delegate Mapping

Another scenario that I’ve run into is one where the code path is determined by hierarchical conditionals. Creating new objects in order to apply the polymorphic solution may not be appropriate. This is where we can use delegate mapping to solve the problem.

Consider this code:

public class Mammal
{
	public bool IsSleeping { get; set; }
	public bool IsEating { get; set; }
	public int Age { get; set; }
	public bool IsDrunk { get; set; }
	
	public void SayWhat()
	{
		if (IsSleeping)
		{
			Console.WriteLine("Zzz");
		}
		else if (IsEating)
		{
			Console.WriteLine("Nom nom nom");
		}
		else if (Age > 21 && IsDrunk)
		{
			Console.WriteLine("*hiccup*");
		}
		else if (IsDrunk)
		{
			Console.WriteLine("*barf*");
		}
	}
}

We can build a delegate map using Predicates and Actions. Here’s what the refactored code might look like

// refactored!
public class Mammal
{
	public bool IsSleeping { get; set; }
	public bool IsEating { get; set; }
	public int Age { get; set; }
	public bool IsDrunk { get; set; }
	
	public void SayWhat()
	{
		var delegateMap = new Dictionary<Predicate<Mammal>, Action>
		{
			{ x => x.IsSleeping, () => Console.WriteLine("Zzz") },
			{ x => x.IsEating, () => Console.WriteLine("Nom nom nom") },
			{ x => x.Age >= 21 && x.IsDrunk, () => Console.WriteLine("*hiccup*") },
			{ x => x.IsDrunk, () => Console.WriteLine("*barf*") },
		};
		
		delegateMap.First(x => x.Key(this)).Value();
	}
}

A few notes about this specific implementation:

  • It uses the LINQ First method to find and execute the first Action
  • It will produce an InvalidOperationException if no match is found
  • If more than one match is found, only the first delegate will be executed

Microsoft Flash Policy Reversed in Windows 8 and RT

Hooray! This week, Microsoft reversed its very conservative Flash policy for Windows 8 and Windows RT. The previous policy required Flash developers to certify their applications and have them added to Microsoft’s Compatibility View List. This was frustrating to me as a Surface RT early-adopter. Many Flash sites didn’t work. And I’m not talking about small-time Flash sites from random developers. I’m talking about large-scale, reputable sites like LogMeIn.com.

Luckily, Microsoft decided to convert its Compatibility View List from a white-list to a black-list. Now, all Flash websites will work in IE10 in Windows 8 and RT unless they have been explicitly banned.

In the long-term, I don’t think this is big news, as I see Flash being replaced slowly and surely by HTML5. I definitely see this as a win for RT in the short-term, though. Not being able to install alternate browsers or software really puts RT users at the mercy of Microsoft, and while it obviously had the best of intentions, the certification process was causing more harm than good. Kudos to Microsoft for realizing this and easing the restriction. As a Surface RT user, I appreciate it!

Read more about the policy change here.

Partially Mock Static Classes With Rhino Mocks

One of my favorite unit testing techniques is using partial mocks with Rhino Mocks. This allows me to substitute logic or assert function calls in my unit tests, making it simple to write concise, targeted tests. Here’s a quick example that demonstrates the basic concept.

public class Animal
{
	public void EatFood()
	{
		GetSleepy();
	}

	public virtual void GetSleepy()
	{
		throw new Exception("I break tests!");
	}
}

[TestClass]
public class AnimalTest
{
	[TestMethod]
	public void EatFood_GetsSleepy()
	{
		// Arrange
		var target = MockRepository.GeneratePartialMock<Animal>();
		target.Expect(x => x.GetSleepy());

		// Act
		target.EatFood();

		// Assert
		target.VerifyAllExpectations();
	}
}

A partial mock is essentially just a derived class that allows you to provide alternate implementations by overriding virtual methods. The problem with this is that it relies on inheritance, which makes it not feasible for static classes. The best way I’ve found to get the desired behavior is to do a bit of refactoring using interfaces and static properties. I can then inject mock objects to manipulate (or avoid) certain methods.

For this example, consider the following static class. Assume we want to write a test for the method SavePassword, but a method that it invokes, SaveToSecretLocation, contains some logic that is not conducive to unit testing.

public static class PasswordSaver
{
	public static void SavePassword(string description, string username, string password)
	{
		SaveToSecretLocation(description, username, password);
	}

	private static void SaveToSecretLocation(string description, string username, string password)
	{
		throw new Exception("I break tests!");
	}
}

Step one is to define an interface for the method or functionality that’s causing us problems. In this case, it’s the method SaveToSecretLocation. I’m going to create an interface called ISecretLocationAccess that has a single method whose definition matches our static class’s SaveToSecretLocation.

public interface ISecretLocationAccess
{
	void SaveToSecretLocation(string description, string username, string password);
}

We obviously need a class that implements this interface in order to use it, and we have a few choices. One option is to create a new class. Speaking strictly in terms of object-oriented design principles, this will probably be your best bet. If creating a new object isn’t an option for you for some reason–the code is too spaghetti’d or something–you can convert your static class to be instantiable. I’m going to use the second method in this example.

Since I’m going to convert my static class to be instantiable, I’m going to do two things: replace static with sealed and add a private constructor. Both of these measures will prevent developers from accidentally–or intentionally–instantiating the class.

public sealed class PasswordSaver
{
	private PasswordSaver() { }
	
	public static void SavePassword(string description, string username, string password)
	{
		SaveToSecretLocation(description, username, password);
	}

	private static void SaveToSecretLocation(string description, string username, string password)
	{
		throw new Exception("I break tests!");
	}
}

Now that our class is instantiable, we’ll implement the interface. Note that I’m implementing it explicitly so that it can co-exist with the original static method.

public sealed class PasswordSaver : ISecretLocationAccess
{
	private PasswordSaver() { }
	
	public static void SavePassword(string description, string username, string password)
	{
		SaveToSecretLocation(description, username, password);
	}

	private static void SaveToSecretLocation(string description, string username, string password)
	{
		throw new Exception("I break tests!");
	}
	
	void ISecretLocationAccess.SaveToSecretLocation(string description, string username, string password)
	{
		throw new NotImplementedException();
	}
}

In order to re-route the logic to the interface version of the method, we need an instance of the interface. We’ll add a static property for our instance, and I’m using lazy initialization to prevent the possibility of a null reference. Note that I’ve made the property private because I don’t want developers accessing this object directly. If you don’t need the original method to remain as static, you could simply remove the static keyword from that method to implement the interface implicitly–I did that in the final solution at the bottom.

public sealed class PasswordSaver : ISecretLocationAccess
{
	private PasswordSaver() { }
	
	private static ISecretLocationAccess _secretLocationAccess;
	private static ISecretLocationAccess SecretLocationAccess 
	{ 
		get
		{
			return _secretLocationAccess ?? 
				(_secretLocationAccess = new PasswordSaver());
		}
		set
		{
			_secretLocationAccess = value;
		}
	}
	
	public static void SavePassword(string description, string username, string password)
	{
		SaveToSecretLocation(description, username, password);
	}

	private static void SaveToSecretLocation(string description, string username, string password)
	{
		throw new Exception("I break tests!");
	}
	
	void ISecretLocationAccess.SaveToSecretLocation(string description, string username, string password)
	{
		throw new NotImplementedException();
	}
}

Now that we have access to an instance of the interface, we can relocate our logic from the original method to the interface method and re-route execution through the property.

public sealed class PasswordSaver : ISecretLocationAccess
{
	private PasswordSaver() { }
	
	private static ISecretLocationAccess _secretLocationAccess;
	private static ISecretLocationAccess SecretLocationAccess 
	{ 
		get
		{
			return _secretLocationAccess ?? 
				(_secretLocationAccess = new PasswordSaver());
		}
		set
		{
			_secretLocationAccess = value;
		}
	}
	
	public static void SavePassword(string description, string username, string password)
	{
		SaveToSecretLocation(description, username, password);
	}

	private static void SaveToSecretLocation(string description, string username, string password)
	{
		SecretLocationAccess.SaveToSecretLocation(description, username, password);
	}
	
	void ISecretLocationAccess.SaveToSecretLocation(string description, string username, string password)
	{
		throw new Exception("I break tests!");
	}
}

At this point, our class is sufficiently refactored to support the desired testing behavior. All that’s left is for us to inject a mock object in our test. We create a mock object using Rhino Mocks, and since the property is private, we’ll use reflection to inject it. Note the use of TestCleanup to ensure we don’t leave any spent mock objects for the next test–very important!

//
// test class
//
[TestClass]
public class PasswordSaveTest
{
	private ISecretLocationAccess _mockSecretLocationAccess;
	
	[TestInitialize]
	private void TestInitialize()
	{
		_mockSecretLocationAccess = MockRepository.GenerateMock<ISecretLocationAccess>();
		InjectSecretLocationAccess(_mockSecretLocationAccess);
	}
	
	[TestCleanup]
	private void TestCleanup()
	{
		InjectSecretLocationAccess(null);
	}
	
	private void InjectSecretLocationAccess(ISecretLocationAccess secretLocationAccess)
	{
		typeof(PasswordSaver)
           .GetProperty("SecretLocationAccess", BindingFlags.NonPublic | BindingFlags.Static)
           .SetValue(null, secretLocationAccess, null);
	}
	
	[TestMethod]
	public void SavePassword_SavesPasswordToSecretLocation()
	{
		// Arrange
		const string description = "description";
		const string username = "username";
		const string password = "password";
		_mockSecretLocationAccess.Expect(x => 
			x.SaveToSecretLocation(description, username, password));
			
		// Act
		PasswordSaver.SavePassword(description, username, password);
		
		// Assert
		_mockSecretLocationAccess.VerifyAllExpectations();
	}
}
//
// final solution
//
public sealed class PasswordSaver : ISecretLocationAccess
{
	private PasswordSaver() { }
	
	private static ISecretLocationAccess _secretLocationAccess;
	private static ISecretLocationAccess SecretLocationAccess 
	{ 
		get
		{
			return _secretLocationAccess ?? 
				(_secretLocationAccess = new PasswordSaver());
		}
		set
		{
			_secretLocationAccess = value;
		}
	}
	
	public static void SavePassword(string description, string username, string password)
	{
		SecretLocationAccess.SaveToSecretLocation(description, username, password);
	}
	
	void ISecretLocationAccess.SaveToSecretLocation(string description, string username, string password)
	{
		throw new Exception("I break tests!");
	}
}

public interface ISecretLocationAccess
{
	void SaveToSecretLocation(string description, string username, string password);
}

Software Entropy

Software Entropy

Whenever I talk about broken windows with respect to software development, I find myself directing people to this page. It’s an excerpt from the book The Pragmatic Programmer, which I’d definitely recommend checking out.

I like this link because it makes a pair of great points:

  1. Badness invites badness. A little bit of ugliness can quickly lead to the mindset of “All the rest of this code is crap, I’ll just follow suit.”
  2. Clean code encourages good habits. Nobody wants to be the one who introduces icky code.

So what’s the takeaway? First, you should always strive to create good, clean code. Second, and perhaps more importantly, apply the if-you-see-a-piece-of-garbage-pick-it-up principle. It’s much easier to clean up small messes now than it will be to deal with big messes later.

The Way the World Should Work

Yesterday morning, I was waiting in line at Starbucks to grab a quick coffee on my way to work. The guy in front of me paid with his phone, but his balance didn’t cover the cost. He stepped aside and started the process of reloading on his Starbucks app. He seemed like a nice enough guy; the baristas knew him well enough to suggest his drink before he ordered, and he was apologetic about his mistake once he learned that he had insufficient funds. I’m not sure why he didn’t just pull out a credit card to pay for the rest, but that’s not the point. I think he panicked and just wasn’t thinking clearly, as silly as that may sound. But, I digress.

As he stepped aside, I stepped up to the register, ordered my drink, and offered to pick up the rest of his bill. It was just a dollar, and it seemed like a good random-acts-of-kindness opportunity. Plus, I figure I’ll inevitably find myself in that same position at some point. I know I’d appreciate it if the person behind me in line just floated me the buck instead of impatiently glaring at me while the barista waits and eventually decides to void the transaction in order to ring up the next person. And so I suggested that she just add my coffee to the order, and I’d pay for the rest.

The Starbucks employee let me pay for the rest of his bill, but then she did something unexpected and gave me my coffee for free. I definitely wasn’t hoping for or expecting to get anything–I was just trying to help a guy who needed a dollar. I think it was just her way of saying, “Hey, thanks for being a good person.” It was a nice surprise, and I left Starbucks feeling like this is the way the world should work.

It really felt like wins all around, too. The guy who ran out of money got helped by a stranger and an unintentionally-cheaper drink. I got the satisfaction of helping out a stranger and an unintentionally-cheaper drink. And, finally, Starbucks gets more of my business for acknowledging, supporting, and rewarding a good deed at the expense of a cup of coffee. Thanks for that!

Definition of a Good Unit Test

I’m constantly encouraging developers around me to embrace test-driven development and automated unit testing. However, there’s one very important thing I’ve neglected to include in my evangelizing: a definition for what constitutes a good unit test.

A big problem that I think a lot of developers run into is that they’re writing tests but aren’t realizing any value from them. Development takes longer because they have to write tests, and when requirements change it takes longer because they have to refactor tests. Maintenance and warranty work is slower, too, because of the additional upkeep from failing tests created with every little change.

These problems mostly exist because of bad unit tests that are written due to insufficient knowledge of what makes a good test. Here’s a list of properties for a good unit test, taken from The Art of Unit Testing:

  • Able to be fully automated
  • Has full control over all the pieces running (Use mocks or stubs to achieve this isolation when needed)
  • Can be run in any order  if part of many other tests
  • Runs in memory (no DB or File access, for example)
  • Consistently returns the same result (You always run the same test, so no random numbers, for example. save those for integration or range tests)
  • Runs fast
  • Tests a single logical concept in the system
  • Readable
  • Maintainable
  • Trustworthy (when you see its result, you don’t need to debug the code just to be sure)

This is a great list that you can use to gut-check your unit tests. If a test meets all of these criteria, you’re probably in good shape. If you’re violating some of these, refactoring is probably required in your test or in your design, particularly in the cases of Has full control over all the pieces running, Runs in memory, and Tests a single logical concept in the system.

When I see developers struggling with unit tests and test-driven development, it’s usually due to test “backfilling” on a poorly-designed or too-complex method. They don’t see value because, in their minds, the work is already done and they’re having to do additional work just to get it unit tested. These methods and objects are violating the single responsibility principle and sometimes have many different code paths and dependencies. That complexity makes writing tests hard because you have to do so much work upfront in terms of mocking and state preparation, and it’s really difficult to cover each and every code path. It also makes for fragile tests because testing specific parts of a method rely on a test’s ability so successfully navigate its way through the logic in the complex method; if an earlier part of the method changes, you now have to refactor unrelated tests to re-route them back to the code they’re testing. (Tip: Avoid problems like this by writing tests first!)

Whether you’re just getting with unit tests or a grizzled veteran, you can benefit from using this list of criteria as a quality measuring stick for the tests you write. High-quality tests that verify implemented features will result in a stable application. Designs will become better and more maintainable because you’ll be able to modify specific functionality without affecting the surround system the same way that you’re able to test it. You won’t have to worry about other developers breaking functionality you’ve added, and you won’t have to worry about breaking functionality they’ve added. You’ll be able to make a modification that affects the entire system with a high-level of confidence. I’d venture to say that virtually every aspect of software development is better when you’ve got good tests.