Avoid using a wildcard (*) when building mssql views

Using a wildcard while building a View can be very convenient if you have to return all the columns from a table:

CREATE VIEW View_Fruit
AS
SELECT f.*
FROM [dbo].[MyFruitsDb].[Fruit] as f
WHERE DeletedAt IS NULL

Seems legit and easy, however there is one thing to keep in mind, just because you are using a wildcard does not mean that your View will be automatically updated when your table schema is changed. In other words, if you will add one additional column on [dbo].[Fruit] table, you will have to regenerate the view, by simple replacing CREATE by ALTER in the T-SQL script demonstrated above in order to include this new column in the view.

Depends on your development cycle, you may prefer replacing the wildcard by a list of the columns you want to return. This way when you will compare your development DB to staging or production DB schemas you will see that you forgot to add a new column, otherwise schema comparison will results a 0 changes. Tricky.

As a rule of thumb I would avoid using wildcards, as life shows it may take time to discover that a wildcard is used in a View and that it has to be regenerated in order to solve an unexpected bug.

Advertisements

Entity Framework DB First and Computed Columns

Imagine you have a lovely DB first approach in place. There is a table “Fruits” with a column “WikiLink”. This column is getting populated on insert, using a trigger on the DB level. Can you spot “the” problem here?

Let’s try to understand how EF returns the DB generated entity Id from the DB right after the insert. The code below demonstrates a standard way to insert a new entity with EF:

var fruit = new Fruit { Family = “Rosaceae”, Name = “Apple” };
dbContext.Fruits.Add(fruit);
await dbContext.SaveChangesAsync();
// fruit.Id is auto-generated on the DB level and accessible after insert
// fruid.WikiLink is null

The Id column marked as identity, therefor it is recognised as a value generated on DB level. If you will use a DB profiler you will actually see:

— T-SQL Pseudocode for -> dbContext.Fruits.Add(fruit);
INSERT INTO [dbo].[Fruits] (Family, Name)
VALUES (“Rosaceae”, “Apple”)
SELECT SCOPE_IDENTITY() as Id;

It is insert and select in one query. Now back to our problem with ‘WikiLink’. What we would like to achieve is:

INSERT INTO [dbo].[Fruits] (Family, Name)
VALUES (“Rosaceae”, “Apple”)
SELECT SCOPE_IDENTITY() as Id, WikiLink;

When you use a code first approach, you have an option of explicitly marking an entity’s property as db generated with an attribute:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public string WikiLink { get; set; }

However, with DB first approach you don’t have that option, and since there is a trigger behind this column, there is no way for EF to understand that it is generated on the DB level.

Unfortunately, it is not easily solvable with DB first approach. There are a couple of workarounds we could think about:

  1. We can try to manipulate “manually” the auto-generated edmx file and set the “WikiLink” to db computed. Which is a bad idea, because this change will get lost with the next model update as it is being regenerated on each change.
  2. Another option will be to query the DB right after the insert, to get the full row data:

var fruit = new Fruit { Family = “Rosaceae”, Name = “Apple” };
dbContext.Fruits.Add(fruit);
await dbContext.SaveChangesAsync();
await dbContext.Entry(fruit).ReloadAsync()
// fruid.WikiLink is not null anymore

The disadvantage is obvious – additional db query:

— T-SQL Pseudocode for -> dbContext.Fruits.Add(fruit);
INSERT INTO [dbo].[Fruits] (Family, Name)
VALUES (“Rosaceae”, “Apple”)
SELECT SCOPE_IDENTITY() as Id;

— T-SQL Pseudocode for -> await dbContext.Entry(fruit).ReloadAsync()
SELECT FROM * [dbo].[Fruits]
WHERE Id = 99;

However, it could be good enough for some projects.

If you can modify the DB scheme, the solution is quite simple – replace the trigger by a computed column:

— T-SQL Pseudocode for
alter table [dbo].[Fruits]
add [WikiLink] as concat(N’_ttp://mywiki.net/fruits?id=’, Id )

This way you may gain more performance. Since you may have a ‘RowVersion’ column which will be calculated twice because of the trigger. Not to mention that the DB structure is much more readable, compare to unnecessary trigger.

There might be another workarounds for this issue if you don’t have access or you can’t modify the DB. I would like to hear about your experience, so please share via comments.