IEnumerable vs IQueryable object - LinQ to SQL

Question

At first I had an IEnumerable object called "Employees" that had all of its properties mapped to a single class "Employee".

I passed the IEnumerable object Employee to a private method, that parsed through the properties and mapped it to a data-table.

   private void createEmployeesDataTable(IEnumerable<Employee> Employees)
    {
    .... stuff here to define the datatable ....

       foreach (var elem in Employees)
       {
           var row = dataTable.NewRow();
           row["Name"] = elem.JobTitle;
           row["Address"] = elem.Address;
           row["Phone"] = elem.Phone;
           row["DateOfHire"] = elem.HireDate;
           dataTable.Rows.Add(row);
       }
    }

Worked like a charm.

Now I have a bunch of classes, mapped to the database and an IQueryable object Employees. The code is simplistic.

    DataContext db = new DataContext(System.Configuration.ConfigurationManager.ConnectionStrings["employeeDB"].ConnectionString);
    Table<Employee> Emp = db.GetTable<Employee>();
    Table<Address> Add = db.GetTable<Address>();
    Table<BusinessEntityAddress> BE = db.GetTable<BusinessEntityAddress>();
    Table<Phone> Phone = db.GetTable<Phone>();
    Table<State> State = db.GetTable<State>();

    var Employees =
        from Employi in Emp
        join PhoneNumber in Phone on Employi.BusinessEntityID equals PhoneNumber.BusinessEntityID
        join BusEntity in BE on Employi.BusinessEntityID equals BusEntity.BusinessEntityID
        join Addy in Add on BusEntity.AddressID equals Addy.AddressID
        join StProv in State on Addy.StateProvinceID equals StProv.StateProvinceID
        where Employi.HireDate > userInputLimit
        select new {DateOfHire = Employi.HireDate, Address = Addy.AddressLine1 + Addy.City + StProv.StateProvinceCode + Addy.PostalCode,
                    Phone = PhoneNumber.PhoneNumber, Name = Employi.JobTitle};

Now I am passing this object to the private method, to create a Data-table, except I pass it as an IQueryable of anonymous type. The reason being, my object now has properties derived from multiple classes and no longer from a single, employee class:-

private void createEmployeesDataTable(IQueryable Employees)
        {
.... how can I still access all of its properties and bind it to the datatable?? .... 
}

When I put a breakpoint on the IQueryable Employees object, I can see it has all the property names and values correctly stored in it. But I cannot seem to access them via code....

A sample of my class:-

 [Table(Name="HumanResources.Employee")]
    public class Employee
    {
        private int _BusinessEntityID;
        [Column(IsPrimaryKey = true, CanBeNull=false, Storage = "_BusinessEntityID")]
        public int BusinessEntityID
        {
            get
            {
                return this._BusinessEntityID;
            }
            set
            {
                this._BusinessEntityID = value;
            }

        }

        private string _JobTitle;
        [Column(Storage = "_JobTitle", CanBeNull=false, DbType="nvarchar(50) NOT NULL")]
        public string JobTitle
        {
            get
            {
                return this._JobTitle;
            }
            set
            {
                this._JobTitle = value;
            }
        }


        private DateTime _HireDate;
        [Column(Storage = "_HireDate", CanBeNull=false)]
        public DateTime HireDate
        {
            get
            {
                return this._HireDate;
            }
            set
            {
                this._HireDate = value;
            }
        }
    }

Answer 1

When I put a breakpoint on the IQueryable Employees object, I can see it has all the property names and values correctly stored in it. But I cannot seem to access them via code.

That's because the non-generic IQueryable interface knows nothing about the underlying types. You'd have to use the generic version (IQueryable<T>) to see the properties of the items at design-time.

However, since you're projecting to an anonymous type (and not a collection of Employee objects despite what you're named the variable), you don't know the type name at compile-time, so you can't specify the type T to use for IQueryable<T>.

The best solution would be to define a concrete type instead of using an anonymous on so that you can access the field at compile-time. You could use dynamic, but then you're delaying the property binding to run-time and won't catch any errors at compile-time.

The class definition would be something like:

public class EmployeeView
{
    public DateTime DateOfHire {get; set;}
    public string AddressLine1 {get; set;}
    public string City {get; set;}
    public string StateProvinceCode {get; set;}
    public string PostalCode {get; set;}
    public string Phone {get; set;}
    public string Name {get; set;}
}

and your projection would be:

select new EmployeeView {
    DateOfHire = Employi.HireDate, 
    AddressLine1 = Addy.AddressLine1,
    City = Addy.City,
    StateProvinceCode = StProv.StateProvinceCode,
    PostalCode = Addy.PostalCode,
    Phone = PhoneNumber.PhoneNumber, 
    Name = Employi.JobTitle};

Now you can specify the type in :

private void CreateEmployeesDataTable(IEnumerable<EmployeeView> employees)
{
    .... 
}

Note that I have changed the casing to .NET standards - classes with capitalized camel case and variables with lower-case camel case