Select only few columns from a table to map with Odata Select query on C# Web API

Question

We have a table in the database which has multiple columns. We only want a query which will return only requested columns. These requested columns will from Odata Select Query requested using C# Web API.
For Example, I have an EmployeeTable which has below structure in SQL

And I am calling below sample API to get the results
http://domain/controller/method?Select=EmployeeID,EmployeeName

This query should get only above 2 columns from the database; in the current scenario - Database returns all the columns and Odata configurations in Web API filters out the response before returning it to the user
Please help with how to implement this in the database.

Answer 1

I am not aware of .Net but the OData query for select should be something like below. Dollar sign is required

http://localhost/odata/Products?$select=Price,Name

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

This is why one uses the DTO pattern where the result can be shaped, properties omitted, and returned by using a DTO.

https://www.codeproject.com/articles/1050468/data-transfer-object-design-pattern-in-csharp

https://learn.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

Answer 3

Solution is simple. Odata behaves like this :

if your controller returns an IEnumerable type => Odata filter your returned list results (in this scenario possibly a list created by all selected columns from db)
if your controller returns IQueryable => Odata selects only requested columns from db.

Key point to this problem is controllers return type, if you want the real benefit of using odata you must return an IQueryable object and let the odata do the rest for you...