SQL Query and EF Core Returning Different Results

Question

User-1580299748 posted

Hello All,

I am using ASP .Net Core (2.1) Razor (non-MVC) Pages, with EF Core and SQL Server.

In this particular scenario, I have two classes, a "ParentCity" which has a one to many relationship with "City".

Classes as follows.

Parent City:

public class ParentCity
{

[Key]
public int Id { get; set; }

[Required]
[Display(Name = "Parent City")]
public string Description { get; set; }

public City City { get; set; }

}

City:

public class City
{

[Key]
public int Id { get; set; }

[Required]
[Display(Name = "City")]
public string Description { get; set; }

public int? ParentCityId { get; set; }
[ForeignKey("ParentCityId")]
public virtual ParentCity ParentCity { get; set; }

}

In my database I have one Parent City, and three Cities:

Parent City Data:

ID	Description
1	Columbus

City Data:

ID	Description	ParentCityID
1	Canal Winchester	1
2	Worthington	1
3	Grandview	1

My Index.cshtml webpage looks like this:

{
[TempData]
public string StatusMessage { get; set; }

private readonly Data.ApplicationDbContext _context;

public IndexModel(Data.ApplicationDbContext context)
{
_context = context;
}

public IList<ParentCity> ParentCity { get; set; }

public async Task<IActionResult> OnGetAsync()
{
ParentCity = await _context.ParentCity
.Include(p => p.City)
.OrderBy(c => c.Description)
.AsNoTracking()
.ToListAsync();

return Page();
}
}

When I execute my webpage, the Output windows if my VS2019 IDE shows the following SQL Query being executed by EF Core:

SELECT
[p].[Id],
[p].[Description],
[p.City].[Id],
[p.City].[Description],
[p.City].[ParentCityId],
FROM
[ParentCity] AS [p]
LEFT JOIN
[City] AS [p.City]
ON
[p].[Id] = [p.City].[ParentCityId]
ORDER BY
[p].[Description]

On the webpage I have the following:

<tbody>
@foreach (var item in Model.ParentCity)
{
<tr>
<td class="align-middle">
@Html.DisplayFor(modelItem => item.Description)
</td>
<td class="align-middle">
@Html.DisplayFor(modelItem => item.City.Description)
</td>

</td>
</tr>
}
</tbody>

The webpage returns the following data:

Columbus	Grandview
Columbus	Grandview
Columbus	Grandview

However, if I execute the raw SQL Query against my database, in my VS2019 IDE I get the following results:

ID	Description	ID	Description	ParentCityID
1	Columbus	1	Canal Winchester	1
1	Columbus	2	Worthington	1
1	Columbus	5	Grandview	1

This data appears correct to me.

One other point... If I add a new 4th record to the City table (e.g. "Bexley") my webpage data now looks like this:

Columbus	Bexley
Columbus	Bexley
Columbus	Bexley
Columbus	Bexley

The City field always returns the last row in the table.

Any help appreciated in understanding why this is happening.

Thanks in advance for your time.