score:2
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
Source: stackoverflow.com
Related Articles
- The result of a query returns the same value for COLUMN_NAME from an Entity Framework query, which doesn't make sense
- C# Linq XML Query where multiple elements of same name from a parent node based on a child node value
- LINQ Query Result - dynamically get field value from a field name variable
- The given value of type String from the data source cannot be converted to type int of the specified target column
- C# - Remove rows with the same column value from a DataTable
- Entity Framework/LINQ Error: The column prefix 'Project1' does not match with a table name or alias name used in the query
- get attribute value from multiple elements with same name and attribute value of one other element in xml
- Extracting Week number from DateTime value in Linq to entity query
- Returning the result from a linq query but with one value modified
- Remove rows with same column value from DataTable and add corresponding values
- Select Value from List Column Where Name Match
- How to SUM up results by column value in db query result
- Linq query to get the properties from object B which have the same name and type of object A
- SQL query shows good database values, but LINQ to Entity framework brings a null value from nowhere
- How to update two columns with same name from two tables in a join query
- LINQ returns wrong value for one specific column in SQL View query
- comparing equality 2 lists resulting from entity framework query result in c#
- Nested new result from entity framework query
- How can I get data from the Entity Framework if I only know the table name and the column name from which to get the data?
- How to translate a query with an EntityState and a value from Entity Framework 5 to 6?
- linq query to select record having same name but different value in two columns
- Selecting column from LINQ query where cells equals value from a list
- Get a specific column value from a table when query through linq
- Linq Query where related entity contains value from array
- How to get multiple column value from database into a list using LINQ query
- Can I assign the result of a Linq query to the source variable of the same query?
- Using LINQ to get column value from column name when you have the row?
- Get LINQ query Result with column value based on DateTime column
- Take value from part of a LINQ query and add it to the result
- access entity query result from another func
- Cannot use aggregate on a List<T>
- groupby filtering in linq
- Can I refactor my linq select?
- Dynamic Left join 2 datatable
- LINQ how to Filter the data based on the value of the properties
- at least one object must implement icomparable on datasourceresult
- Take any single record if matching records not available - Linq
- Simple LINQ to XML query
- LINQ - Group By with Having?
- Sub-Query in linq
- How to find the next greater available record in SQL if the one that I entered is not found
- Grouping Table Data As Column Headers on Excel Sheet
- IQueryable optional OrderBy with PredicateBuilder and Entity Framework
- Three table query using Linq/Lambda in EF
- Duplicating Stored Users C# MVC Linq
- Returning Dictionary<string,?> from LINQ to SQL
- How to filter data with method GetAll() and UnitOfWork (Repository Pattern)
- Is it possible to do XQuery through linq?
- linq query with many to many relationship
- Call to Task<> method inside LINQ and return data