0

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?

jamesSampica
  • 12,230
  • 3
  • 63
  • 85
  • You cannot perform inserts/updates using LINQ if the tables/views you are updating do not have primary keys defined. It needs a way to uniquely identify rows. – Jeff Mercado Jun 18 '13 at 19:10
  • 1
    I know it needs a way to uniquely identify rows. Given the task of aggregating data in a table, how can I accomplish that? – jamesSampica Jun 18 '13 at 19:17
  • @Jim do you have an id column on one of the source tables? If so you could try `MAX(id) as id` – qujck Jun 18 '13 at 19:46
  • @qujck yes, this seems to give me unique ids. However when I update my edmx it still gives me the error "primary key cannot be inferred, table excluded". – jamesSampica Jun 18 '13 at 20:02
  • @Jim did you try adding a unique index? `CREATE UNIQUE CLUSTERED INDEX idx_table1 ON dbo.Table1(id)` – qujck Jun 18 '13 at 20:08
  • Since Table1 already has a primary key (id) it does not allow me to create another clustered index. I think specifying a column as a primary key automatically creates a clustered index. – jamesSampica Jun 18 '13 at 21:06
  • 1
    `SELECT ISNULL(MAX(id), 0) as ID, SUM(col1), col2, col3 FROM Table1 GROUP BY col2, col3` This worked. The entity model was able to infer ID as the primary key. Any drawbacks to this? – jamesSampica Jun 18 '13 at 21:29

3 Answers3

2

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)
qujck
  • 14,388
  • 4
  • 45
  • 74
1

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

Abdullah SARGIN
  • 1,646
  • 1
  • 13
  • 19
-1

This is all I needed to add in order to import my view into EF6.

select ISNULL(1, 1) keyField
adrianz
  • 109
  • 1
  • 3