EF Core stored procedure different returning result

Question

I have this stored procedure

CREATE PROCEDURE [dbo].[GetBooksByAuthor2]
AS
    SELECT 
        books.Id, authors.Name AS Author, books.Name AS Book 
    FROM
        dbo.Authors authors
    INNER JOIN 
        dbo.AuthorsBooks authorsBooks ON authors.Id = authorsBooks.AuthorId
    INNER JOIN 
        dbo.Books books ON books.Id = authorsBooks.BookId
GO

In SQL Server Management Studio, result looks like this:

Id| Author       | Book
--+--------------+----------
1 | FirstAuthor  | GoodBook1
2 | FirstAuthor  | GoodBook2
3 | FirstAuthor  | GoodBook3
1 | SecondAuthor | GoodBook1
3 | SecondAuthor | GoodBook3 

In EF Core I added a view model for my stored procedure

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

    public string Author { get; set; }
    public string Book { get; set; }
}

and I also added something like this to the context class:

public partial class StrangeContext : DbContext
{
     public virtual DbSet<BookViewModel> Books2 { get; set; }
}

Then I invoke it in a controller action

[HttpGet]
public JsonResult GetAuthors()
{
    var result = db.Books2.FromSql("GetBooksByAuthor2").ToList();
    return new JsonResult(result);
}

Result of Json look like

[
    { "id": 1, "author":"FirstAuthor", "book":"GoodBook1"},
    { "id": 2, "author":"FirstAuthor", "book":"GoodBook2"},
    { "id": 3, "author":"FirstAuthor", "book":"GoodBook3"}, 
    { "id": 1, "author":"FirstAuthor", "book":"GoodBook1"}, 
    { "id": 3, "author":"FirstAuthor", "book":"GoodBook3"}
]

What I am doing wrong? Wrong ViewModel? Where is SecondAuthor?

Answer 1

You defined the Id of your BookViewModel as the Key and as such, Entity Framework caches the first time it queries the 'FirstAuthor' with the Id of 1. The second time it encounters the Id 1, it uses the current context and fills the entity based on the cached value.

The problem here is your equivocal model. How can FirstAuthor and SecondAuthor both have the Id = 1.

You could remove the Key attribute in your model, but it might lead to confusion, wherever you display these results and once you will want to save any of these entities, you will get unpredictable results.

You could also try AsNotracking() but I would strongly advise against it, as you will have troubles once you try to track or save changes. Instead, adjust your stored procedure to return unique IDs for unique rows.