Id value in View

Hi,

We have a view that we want to map to a model in the visual designer but our view don't have any primary key or unique value for each row. 

When we want to try to access this view at runtime, an error occur. 

If we add a column 'Id' in the view, the view can be access successfully but some values are duplicated in the result.

If we want the view to return the same result as in sql server, we must provide a unique value to the id column.

Is this a normal behavior? If yes, what can we do when we have a view with no unique column value that can be provided?

Thank you.

Yes, this is the expected behaviour.  The Id really is an identifier: if you want to materialise a row as an entity, it must have an Id which is unique (within the table/entity type).

You have a couple of options if you really cannot establish a unique identifier for your view:

1. Don't materialise the view as a set of entities.  Instead, create a 'dummy' entity, but use a LINQ projection to materialise the view into non-entity objects instead of the entity type.  (I know this sounds weird, but LightSpeed needs to have an entity to do things like mapping properties to columns, even in projection scenarios.)  The lack of an ID won't be a problem for projected objects, and LightSpeed won't care because the ID isn't part of the projection.  (NOTE: This will only work if the projection can be done server-side.  So be sure to do any fancy stuff in the view, not in the query: some projections may cause LightSpeed to retrieve entities and do the projection client-side.  In particular you cannot select associations server-side.)

2. Add an ID column to the view, but assign values on the fly, e.g. NEWID().  You won't be able to assume that the same row will get the same ID each time you query it, so you won't be able to rely on entity identity semantics (which depend on a stable ID).  But this should be okay if you only query the view once per unit of work, or don't care about multiple copies of an entity being returned from different queries.  Note that LightSpeed requires a stable ID to resolve associations.

Please note I haven't tested either of these solutions; also that you will not be able to save changes in either case (though since this is a view I am guessing it is read-only anyway).

Also, if your view contains a combination of columns which uniquely identifies a row, you could use that as a composite key.

Hi Ivan,

Thank you for the very comprehensive answer.

I looked for a more performing manner to generate unique Id of type Int like the row index and found syntax in sql server 2005 where you can get the row index of each line directly in the select query.

Here is the syntax:

SELECT ROW_NUMBER () OVER (ORDER BY  [Column]) AS 'RowIndex', [Fields to add...]

FROM [Table]

Ah, neat trick!  However, one thing to watch out for with this is that IDs are not only not stable, they can also be reused.  For example, suppose you have a table containing two rows, A anc C.  These will get IDs 1 and 2.  Now suppose another thread inserts row B.  Now B will get ID 2 and C will get ID 3.  So if a unit of work which has already loaded C when it had ID 2 has to re-query the view, when it sees ID 2 come back it will go "oh, I already have that" and return the existing C entity instead of materialising B into an entity.

(You may have a similar problem when the order value is not unique -- if there are two rows with the same value in the order column, I don't think SQL Server guarantees to return them in the same order each time.)

This won't matter if you're only querying the view once per unit of work, or if you can be confident that the order will never change during a UOW.  Just thought I'd mention it in case you do run into any weirdness!