Loading a view throws exception

[pbolduc]

trafficstars

I am having troubles referencing any of my views. The error occurs in the Column class on line 103.

The following expression returns an empty list. The column it fails on is the first [Id] column of the view. It says it is a SqlComputedColumn. I do not know enough about the Microsoft.SqlServer.Dac.Model classes to try to resolve myself.

SqlObject.GetReferenced(dac.Column.DataType).ToList()

Error       Running transformation: System.InvalidOperationException: Sequence contains no elements
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at SqlSharpener.Model.Column..ctor(TSqlObject tSqlObject, TSqlObject tSqlTable, IEnumerable`1 primaryKeys, IDictionary`2 foreignKeys)
   at SqlSharpener.Model.View..ctor(TSqlObject tSqlObject, IEnumerable`1 primaryKeys, IDictionary`2 foreignKeys)
   at SqlSharpener.MetaBuilder.<>c__DisplayClass10.<LoadModel>b__9(TSqlObject sqlView)
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at SqlSharpener.MetaBuilder.LoadModel(TSqlModel model)
   at SqlSharpener.MetaBuilder.LoadModel()
   at SqlSharpener.MetaBuilder.get_Tables()

SQL Scripts to repoduce

CREATE TABLE [dbo].[Port] (
    [Id]                 SMALLINT       IDENTITY (1, 1) NOT NULL,
    [Name]               VARCHAR (50)   NOT NULL,
    [Abbreviation]       VARCHAR (3)    NOT NULL,
    [Active]             BIT            NOT NULL,
    [Longitude]          DECIMAL (9, 6) NULL,
    [Latitude]           DECIMAL (9, 6) NULL,
    [PortTypeId]         SMALLINT       NOT NULL,
    [CreatedBy]          VARCHAR(255)   NOT NULL,
    [CreatedDateTime]    DATETIME2      NOT NULL,
    [UpdatedBy]          VARCHAR(255)       NULL,
    [UpdatedDateTime]    DATETIME2          NULL,
    [RowVersion]         ROWVERSION     NOT NULL,
    CONSTRAINT [PK_Port] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [CK_Port_Latitude_Latitude] CHECK ([Latitude] IS NULL AND [Longitude] IS NULL OR [Latitude] IS NOT NULL AND ([Latitude]>=(-90) AND [Latitude]<=(90)) AND [Longitude] IS NOT NULL AND ([Longitude]>=(-180) AND [Longitude]<=(180))),
    CONSTRAINT [FK_Port_PortTypeId] FOREIGN KEY ([PortTypeId]) REFERENCES [dbo].[PortType] ([Id]),
    CONSTRAINT [UQ_Port_Abbreviation] UNIQUE NONCLUSTERED ([Abbreviation] ASC),
    CONSTRAINT [UQ_Port_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);

CREATE TABLE [dbo].[PortType]
(
    [Id]           SMALLINT      IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR(20)   NOT NULL,
    [RowVersion]   ROWVERSION    NOT NULL,
    CONSTRAINT [PK_PortType] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UX_PortType_Name] UNIQUE NONCLUSTERED ([Name] ASC)
)

CREATE VIEW [dbo].[RemotePort]
AS
SELECT [Port].*
  FROM [dbo].[Port]
 INNER JOIN [dbo].[PortType]
         ON [PortType].[Id] = [Port].[PortTypeId]
 WHERE [PortType].[Name] = 'Remote Port'

[pbolduc]

I spent some time looking at this problem. When I used DacExplorer, I can see the column is defined as a ComputedColumn and type type references the origin table's column. My guess is the correct way to determine the view's column type is to look up the data type of the source table's column.

[aeslinger0]

Not sure if we can loop through the source columns used in the expression, but if we can we might be able to derive a type that will work. For example, if all columns are int then use int, else if all are numeric, but at least one is decimal or float then use float, else if any are a string then use string.

I think a work-around might be to wrap your expression in the view with CAST as in Cast((col1 + col2) as int) AS ColName.

[chad206548]

I received a similar error in any table script where a column is a calculated column. I ended up having to modify my T4 to simple ignore any table that had a calculated column, as I tried multiple solutions to try to get it to work with the calculated column but had no luck.