What is the right return value for a LINQ join method?

Question

I am working on a 3-Tier application. Also I am using LINQ to SQL for data access.

The DataLayer has a function which returns a table of customers:

public Table<Customer> getCustomers()
{
    DataContext context = new DataContext();
    Table<Customer> customerTable = context.GetTable<Customer>();

    return customerTable;
}

It is provided to the Business-Layer where the results are being passed to the Presentation-Layer as IEnumerable<Customer>:

public IEnumerable<Customer> getCustomers()
{
    CustomerDAL customerDAL = new CustomerDAL();

    return from c in customerDAL.getCustomers() select c;            
}

In the Presentation-Layer I am simply using the IEnumerable for a DatagridView's DataSource.

What if I had another table like "Information" and the according customerDAL.getInfo() table? Now I want to make a join query in a method in the Business-Layer. I imagined it something like this:

public IEnumerable<Customer> getCustomerInfo()
{
    CustomerDAL customerDAL = new CustomerDAL ();

    return from c in customerDAL.getCustomers()
                  join i in customerDAL.getInfo() on c.id equals i.InfoID
                  select new { c.id, c.Name, i.DateTime, i.Project };
}

The problem is IEnumerable needs the object's type. My return value isn't a customer-table anymore, but a combination of a customer- and a info-table. Did I get it right? What would be the right choice for a return value here?

After your advice I created a custom class, CustomerInfo.cs:

public class CustomerInfo
    {        
        string name { get; set; }
        long id { get; set; }
        string dateTime { get; set; }
        string project { get; set; }


        public CustomerInfo(long _id, string _name, string _date, string _project) 
        {
            name = _name;
            id = _id;
            dateTime = _date;
            project = _project;
        }
    }

Then I am calling the exact same method as described by Reed. But in the Presentation-Layer when I set the DataSource I get the exception:

The query contains references to items defined on a different data context.

Actually this is not true all Entity classes are in the same .dbml file. What could be wrong?

Answer 1

Regarding the second error:

Then I am calling the exact same method as described by Reed. But in the Presentation-Layer when I set the DataSource I get the exception: The query contains references to items defined on a different data context.

Most likely your DAL is instantiating a separate instance of the context for each table that you are returning (Typical in a ActiveRecord patterned implementation). In order for the join to work, both tables need to be retrieved by the same context object. You may want to modify your DAL so that you inject the context in the constructor of the DALs so that you can centralize the lifetime of the context.