The required column 'id' was not present in the results of a `FromSql` operation

I have this entity class:

public class PoDetail
{
    [Key]
    public int Id { get; set; }
    public string ProductId { get; set; }
    public virtual Product Product { get; set; }
}

And I’m trying to run a query on it:

public IActionResult Data()
    {           
        var result = _context.PoDetail
            .FromSqlRaw("select count(ProductId) as count, ProductId from dbo.PoDetail group by ProductId").ToList();
        return Json(result);         
    }

It works fine when I run in query console.
But on the browser I am getting this error:

The required column ‘id’ was not present in the results of a FromSql operation

But if I include the id column in query. I won’t get the desired output.

>Solution :

You need to create a new class to hold the desired result:

public class PoSummary
{
    public int Count { get; set; }
    public string ProductId { get; set; }
}

Add it to your DbContext:

modelBuilder.Entity<PoSummary>().HasNoKey().ToView(null);

And then:

public IActionResult Data()
    {           
        var result = _context.PoSummary
            .FromSqlRaw("select count(ProductId) as count, ProductId from dbo.PoDetail group by ProductId").ToList();
        return Json(result);         
    }