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.

Add Additional References in LINQPad

LINQPad is one of my favorite development tools. I use it all the time to do quick tests to verify thoughts or discussions that I’m having with peers. It’s terrific for building snippets for email and doing quick what-is-this-really-doing checks. (And it’s perfectly free!)

One of the not-so-obvious things that I’ve run into with LINQPad is adding references. I looked through all the menus looking for some sort of “Add References” option but found nothing!

While slightly less obvious than I would’ve liked, adding references is very easy to do: just press F4. This is the keyboard shortcut to Query Properties–which can be found in the menus–where you can add additional assembly references or import namespaces.

I loves me some LINQPad!

%d bloggers like this: