Kenci Kenci - 1 month ago 4
C# Question

Why does Take() return more rows than specified?

I am querying a database with Entity Framework. To limit the results I am using Take(1000). The problem is that EF returns more than 1000 rows, and all entities after #1000 do not contain correct data.

This is the table structure:

enter image description here

And the query looks like this:

var resultListTmp = db.TinglysEjendom.Where(x => x.EjdStatus == description)
.Include(x => x.TinglysEjd_ESR.Select(y => y.ESR))
.Include(nameof(TingLysMatrikkel))
.Take(amount).AsNoTracking().ToList();


The problem is that all TinglysEjendom entities after #1000 do not have any TinglysMatrikkel relations, but in the database they do.

When removing:

.Include(x => x.TinglysEjd_ESR.Select(y => y.ESR))


from the query, EF returns correct number of rows.

The column MatrikkelNummer in the TinglysMatrikkel table can (amongst others) take the values:

æ
ø
å

Someone suggested it might be an i18n "Internationalization and localization" problem. Im not sure how this could be. Maybe someone could point me in the right direction?

Thanks in advance

LINQ translates the above query in to:

exec sp_executesql N'SELECT

[UnionAll1].[EjdId] AS [C1],

[UnionAll1].[EjdId1] AS [C2],

[UnionAll1].[EjdType] AS [C3],

[UnionAll1].[BygningsNr] AS [C4],

[UnionAll1].[TimeshareNr] AS [C5],

[UnionAll1].[AnpartsNr] AS [C6],

[UnionAll1].[EjerLejNr] AS [C7],

[UnionAll1].[Beskrivelse] AS [C8],

[UnionAll1].[StreetBuildingIdentifier] AS [C9],

[UnionAll1].[EjdStatus] AS [C10],

[UnionAll1].[StatusTimestamp] AS [C11],

[UnionAll1].[IAbonnement] AS [C12],

[UnionAll1].[AbonnementOpretAttempts] AS [C13],

[UnionAll1].[AbonnementsId] AS [C14],

[UnionAll1].[BestemtFastEjendomsNummer] AS [C15],

[UnionAll1].[UpdateToken] AS [C16],

[UnionAll1].[FaellesEjendomIdentifikator] AS [C17],

[UnionAll1].[C1] AS [C18],

[UnionAll1].[ESRId] AS [C19],

[UnionAll1].[ESRId1] AS [C20],

[UnionAll1].[EjdId2] AS [C21],

[UnionAll1].[Passiv] AS [C22],

[UnionAll1].[ESRId2] AS [C23],

[UnionAll1].[EjdId3] AS [C24],

[UnionAll1].[ESRId3] AS [C25],

[UnionAll1].[ESR_Kommune] AS [C26],

[UnionAll1].[ESR_EjdNummer] AS [C27],

[UnionAll1].[UdgaaetCognito] AS [C28],

[UnionAll1].[Status] AS [C29],

[UnionAll1].[C2] AS [C30],

[UnionAll1].[C3] AS [C31],

[UnionAll1].[C4] AS [C32],

[UnionAll1].[C5] AS [C33]

FROM (SELECT

CASE WHEN ([Join1].[ESRId1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],

[Limit1].[EjdId] AS [EjdId],

[Limit1].[EjdId] AS [EjdId1],

[Limit1].[EjdType] AS [EjdType],

[Limit1].[BygningsNr] AS [BygningsNr],

[Limit1].[TimeshareNr] AS [TimeshareNr],

[Limit1].[AnpartsNr] AS [AnpartsNr],

[Limit1].[EjerLejNr] AS [EjerLejNr],

[Limit1].[Beskrivelse] AS [Beskrivelse],

[Limit1].[StreetBuildingIdentifier] AS [StreetBuildingIdentifier],

[Limit1].[EjdStatus] AS [EjdStatus],

[Limit1].[StatusTimestamp] AS [StatusTimestamp],

[Limit1].[IAbonnement] AS [IAbonnement],

[Limit1].[AbonnementOpretAttempts] AS [AbonnementOpretAttempts],

[Limit1].[AbonnementsId] AS [AbonnementsId],

[Limit1].[BestemtFastEjendomsNummer] AS [BestemtFastEjendomsNummer],

[Limit1].[UpdateToken] AS [UpdateToken],

[Limit1].[FaellesEjendomIdentifikator] AS [FaellesEjendomIdentifikator],

[Join1].[ESRId1] AS [ESRId],

[Join1].[ESRId1] AS [ESRId1],

[Join1].[EjdId] AS [EjdId2],

[Join1].[Passiv] AS [Passiv],

[Join1].[ESRId1] AS [ESRId2],

[Join1].[EjdId] AS [EjdId3],

[Join1].[ESRId2] AS [ESRId3],

[Join1].[ESR_Kommune] AS [ESR_Kommune],

[Join1].[ESR_EjdNummer] AS [ESR_EjdNummer],

[Join1].[UdgaaetCognito] AS [UdgaaetCognito],

[Join1].[Status] AS [Status],

CAST(NULL AS int) AS [C2],

CAST(NULL AS varchar(1)) AS [C3],

CAST(NULL AS varchar(1)) AS [C4],

CAST(NULL AS int) AS [C5]

FROM (SELECT TOP (1000)

[Extent1].[EjdId] AS [EjdId],

[Extent1].[EjdType] AS [EjdType],

[Extent1].[BygningsNr] AS [BygningsNr],

[Extent1].[TimeshareNr] AS [TimeshareNr],

[Extent1].[AnpartsNr] AS [AnpartsNr],

[Extent1].[EjerLejNr] AS [EjerLejNr],

[Extent1].[Beskrivelse] AS [Beskrivelse],

[Extent1].[StreetBuildingIdentifier] AS [StreetBuildingIdentifier],

[Extent1].[EjdStatus] AS [EjdStatus],

[Extent1].[StatusTimestamp] AS [StatusTimestamp],

[Extent1].[IAbonnement] AS [IAbonnement],

[Extent1].[AbonnementOpretAttempts] AS [AbonnementOpretAttempts],

[Extent1].[AbonnementsId] AS [AbonnementsId],

[Extent1].[BestemtFastEjendomsNummer] AS [BestemtFastEjendomsNummer],

[Extent1].[UpdateToken] AS [UpdateToken],

[Extent1].[FaellesEjendomIdentifikator] AS [FaellesEjendomIdentifikator]

FROM [dbo].[TinglysEjendom] AS [Extent1]

WHERE [Extent1].[EjdStatus] = @p__linq__0 ) AS [Limit1]

LEFT OUTER JOIN (SELECT [Extent2].[ESRId] AS [ESRId1], [Extent2].[EjdId] AS [EjdId], [Extent2].[Passiv] AS [Passiv], [Extent3].[ESRId] AS [ESRId2], [Extent3].[ESR_Kommune] AS [ESR_Kommune], [Extent3].[ESR_EjdNummer] AS [ESR_EjdNummer], [Extent3].[UdgaaetCognito] AS [UdgaaetCognito], [Extent3].[Status] AS [Status]

FROM [dbo].[TinglysEjd_ESR] AS [Extent2]

INNER JOIN [dbo].[ESR] AS [Extent3] ON [Extent2].[ESRId] = [Extent3].[ESRId] ) AS [Join1] ON [Limit1].[EjdId] = [Join1].[EjdId]

UNION ALL

SELECT

2 AS [C1],

[Limit2].[EjdId] AS [EjdId],

[Limit2].[EjdId] AS [EjdId1],

[Limit2].[EjdType] AS [EjdType],

[Limit2].[BygningsNr] AS [BygningsNr],

[Limit2].[TimeshareNr] AS [TimeshareNr],

[Limit2].[AnpartsNr] AS [AnpartsNr],

[Limit2].[EjerLejNr] AS [EjerLejNr],

[Limit2].[Beskrivelse] AS [Beskrivelse],

[Limit2].[StreetBuildingIdentifier] AS [StreetBuildingIdentifier],

[Limit2].[EjdStatus] AS [EjdStatus],

[Limit2].[StatusTimestamp] AS [StatusTimestamp],

[Limit2].[IAbonnement] AS [IAbonnement],

[Limit2].[AbonnementOpretAttempts] AS [AbonnementOpretAttempts],

[Limit2].[AbonnementsId] AS [AbonnementsId],

[Limit2].[BestemtFastEjendomsNummer] AS [BestemtFastEjendomsNummer],

[Limit2].[UpdateToken] AS [UpdateToken],

[Limit2].[FaellesEjendomIdentifikator] AS [FaellesEjendomIdentifikator],

CAST(NULL AS int) AS [C2],

CAST(NULL AS int) AS [C3],

CAST(NULL AS int) AS [C4],

CAST(NULL AS bit) AS [C5],

CAST(NULL AS int) AS [C6],

CAST(NULL AS int) AS [C7],

CAST(NULL AS int) AS [C8],

CAST(NULL AS int) AS [C9],

CAST(NULL AS int) AS [C10],

CAST(NULL AS bit) AS [C11],

CAST(NULL AS varchar(1)) AS [C12],

[Extent5].[EjdId] AS [EjdId2],

[Extent5].[LandsEjerlavKode] AS [LandsEjerlavKode],

[Extent5].[MatrikkelNummer] AS [MatrikkelNummer],

[Extent5].[EjdId] AS [EjdId3]

FROM (SELECT TOP (1000)

[Extent4].[EjdId] AS [EjdId],

[Extent4].[EjdType] AS [EjdType],

[Extent4].[BygningsNr] AS [BygningsNr],

[Extent4].[TimeshareNr] AS [TimeshareNr],

[Extent4].[AnpartsNr] AS [AnpartsNr],

[Extent4].[EjerLejNr] AS [EjerLejNr],

[Extent4].[Beskrivelse] AS [Beskrivelse],

[Extent4].[StreetBuildingIdentifier] AS [StreetBuildingIdentifier],

[Extent4].[EjdStatus] AS [EjdStatus],

[Extent4].[StatusTimestamp] AS [StatusTimestamp],

[Extent4].[IAbonnement] AS [IAbonnement],

[Extent4].[AbonnementOpretAttempts] AS [AbonnementOpretAttempts],

[Extent4].[AbonnementsId] AS [AbonnementsId],

[Extent4].[BestemtFastEjendomsNummer] AS [BestemtFastEjendomsNummer],

[Extent4].[UpdateToken] AS [UpdateToken],

[Extent4].[FaellesEjendomIdentifikator] AS [FaellesEjendomIdentifikator]

FROM [dbo].[TinglysEjendom] AS [Extent4]

WHERE [Extent4].[EjdStatus] = @p__linq__0 ) AS [Limit2]

INNER JOIN [dbo].[TingLysMatrikkel] AS [Extent5] ON [Limit2].[EjdId] = [Extent5].[EjdId]) AS [UnionAll1]

ORDER BY [UnionAll1].[EjdId1] ASC, [UnionAll1].[C1] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'HentData'

hvd hvd
Answer

This looks like a somewhat unreliable query at the C# part. That said, it's up to Entity Framework to translate it to SQL that does what you asked, and it's failing to do that. It may be worth reporting as a bug if it hasn't been already.

If you look closely in your SQL, you'll find two separate queries using TOP (1000) combined by UNION ALL. However, neither of those queries is using any ORDER BY, therefore there's nothing preventing SQL Server from picking a different set of 1000 records, which it may do if e.g. it can use a different index for the first part of the query compared to the second.

You should be able to work around it by specifying which first 1000 records you want returned. If you don't care about the order, just order them by ID.