LINQ: how to select specific columns using IQueryable ()
I only need to select two columns from the hospital table, HospitalId and Name. i tried the below code, it selects all columns from hospital table resulting in poor performance. Please help me select only two columns from hospitals table.
public HttpResponseMessage GetAvailableHospitalsByAjax(System.Guid? DirectorateOfHealthID = null, System.Guid? UnitTypeID = null, string DeviceTypeIDs = null)
{
Context db = new Context();
var query = db.Hospitals.AsQueryable();
if (UnitTypeID != null)
{
query = query.Where(j => j.HospitalDepartments.Any(www => www.Units.Any(u => u.UnitTypeID == UnitTypeID)));
}
if (DirectorateOfHealthID != null)
{
query = query.Where(h => h.DirectorateHealthID == DirectorateOfHealthID);
}
query = query.Where(j => j.HospitalDepartments.Any(u => u.Units.Any(d => d.Devices.Any(s => s.Status == Enums.DeviceStatus.Free)))
&& j.HospitalDepartments.Any(hd => hd.Units.Any(u => u.Beds.Any(b => b.Status == Enums.BedStatus.Free))));
var list = query.ToList().Select(w => new HospitalInfo()
{
Id = w.ID,
Name = w.Name
}).ToList();
return Request.CreateResponse(HttpStatusCode.OK, list);
}
source to share
IQueryable<T>
performs server side query selection with all filters. Consequently, it does less work and becomes faster.
IEnumerable<T>
performs query selection on the server side, loads the data in memory on the client side, and then filters the data. Hence, it works harder and becomes slower.
List<T>
is just an output format, and although it implements IEnumerable<T>
, is not directly related to the request.
So,
var list = query.ToList().Select(w => new HospitalInfo()
{
Id = w.ID,
Name = w.Name
}).ToList();
In your code, you are using query.ToList()
. This means pulling all the data into memory first and then applying the query Select
. If you want to get HospitalID and name and then delete ToList()
then your code will be
var list = query.Select(w => new HospitalInfo
{
Id = w.ID,
Name = w.Name
}).ToList();
source to share
As you execute query.ToList()
, this materializes the entire query with all columns into memory. This is actually a bad habit. Remove that instead, you still have it at the end. The projection Select
you have will only get the columns that match:
var list = query.Select(w => new HospitalInfo()
{
Id = w.ID,
Name = w.Name
}).ToList();
source to share