CODEWITHSUNDEEP
asp.netentity-frameworklinqasp.net-corelinq-to-entities
Adi
Adi

Reputation: 525

Linq to Entities method: Select and ToListAsync

I want to retrieve only a few columns in the query. I want to achieve this query using entity framework:

select FirstName from Employee

However, I am not able to use Select and ToListAsync at the same time. I am getting an error and it looks like I can't use both at the same time. Is there any other way to do this?

[HttpGet]
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
{
     return await _context.Employee.Select(s => s.FirstName).ToListAsync();
}

Upvotes: 0

Views: 3829

Answers (4)

Ho&#224;ng Gia Thi&#234;n
Ho&#224;ng Gia Thi&#234;n

Reputation: 50

It's wrong syntax. You just change like this:

[HttpGet]
public async Task<ActionResult<IEnumerable<string>>> GetEmployee()
{
     return await _context.Employee.Select(s => s.FirstName).ToListAsync();
}

or 

[HttpGet]
public async Task<IActionResult> GetEmployee()
{
     return Ok(await _context.Employee.Select(s => s.FirstName).ToListAsync());
}

Upvotes: 1

Ryan
Ryan

Reputation: 20116

Since your return type is ActionResult<IEnumerable<Employee>> .simply use below code to return List of Employee

[HttpGet]
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
    {
        var result = await _context.Employee
                         .Select(s => new Employee
                         {
                             Name = s.FirstName
                         }).ToListAsync();
        return Ok(result);

    }

Upvotes: 1

Steve Py
Steve Py

Reputation: 34698

Gert's comment covered the reason for the error. It will be due to the function expecting a set of Employee entity entities but you are trying to just return a set of employee names by using Select(s => s.FirstName)

If your intention is to return just Employee names, then update the method signature to something like:

public async Task<ActionResult<IEnumerable<string>>> GetEmployeeNames()

Typically situations like this will be when you want to return something like search results or a summary list. You don't necessarily want to return everything about employees (and possibly serializing related data on top of that) just for displaying a list of employee names for users to select from. Still, returning just an employee name isn't much use on its own in case you want to select one of them and request more information or perform an action against them. (Not to mention you could have two "Peter"s as employees...)

In these cases it is helpful to define a simple view model to represent just the data the front end will need. For example the employee name and ID: 

[Serializable]
public class EmployeeSummaryViewModel
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
}

Then the method to retrieve the employees:

[HttpGet]
public async Task<ActionResult<IEnumerable<EmployeeSummaryViewModels>>> GetEmployeeSummaries()
{
    return await _context.Employee
        .Select(s => new EmployeeSummaryViewModel
        {
            EmployeeId = s.EmployeeId,
            Name = s.FirstName + " " + s.LastName
        }).ToListAsync();
}

The view model is a simple serializable C# class. By leveraging Select to populate it, EF can generate a very efficient query to return just those fields we want. This can include fields from related tables such as a Role or such without needing to worry about eager loading or serializing everything from those related tables.

In the above example the view model contains the ID for each employee returned so we can pass that ID to future calls if needed, such as selecting an employee to load a complete view of, or perform an action against. The server also formats the name. Alternatively you can return the FirstName and LastName and leave the formatting up to the client side.

Upvotes: 2

crgolden
crgolden

Reputation: 4634

Based on your code, I think you would need something like this:

[HttpGet]
public async Task<ActionResult> GetEmployee()
{
    var employeeNames = await _context.Employee.Select(s => s.FirstName).ToListAsync();
    return Ok(employeeNames);
}

Upvotes: 1

Related Questions