locked
SQL Query and EF Core Returning Different Results RRS feed

  • 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.

    Wednesday, January 8, 2020 5:55 AM

Answers

  • User-719153870 posted

    Hi bsashcraft,

    I followed your description and your code to build a demo, the demo can reproduce your problem successfully but the reason is unkown.

    Below is the result i got:

    I also tested this same demo in another computer and the demo works well, which return the correct data in the page:

    The only difference between these two environments is the version of core sdk.

    Yours is core 2.1 and my first computer is 2.2, the computer that works successfully is core 3.0.

    My suggestion is that you can update your core sdk to 3.0 and try again see if the problem is solved.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 9, 2020 6:40 AM