All geek questions in one place

The request is not returned when there is data in the database

I am using EntityFramework 4+ generated POCO with Lazy loading disabled .
Say there are SQL tables with tables Table1, Table2, Table3 and Table4 and assume that they contain some data. Assume that a simplified POCO representation of these tables is as follows:

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; }

If the following code is executed:

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

EF generates the following SQL statement:

 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'

and the request will return the expected data.
However, if the following code is executed:

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

EF generates the following SQL statement:

 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'

and the request will not return anything.

Why can this happen?

EDIT

The following are SQL statements for creating the above tables:

 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]

EDIT 2

This query also returns no records and can serve as a minimal example:

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

Generated 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'

Also, here is an excerpt from .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>
+6
source share
2 answers

It seems that the problem with the actual data on the SQL server was inconsistent.
As indicated there ,

The INNER JOIN keyword returns rows if there is at least one match in both tables. If there are rows in table "Table1" that do not have matches in "table 2", these rows will NOT be listed.

there is no reason this query fails if there is no row in table "Table2" that matches "Table1". However, this is strange, since the FK restrictions are respected, but it deserves another question, and this case is closed.

+1
source

My assumption is that Include () s forces your query to not be evaluated in the expected order, so TableDate is not available when Where () is called. What if you are forcing and then name your "Where" ()?

0
source

Source: https://habr.com/ru/post/915375/

More articles:


All Articles