Using Views in Entity Framework

I had an issue when accessing a view via Entity Framework. After some research I found a blog that exactly described my issue:

In the example there’s a view that returns the following data when called from SQL Server:
Country Year TotalSales
Philippines 2010 20.000000
Philippines 2011 40.000000

Now, let’s look at the generated class in Entity Framework. In this case there’s a primary key on field Country alone.
public class SalesOnEachCountry
{
[Key]
public int CountryId { get; set; }
public string CountryName { get; set; }
public int OrYear { get; set; }
public long SalesCount { get; set; }
public decimal TotalSales { get; set; }
}

Based on this class, Entity Framework will produce incorrect output:
Country Year TotalSales
Philippines 2010 20.000000
Philippines 2010 20.000000

The problem is, that Country alone doesn’t make the row unique. To guarantee uniqueness, the field Year must be included in the primary key, like shown below:
public class SalesOnEachCountry
{
[Key, Column(Order=0)] public int CountryId { get; set; }
public string CountryName { get; set; }
[Key, Column(Order=1)] public int OrYear { get; set; }

public long SalesCount { get; set; }
public decimal TotalSales { get; set; }
}

Now Entity Framework will return the correct results from the view.

Note from practical experience. Changing the code behind file vw_PortalFault.cs in EsbExceptionDbModel.tt to include the [Key] annotation didn’t work for me. Just open the edmx diagram, go to vw_PortalFaults and then set property EntityKey to true for both FaultCode and FaultCode. Next close and re-open Visual Studio. Build the project, deploy the service containing the edmx model and bingo: it works!

Leave a Reply

Your email address will not be published. Required fields are marked *