Why is this iteration over a List not giving me the correct values out of the SQL view?

Question

I'm working on a new application that's must show a table in a data grid. The columns for the data grid are retrieved by a SQL view that a colleague of mine wrote. However, the data returned looks like it repeats data, such as you get if the SQL query wasn't well formed. Here's code to define the SQL view:

Create view [Core].[vwDataDictionary] as
SELECT ROW_NUMBER() OVER(order by A.[TABLE_CATALOG]) AS [ID]   
      ,A.[TABLE_CATALOG]
      ,A.[TABLE_SCHEMA]
      ,A.[TABLE_NAME]
      ,A.[COLUMN_NAME]
      ,[ORDINAL_POSITION]
      ,[COLUMN_DEFAULT]
      ,[IS_NULLABLE]
      ,[DATA_TYPE]
      ,[CHARACTER_MAXIMUM_LENGTH]
      ,[CHARACTER_OCTET_LENGTH]
      ,[NUMERIC_PRECISION]
      ,[NUMERIC_PRECISION_RADIX]
      ,[NUMERIC_SCALE]
      ,[DATETIME_PRECISION]
      ,[CHARACTER_SET_CATALOG]
      ,[CHARACTER_SET_SCHEMA]
      ,[CHARACTER_SET_NAME]
      ,[COLLATION_CATALOG]
      ,[COLLATION_SCHEMA]
      ,[COLLATION_NAME]
      ,[DOMAIN_CATALOG]
      ,[DOMAIN_SCHEMA]
      ,[DOMAIN_NAME]
      ,B.[CONSTRAINT_NAME]
      ,C.[CONSTRAINT_TYPE]
  FROM [INFORMATION_SCHEMA].[COLUMNS] AS A
  left join
  [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] AS B
  on A.TABLE_NAME = B.TABLE_NAME and A.COLUMN_NAME = B.COLUMN_NAME
  left join
  [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS C
  on B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
  where A.[TABLE_SCHEMA] = 'App'

However, the data grid show data like this:

So, I decided to simplify things by creating a console app to just pull the data from the view and iterate over what's returned. I used EF's code first with existing database. Here's the code for the console app:

class Program
{
    static void Main(string[] args)
    {
        using (var ctx = new CodeModel())
        {
            var bozo = ctx.vwDataDictionaries.ToList();
            int i = 0;
            foreach (var item in bozo)
            {
                i++;
                Console.WriteLine($"i: {i}, ID: {item.ID}, TABLE_NAME: {item.TABLE_NAME}, COLUMN_NAME: {item.COLUMN_NAME}");
            }
        }
        Console.WriteLine("Finished");
    }
}

But if I run that console app, I get values like this:

Running the query in the C# app returns 282 records. All have a column name of "ID". However, if I run the same query in Azure Data Studio, I get the following:

Of course, I know that I'm not sorting or filtering, but I still don't understand why the sample program is returning so many duplicates for COLUMN_NAME.

I'm working with VS 2019 version 16.8.2. .NET Framework 4.5.2 and EF 6.

Are you running this view against the same data sources? In SQL Server database `[TABLE_CATALOG]` stores the name of the database as per [this answer](https://dba.stackexchange.com/a/3785/94130). — Alex, Dec 01 '20 at 23:42
Yes, I'm running the view against the same data source that I use for EF. — Rod, Dec 02 '20 at 04:57
Thank you, to whoever left links to other posts addressing this issue. It has helped me. — Rod, Dec 02 '20 at 16:17

score 0 · Answer 1 · answered Dec 01 '20 at 23:52

I'm not sure what exactly you are expecting, but columns can have more than one constraint. And it looks like you are fetching the list of all columns in the entire schema, rather than the columns of a single table.

If you look at each of the three tables in your query;

select * from [INFORMATION_SCHEMA].[COLUMNS] order by table_schema, TABLE_NAME
select * from [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] order by table_schema, TABLE_NAME
select * from [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] order by table_schema, TABLE_NAME

You'll probably get a better idea of what is different to your expectation.

One of my colleagues wrote the view. He wrote it with the intent of filtering what the view returns by TABLE_NAME. — Rod, Dec 02 '20 at 05:02

Why is this iteration over a List not giving me the correct values out of the SQL view?

1 Answers1