How to get distinct values from set of rows matching condition

Question

I have a table of following nature.

+----+-----------+-----------+------+---------+------+
| Id | AccountId | ProjectId | Year | Quarter | Data |
+----+-----------+-----------+------+---------+------+
| 39 |       163 |        60 | 2019 |       2 |    0 |
| 40 |       163 |        60 | 2019 |       2 |    8 |
| 41 |       163 |        61 | 2019 |       2 |    1 |
| 42 |       163 |        61 | 2019 |       2 |    2 |
+----+-----------+-----------+------+---------+------+

I want to get distinct ProjectIds as Json using Entity Framework, so far my code looks like this.

    // GET: api/Insight/163/2019/2
    [HttpGet("{accid}/{year}/{qurter}")]
    public async Task<IActionResult> GetSurveys([FromRoute] long accid, [FromRoute] long year, [FromRoute] long qurter)
    {
        //This code gives me the error.
        return await _context.CustomerSatisfactionResults.Select(x=>x.ProjectId)
            .Where(x => x.AccountId == accid && x.Year == year && x.Quarter == qurter).ToListAsync();
    }

When I hit this endpoint with parameters, /163/2019/2 I want a Json responce as,

[
  "60", "61"
]

But I get the following error. What have I done wrong?

Answer 1

The reason you get an error is because you apply the Where condition on a projected sequence that includes only ProjectId. You should use Where before Select.

To get the distinct values, use the Enumerable.Distinct method:

return await _context.CustomerSatisfactionResults
   .Where(x => x.AccountId == accid && x.Year == year && x.Quarter == qurter)
   .Select(x => x.ProjectId)
   .Distinct()
   .ToListAsync();