LINQ: How to Select specific columns using IQueryable()

Question

I need to select only two columns from Hospital table, HospitalId and Name. i tried the below code it selects all columns from Hospital table which lead to slow performance. Please help me to select only two columns from Hospital 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);
}

Answer 1

IQueryable<T> executes select query on server side with all filters. Hence does less work and becomes fast.

IEnumerable<T> executes select query on server side, load data in-memory on client side and then filter data. Hence does more work and becomes slow.

List<T> is just an output format, and while it implements IEnumerable<T>, is not directly related to querying.

So,

var list = query.ToList().Select(w => new HospitalInfo()
    {
        Id = w.ID,
        Name = w.Name 

    }).ToList();

In your code you use query.ToList(). This means at first it pull all data into memory then apply Select query.If you want to retrieve HospitalID and Name then remove ToList() then your code like

   var list = query.Select(w => new HospitalInfo
        {
            Id = w.ID,
            Name = w.Name     
        }).ToList();