LINQ to SQL 生成"null"，而不是从 SQL 服务器函数"return table as"生成"not null"

Question

我正在使用SQL Server Express 2012高级服务和Visual Studio 2013更新2。我已经彻底简化了这个例子。我的数据库中有以下表:

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

在此表上使用全文表功能:

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

当我将函数GetFilteredRealtyFulltext放入DBML中时，设计器生成列Ranking为空值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;
    }
  }
}

我希望它只生成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;
    }
  }
}

DBML文件中正确生成了表Realty, rank只是整数，但是表函数生成错误。有什么问题吗?

更新:

链接问题直达问题核心

Answer 1

问题是SQL Server认为GetFilteredRealtyFulltext函数的Ranking列是可空的，即使底层表列被创建为NOT NULL。您可以通过对数据库运行以下查询来查看这一点:

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'

我发现SQL Server做同样的事情，如果你创建一个基于Realty表的视图。谷歌搜索答案没有找到任何权威的答案，但在数据库管理员网站上确实导致了类似的问题。这导致了这个SO讨论，这让我尝试使用ISNULL。在我从DBML文件中删除前面的函数并重新添加它之后，使用ISNULL hack(如下例所示)在DBML文件中给出了正确的列定义。

需要强制计算列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,
...
)

然后强制函数也不可为空:

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