Views not being engineered.

[MarkLFT]

I have around 20+ views in a database, but when I select includeviews = true; only two views are created as keyless tables, the remainder seem to be ignored.

My template type is set to EfCore5, which, from what I have read should map these views correctly. Am I missing an important step, or are these some severe limitations where this will work?

Thanks

[sjh37]

Hi Mark, It could be that the view name has some illegal characters in the name, such as

CREATE VIEW [view.with.multiple.periods]
AS
   ...

However I want to make sure you know why it wasn't generated. So I'll look into the generator and make sure it outputs a comment to say why a view is not generated.

[MarkLFT]

Hi Simon, it would be good to have a reason of why something is not created, but in this case I do not think the name is the problem. Some examples of the missing view names are:

dbo.CashierCurrency dbo.CashierHotelGuest dbo.vCustomerCreditPeriodTotals dbo.vMsgs ...

Hope this helps.

Best regards and many thanks.

[MarkLFT]

Sorry I would add, the name dbo.vPOSDiscounts works, which follows the naming patterns of the above that do not work. Plus a couple of others do work also.

[sjh37]

Could you send me the following data for those views, so I can unit test them. This is what the generator will receive from sql server about the views:

SELECT  c.TABLE_SCHEMA AS SchemaName,
        c.TABLE_NAME AS TableName,
        t.TABLE_TYPE AS TableType,
        c.ORDINAL_POSITION AS Ordinal,
        c.COLUMN_NAME AS ColumnName,
        CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1
                  ELSE 0
             END AS BIT) AS IsNullable,
        DATA_TYPE AS TypeName,
        ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [MaxLength],
        CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS [Precision],
        ISNULL(COLUMN_DEFAULT, '') AS [Default],
        CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision,
        ISNULL(NUMERIC_SCALE, 0) AS Scale,
        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity,
        CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
                  WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1
                  WHEN DATA_TYPE = 'TIMESTAMP' THEN 1
                  WHEN DATA_TYPE = 'UNIQUEIDENTIFIER' AND LOWER(ISNULL(COLUMN_DEFAULT, '')) LIKE '%newsequentialid%' THEN 1
                  ELSE 0
             END AS BIT) AS IsStoreGenerated,
        CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS PrimaryKey,
        ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal,
        CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS IsForeignKey
FROM    INFORMATION_SCHEMA.COLUMNS c
        LEFT OUTER JOIN (SELECT u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME,
                                u.ORDINAL_POSITION
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY') pk
            ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
               AND c.TABLE_NAME = pk.TABLE_NAME
               AND c.COLUMN_NAME = pk.COLUMN_NAME
        LEFT OUTER JOIN (SELECT DISTINCT
                                u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY') fk
            ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA
               AND c.TABLE_NAME = fk.TABLE_NAME
               AND c.COLUMN_NAME = fk.COLUMN_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLES t
            ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
               AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
      AND c.TABLE_NAME NOT LIKE 'sysdiagram%'
	  AND t.TABLE_TYPE='view'
	  AND t.TABLE_NAME IN ('CashierCurrency', 'CashierHotelGuest', 'vCustomerCreditPeriodTotals', 'vMsgs')

If you want to keep it private, email me the result to [email protected]

[MarkLFT]

Hi Simon,

I am afraid this issue comes down to my stupidity. Whilst I had set the include views to true, I did not realise that table exclusion regex filters also exclude views. Whilst testing some regex scenarios, I noticed the views appeared.

I am not sure if anyone else has fallen foul to this, but I find the management of views through the includeviews option, then fine control using table filters confusing. Maybe either using Include/exclude views filter might be less confusing, or if not possible, a not to indicate that views are controlled by the tables filters.

Sorry for the confusion and an inconvenience caused by my initial problem reporting.

[sjh37]

Ah yes. The SQL I used for tables also returns the views so the table filter also filters the views. I should make this clearer! and will make sure I document this, or even split out the filtering for views separate from the tables.

No problem. I started refactoring the code to make this more testable anyway. I will continue refactoring and make sure I sort out the filtering of views explicitly.