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

Question

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.

Answer 1

For such simple query, you do not need SQL:

public IActionResult Data()
{           
    var result = _context.PoDetail
        .GroupBy(x => x.ProductId)
        .Select(g => new 
        {
            count = g.Count()
            ProductId = g.Key
        })
        .ToList();
    return Json(result);         
}

Answer 2

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);         
    }

Answer 3

I've used it this way before.

select ProductId , count(*) as TotalId from dbo.PoDetail group by ProductId