Entity Framework: View exclusion without primary key

entity-framework linq-to-entities

Question

I am using SQL Server where I have designed a view to sum the results of two tables and I want the output to be a single table with the results. My query simplified is something like:

SELECT SUM(col1), col2, col3
FROM Table1
GROUP BY col2, col3

This gives me the data I want, but when updating my EDM the view is excluded because "a primary key cannot be inferred".

With a little research I modified the query to spoof an id column to as follows:

SELECT ROW_NUMBER() OVER (ORDER BY col2) AS 'ID', SUM(col1), col2, col3
FROM Table1
GROUP BY col2, col3

This kind of query gives me a nice increasing set of ids. However, when I attempt to update my model it still excludes my view because it cannot infer a primary key. How can we use views that aggregate records and connect them with Linq-to-Entities?

1
0
6/18/2013 7:13:15 PM

Accepted Answer

As already discussed in the comments you can try adding MAX(id) as id to the view. Based on your feedback this would become:

SELECT ISNULL(MAX(id), 0) as ID, 
       SUM(col1), 
       col2, 
       col3 
FROM   Table1 
GROUP BY col2, col3

Another option is to try creating an index on the view:

CREATE UNIQUE CLUSTERED INDEX idx_view1 ON dbo.View1(id)
1
6/19/2013 10:04:50 AM

Popular Answer

I use this code alter view

ISNULL(ROW_NUMBER() OVER(ORDER BY ActionDate DESC), -1) AS RowID 

I use this clause in multi relations view / table query

ROW_NUMBER never give null value because it never seen -1



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow