c# - 当数据库中有数据时查询不返回任何内容

Question

我使用 EntityFramework 4 + 生成的 POCO，延迟加载禁用。
假设有名为 Table1、Table2、Table3 和 Table4 的 SQL 表，并假设它们包含一些数据。
让我们假设这些表的简化 POCO 表示如下所示:

public class Table1
{
    public int ID;
    public DateTime TableDate;
    public int Table2ID;
    public Table2 Table2;
    public ICollection<Table3> Table3s;
}

public class Table2
{
    public int ID;
    public string SomeString;
    public int Table4ID;
    public Table4 Table4;
}

public class Table3
{
    public int ID;
    public int Table1ID;
    public Table1 Table1;
    public decimal SomeDecimal;
}

public decimal Table4
{
    public int ID;
    public string SomeName;
}

如果执行下面的代码:

Database DB = new Database(); // object context
var result = DB.Table1
    .Where(x => x.TableDate >= DateTime.MinValue);

EF 将生成以下 SQL 语句:

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[TableDate] AS [TableDate], 
[Extent1].[Table2ID] As [Table2ID]
FROM [dbo].[Table1] AS [Extent1]
WHERE ([Extent1].[TableDate] >= @p__linq__0)',N'@p__linq__0 datetime2(7)',@p__linq__0='0001-01-01 00:00:00'

查询将返回预期的数据。
但是，如果执行以下代码:

Database DB = new Database(); // object context
var result = DB.Table1
    .Include("Table2")
    .Include("Table2.Table4")
    .Include("Table3")
    .Where(x => x.TableDate >= DateTime.MinValue);

EF 将生成以下 SQL 语句:

exec sp_executesql N'SELECT 
[Project1].[ID2] AS [ID], 
[Project1].[ID] AS [ID1], 
[Project1].[TableDate] AS [TableDate], 
[Project1].[ID1] AS [ID2], 
[Project1].[SomeString] AS [SomeString], 
[Project1].[Table4ID] AS [Table4ID], 
[Project1].[ID3] AS [ID3], 
[Project1].[SomeName] AS [SomeName], 
[Project1].[ID4] AS [ID4], 
[Project1].[SomeDecimal] AS [SomeDecimal], 
[Project1].[Table1ID] AS [Table1ID]
FROM ( SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[TableDate] AS [TableDate], 
[Extent2].[ID] AS [ID1], 
[Extent2].[SomeString] AS [SomeString], 
[Extent2].[Table4ID] AS [Table4ID], 
[Extent3].[ID] AS [ID2], 
[Extent4].[ID] AS [ID3], 
[Extent4].[SomeName] AS [SomeName], 
[Extent5].[ID] AS [ID4], 
[Extent5].[SomeDecimal] AS [SomeDecimal], 
[Extent5].[Table1ID] AS [Table1ID], 
CASE WHEN ([Extent5].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM     [dbo].[Table1] AS [Extent1]
INNER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2ID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[Table2] AS [Extent3] ON [Extent1].[Table2ID] = [Extent3].[ID]
LEFT OUTER JOIN [dbo].[Table4] AS [Extent4] ON [Extent3].[Table4ID] = [Extent4].[ID]
LEFT OUTER JOIN [dbo].[Table3] AS [Extent5] ON [Extent1].[ID] = [Extent5].[Table1ID]
WHERE ([Extent1].[TableDate] >= @p__linq__0)
)  AS [Project1]
ORDER BY [Project1].[ID2] ASC, [Project1].[ID] ASC, [Project1].[ID1] ASC, [Project1].[ID3] ASC, [Project1].[C1] ASC',N'@p__linq__0 datetime2(7)',@p__linq__0='0001-01-01 00:00:00'

查询将不会返回任何内容。

为什么会这样？

编辑

以下是创建上述表的 SQL 语句:

CREATE TABLE [dbo].[Table1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Table2ID] [int] NOT NULL,
[TableDate] [date] NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Table1]  WITH NOCHECK ADD  CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([Table2ID])
REFERENCES [dbo].[Table2] ([ID])

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Table2]

CREATE TABLE [dbo].[Table2](
[ID] [int] NOT NULL,
[SomeString] [nvarchar](50) NOT NULL,
[Table4ID] [int] NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Table2]  WITH NOCHECK ADD  CONSTRAINT [FK_Table2_Table4] FOREIGN KEY([Table4ID])
REFERENCES [dbo].[Table4] ([ID])
ON UPDATE CASCADE

ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table4]

CREATE TABLE [dbo].[Table3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SomeDecimal] [decimal](18, 4) NOT NULL,
[Table1ID] [int] NOT NULL,
 CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Table3]  WITH NOCHECK ADD  CONSTRAINT [FK_Table3_Table1] FOREIGN KEY([Table1ID])
REFERENCES [dbo].[Table1] ([ID])
ON DELETE CASCADE

ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table1]

CREATE TABLE [dbo].[Table4](
[ID] [int] NOT NULL,
[SomeName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table4] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

编辑 2

这个查询也不会返回任何记录，可以作为一个最小的例子:

Database DB = new Database();
var result = DB.Table1
    .Include("Table2")
    .Where(x => x.TableDate >= DateTime.MinValue);

生成的 SQL:

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Table2ID] AS [Table2ID], 
[Extent1].[TableDate] AS [TableDate], 
[Extent2].[ID] AS [ID1], 
[Extent2].[SomeString] AS [SomeString], 
[Extent2].[Table4ID] AS [Table4ID], 
FROM  [dbo].[Table1] AS [Extent1]
INNER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2ID] = [Extent2].[ID]
WHERE ([Extent1].[TableDate] >= @p__linq__0)',N'@p__linq__0 datetime2(7)',@p__linq__0='0001-01-01 00:00:00'

此外，这里是 .edmx 的摘录:

<EntityContainer>
      <AssociationSet Name="FK_Table1_Table2" Association="MyModel.Store.FK_Table1_Table2">
        <End Role="Table2" EntitySet="Table2" />
        <End Role="Table1" EntitySet="Table1" />
      </AssociationSet>
</EntityContainer>

<!-- ... -->

<EntityType Name="Table2">
  <Key>
    <PropertyRef Name="ID" />
  </Key>
  <Property Name="ID" Type="int" Nullable="false" />
  <Property Name="SomeString" Type="nvarchar" Nullable="false" MaxLength="50" />
  <Property Name="Table4ID" Type="int" />
</EntityType>

<!-- ... -->

<EntityType Name="Table1">
  <Key>
    <PropertyRef Name="ID" />
  </Key>
  <Property Name="ID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
  <Property Name="TableDate" Type="date" Nullable="false" />
  <Property Name="Table2ID" Type="int" Nullable="false" />
</EntityType>

<!-- ... -->

<Association Name="FK_Table1_Table2">
  <End Role="Table2" Type="MyModel.Store.Table2" Multiplicity="1" />
  <End Role="Table1" Type="MyModel.Store.Table1" Multiplicity="*" />
  <ReferentialConstraint>
    <Principal Role="Table2">
      <PropertyRef Name="ID" />
    </Principal>
    <Dependent Role="Table1">
      <PropertyRef Name="Table2ID" />
    </Dependent>
   </ReferentialConstraint>
</Association>

Answer 1

看来是SQL Server中的实际数据不一致的问题。
如前所述 there ,

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Table1" that do not have matches in "Table2", those rows will NOT be listed.

此查询没有理由失败，除非“Table2”中确实没有与“Table1”匹配的行。虽然这很奇怪，因为 FK 约束是强制执行的，但这值得另一个问题并且这个案例已经结束。