.NET: Use Entity Framework to Create Database Command and Execute a SQL Statement to Return Data

Need a custom SQL query to return data while using Entity Framework? You can use the EF data context to create a database command and execute any query you want.

copy

public class User
{
	public int Id { get; set; }	
	public string Email { get; set; }
}

public async Task<List<User>> List()
{
	var users = new List<User>();

	var query = @"
			select 	Id
					,Email

			from	Users		
		";
		
	//this assumes you have an EF db context part of this object
	using (var command = this._DbContext.Database.GetDbConnection().CreateCommand())
	{
		command.CommandText = query;
		command.CommandType = CommandType.Text;

		this._DbContext.Database.OpenConnection();

		using (var result = command.ExecuteReader())
		{                    
			while (result.Read())
			{
				var user = new User();
				
				//the ordinal number is based on the order of the fields returned in your query
				user.Id = result.GetInt32(0);
				user.Email = result.GetString(1);				

				users.Add(user);
			}			
		}
	}
	
	
	return users;
}