score:2

Accepted answer

There is a subtle problem with views when used from Entity Framework.

If you have a table with EF, you need to have a primary key to uniquely identify each row. Typically, that's a single column, e.g. an ID or something like that.

With a view, you don't have the concept of a "primary key" - the view just contains some columns from some tables.

So when EF maps a view, it cannot find a primary key - and therefore, it will use all non-nullable columns from the view as "substitute" primary key.

When EF now reads the data, it will get all the columns and create an in-memory object representing that row. If EF now later on reads another row from the database where those non-nullable columns that make up the substitute PK of your view are the same - then it'll think: "gee, I already have that row" and just add another copy of the same object into your result set.

So in this case, in the end you might end up having 18 identical rows in your EF result set - even though the SQL Server output properly shows different data. ......

UPDATE: as a possible solution, you could try to tap in the sys.columns and sys.tables catalog views which offer "better" columns - non-nullable ones, that aren't all the same for each column....

Try something like this:

CREATE VIEW [Core].[vwDataDictionary] 
AS
    SELECT 
        t.Name,
        t.object_id,
        c.Name,
        c.column_id
        -- possibly later more columns here....
    FROM
        sys.tables t
    INNER JOIN
        sys.columns c ON c.object_id = t.object_id

Related Articles