SELECT AS in Linq with WHERE clause

Question

I am trying to populate a drop down list with a Linq query. However, I need to have FirstName and Surname as one field. Concatenating in the DDL.DataTextField returns a field not found error.

SQL would be something like this:

SELECT (FirstName + SPACE + Surname) AS FullName FROM Table WHERE ID=1

Current Linq:

public IList<mytable> GetNames(int p_ID)
{
    return db.mytable.Where(c => c.ID_fk == p_ID).ToList();
}

Answer 1

You can use this, if you only need the full name:

public IList<string> GetNames(int p_ID)
{
    return db.mytable.Where(c => c.ID_fk == p_ID)
                     .Select(x => x.FirstName + " " + x.Surname)
                     .ToList();
}

Answer 2

you could try this:

return db.mytable.Where(c => c.ID_fk == p_ID).Select(c=>c.FirstName + " " + c.Surname). ToList();

so you have a list of strings