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?

score 1 · Answer 1 · answered Aug 27 '12 at 20:54

What would be the right choice for a return value here?

If you want to return a strongly typed class, you will need a custom class to represent this type, such as a CustomerInfo class. You'd need to define this class, and include the appropriate properties and constructor. You could then do:

public IEnumerable<CustomerInfo> GetCustomerInfo()
{
        CustomerDAL customerDAL = new CustomerDAL();

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

This would allow you to return the exact information you need, in a strongly typed manner. I would argue that creating a custom class is especially important in this case, as you have this defined as part of your public API.

Would this mean I am running a LINQ query on a custom class which is filled with IEnumerables and I could use the return value as a DataSource? — Prot, Aug 28 '12 at 09:26
@Prot No - you could use LINQ to Objects to filter the results, of course, but it wouldn't be a DataSource that worked against the DB anymore. — Reed Copsey, Aug 28 '12 at 17:31

score 1 · Accepted Answer · answered Aug 28 '12 at 17:31

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.

score 0 · Answer 3 · answered Aug 27 '12 at 20:50

So far everything looks good. If you want to just use the query within the scope of a single method, then using an anonymous class (which is what you're currently doing) is just fine. Since you aren't, you need to create a concrete class for your projection so that you can return it.

The query will become:

select new SomeClassYouAreAboutToCreate { c.id, c.Name, i.DateTime, i.Project };

That class can probably just have a bunch of properties, id, Name, etc. You'll just set those properties in the Select.

Would this mean I am running a LINQ query on a custom class which is filled with IEnumerables and I could use the return value as a DataSource? — Prot, Aug 27 '12 at 21:22

score 0 · Answer 4 · edited May 23 '17 at 10:34

When you create an anonymous object like that, its type is dynamic. So to return a list of anonymous object like that, you would use the following signature:

public IEnumerable<dynamic> 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 };
}

Keep in mind, though, that the dynamic type has its drawbacks. In particular, you lose strong typing, which can introduce hard-to-find bugs. You should consider Servy's answer for a more robust solution.

The dype is not dynamic. It is anonymous an strongly typed. – usr Aug 27 '12 at 21:33 — usr, Aug 27 '12 at 21:33

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

4 Answers4