I have been working with .NET Entity Framework for a while now, but this has never come up before. Basically, I want to show a data grid view with data in it from a view that contains a DISTINCT statement – like the following:
SELECT DISTINCT SSN,PayRate, CommodityType from MyTable
The problem with this is that I’m not using the table’s primary key, so EF won’t create an entity for me. It gives me an error about it not being able to infer the key. The problem here is that if I include the key in the output, I’ll blow the whole purpose of using DISTINCT.
Here’s how I solved it…
I changed my view to join on another related table based on the SSN…
SELECT DISTINCT MT.SSN, MT.PayRate, MT.CommodityType, MM.MemberId from MyTable as MT INNER JOIN MembershipMaster as MM on MT.SSN = MM.SSN
MM.MemberId is the primary key for the MembershipMaster table, thereby giving EF a key to work with. It now will create an entity for this view.
But wait, there’s more.
When using this entity, it’s not going to fetch the correct data…
Let’s say that your view returned a set of data that contains 3 distinct records for an SSN that you’re looking for.
123456789 - 12.75 - C 123456789 - 13.50 - B 123456789 - 17.00 - C
When you do a Where on the entity set as follows:
IQueryable(myView) mve = entities.myViews.Where(mv => mv.SSN == "123456789");
mve contains three records (as expected), but they’re all a copy of the first result set:
123456789 - 12.75 - C 123456789 - 12.75 - C 123456789 - 12.75 - C
Oops. This is because the key that the entity is using is actually not unique in your view.
So, to solve this, we need to create a new output field that we can use as a key. I just concatenated thethree return values together and gave it a column name like so:
SELECT DISTINCT convert(nvarchar(9),MT.SSN) + convert(nvarchar(7), MT.PayRate)
+ MT.CommodityType as RowKey,
MT.SSN, MT.PayRate, MT.CommodityType, MM.MemberId
from MyTable as MT
INNER JOIN MembershipMaster as MM on MT.SSN = MM.SSN
(PayRate is a numeric(5,2) and SSN is numeric(9,0), so they have to be converted)
Now, I simply updated my entity model from the database, and now I have a new Field on the Entity called “RowKey”. Simply right-click on the original key and deselect “Entity Key” and then right-click on RowKey and select “Entity Key”.
This will give you the desired results in your set:
IQueryable(myView) mve = entities.myViews.Where(mv => mv.SSN == "123456789");
Here are the results in the grid:
123456789 - 12.75 - C 123456789 - 13.50 - B 123456789 - 17.00 - C
There’s a bit more going on here because I have object datasources for each of these view entities so that they can be more easily worked with in the DataGridView UI form in the editor, but I think this gets the general point across.
