So today I decieded to create a very simple console application that would help me dump some data to a file.  Since my data was in SQL and I wanted to alter the relationships a bit I figured EntityFramework and a bit of linq magic might do the trick.  I was wrong.

What happened

Well I quickly created my linq query since it was only going to be ran once I simply called .ToList() on my three views I was dealing with and use Linq to Objects to put everything in the correct order.

 1: entities.MyView.ToList();

I ran my query expecting everything to work nicely and I would be done with my simple task in 5 minutes, however looking at my mapped objects from the View everything was identical.  It had the correct number of entries in the list but they were all the same what gives?

The problem

Off to google I went since this seemed very odd behavior, I figured I must have been doing something wrong.  It turns out it was a bit from column a and a bit from column b.  The first issue I had was I just let the entity framework tool build up the edmx file for me without looking anything over.  I ideally you should verify what it produces but it was a quick application there should be no need for it.  The second thing that happened was Entityframework needs an entity key for the models to work correctly, since the view does not have an Id field it will attempt to infer from the data which one is the entity key.  This in here is the problem, Enttyframework sees a column that looks like an entity key but actually isn’t one.  Because of this behavior anytime entity framework sees this key when building the list of object it will use the existing one in the collection and result in the duplicates showing up in your list.  Further details are found on this answer in stackoverflow.

This Fix

You have two options to fix this, one you could simply return a unique id from your view via adding a column to your select.  If this is not possible you will need to do a mapping on your own to retrieve the data from the database.  Since you are using EntityFramework this turns out to be fairly easy by using ExecuteStoreQuery<>

 1: var services = entities.ExecuteStoreQuery<MyObject>("SELECT [EntityID],[Service]FROM [dbo].[myView]").ToList();

That one line will execute the sql and map it to the object specified in the <T>.  As usual something that should have been a 5 minute exercise turned into learning something new.