LINQ to SQL generate "null" instead of "not null" from sql server function "return table as"

Question

I'm working with SQL Server Express 2012 with advance services and Visual Studio 2013 update 2. I have simplified the example to the core. I have the following table in my database:

CREATE TABLE [dbo].[Realty]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
...
...
[RankingBonus] [int] NOT NULL,
[Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL,
...
)

Having fulltext table function over this table:

CREATE FUNCTION [dbo].[GetFilteredRealtyFulltext]
(@fulltextcriteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT 
realty.Id AS realtyId,
...
realty.Ranking, --THIS IS IMPORTANT FOR THE QUIESTION!
...,
( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0)) AS FtRank

FROM realty
--these joins and where conditions are not important for this stackoverflow question
--this only shows why I use table function with return table
--it is because this is the only way I found how to generate LINQ to SQL with fulltext as IQueryable<T>
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
Left JOIN CONTAINSTABLE(Realty, *, @fulltextcriteria) ftR ON realty.Id = ftR.[Key] 
Left JOIN CONTAINSTABLE(ruian_obec, *, @fulltextcriteria) ftObec ON realty.obecId = ftObec.[Key] 
Left JOIN CONTAINSTABLE(Okres, *, @fulltextcriteria) ftOkres ON realty.okresId = ftOkres.[Key]
Left JOIN CONTAINSTABLE(pobvod, *, @fulltextcriteria) ftpobvod ON realty.pobvodId = ftpobvod.[Key]
WHERE Person.ConfirmStatus = 1
AND ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0))  > 0
)
GO

When I drop the function GetFilteredRealtyFulltext into DBML, the designer generate the column Ranking as Nullable int

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Ranking", DbType="Int")]
public System.Nullable<int> Ranking
{
  get
  {
    return this._Ranking;
  }
  set
  {
    if ((this._Ranking != value))
    {
       this._Ranking = value;
    }
  }
}

I expect it should generate just integer:

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Ranking", DbType="Int NOT NULL"")]
public int Ranking
{
  get
  {
    return this._Ranking;
  }
  set
  {
    if ((this._Ranking != value))
    {
       this._Ranking = value;
    }
  }
}

The table Realty is generated correctly in DBML file, the Ranking is just integer, but the table function is generated incorrectly. What is wrong with it?

UPDATE:

Linked question going to the core of problem.

Answer 1

The problem is that SQL Server considers the Ranking column from your GetFilteredRealtyFulltext function to be nullable even though the underlying table column is created as NOT NULL. You can see this by running the following query against your database:

select 
o.name ObjectName,
c.name ColumnName,
c.is_nullable ColumnIsNullable
from 
sys.all_objects o 
inner join 
sys.all_columns c 
on 
o.object_id = c.object_id 
where 
o.[name] = 'GetFilteredRealtyFulltext'

I found that SQL Server does the same thing if you create a view based on Realty table. Googling for an answer didn't find anything authoritative, but did lead to a similar problem over at the Database Administrators site. That led to this SO discussion, which got me to try using ISNULL. Using the ISNULL hack as in the following example gives the correct column definition in the DBML file, once I deleted the previous function from the DBML file and re-added it.

Is is needed to force computed column NotNullable:

CREATE TABLE [dbo].[Realty]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
...
...
[RankingBonus] [int] NOT NULL,
[Ranking]  AS ISNULL([Id]+[RankingBonus], 0) PERSISTED NOT NULL,
...
)

And then force function to be not nullable too:

CREATE FUNCTION [dbo].[GetFilteredRealtyFulltext]
(@fulltextcriteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT 
realty.Id AS realtyId,
realty.RankingBonus,
ISNULL(realty.Ranking, 0) as Ranking  --ISNULL hack
FROM realty
)
GO