Has anybody had success doing this? I have a very simple app that I want to provide a nice heretical view. It seems like it should be very simple. I have three tables Classes, Employees, EmployeeClasses (the intersection table). I fetch the data like this

ClassCollection cc = new ClassCollection();
ISortExpression sorter = new SortExpression(DAL.FactoryClasses.SortClauseFactory.Create(ClassFieldIndex.Dept, SortOperator.Ascending));
sorter.Add(DAL.FactoryClasses.SortClauseFactory.Create(ClassFieldIndex.CourseName, SortOperator.Ascending));
IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.ClassEntity);
prefetchPath.Add(ClassEntity.PrefetchPathEmployee);
cc.GetMulti(null, 0, sorter, null, prefetchPath);
gridClasses.DataSource = cc;
gridClasses.DataBind();

I checked profiler and it looks like I am getting the right data back.

The problem is when I bind all I get is Classes and a plus sign to only displays the EmployeeClass information (basically only the two foreign keys)? Obviously the EmployeeClassId, ClassId and EmployeeId are meaningless. So I get:

-ClassName
--------EmployeeClassId ClassId EmployeeId (EmployeeClass.EmployeeId and Employee.EmployeeId is 1:1)

What I would like is to display properties for the EmployeeEntity so it would look like this:

-ClassName
--------FullName PhoneNumber ect.

The Infragistics grid give you a BaseColumnName to tell each band what to display. Basically I would like it if I could say BaseColumnName = Employee.FullName instead of BaseColumnName = EmployeeId (from the EmployeeClass table) etc. But obviously that will not work. Anybody had success with this? I am sure this has been done millions of times by thousands of people but I am struggling for a solution. Thanks in advance.

As you're using asp.net, I think you should use the m:n relation fetch and bind that data. This won't get the intermediate entities though, but as it's asp.net, the form is readonly anyway.

As you're using asp.net, I think you should use the m:n relation fetch and bind that data. This won't get the intermediate entities though, but as it's asp.net, the form is read only anyway.

Thress questions: 1) In you documentation you say this:

Prefetch Paths can also be used to fetch m:n related entities, they work the same as other related entities. There is one caveat: the intermediate entities are not fetched with an m:n relation Prefetch Path. For example, if you fetch a set of Customer entities and also their m:n related Employee entities, the intermediate entity, Order, is not fetched.

This is exactly the behavior that I want however with the above code I am still getting the intermediate entity? Is there something wrong with the above code? 2) If you don't fetch the intermediate entity how does the datagrid know how the two entities are related. It seem like at the point your remove the intermediate entity there is a disconnect? 3) What do you mean by using the m:n relation fetch? Is there a code example of this?

If I didn't mention it above (although I am sure you can tell from the code), I am using the self-servicing method.

Sam wrote:

As you're using asp.net, I think you should use the m:n relation fetch and bind that data. This won't get the intermediate entities though, but as it's asp.net, the form is read only anyway.

Thress questions: 1) In you documentation you say this:

Prefetch Paths can also be used to fetch m:n related entities, they work the same as other related entities. There is one caveat: the intermediate entities are not fetched with an m:n relation Prefetch Path. For example, if you fetch a set of Customer entities and also their m:n related Employee entities, the intermediate entity, Order, is not fetched.

This is exactly the behavior that I want however with the above code I am still getting the intermediate entity? Is there something wrong with the above code?

No it's not wrong, though the grid might load teh intermediate entities as well through lazy loading, is that the case? (you can see that by clicking open a row and then see the queries executed in the profiler)

2) If you don't fetch the intermediate entity how does the datagrid know how the two entities are related. It seem like at the point your remove the intermediate entity there is a disconnect?

It doesn't know that, but it likely causes the lazy loading code to be triggered somewhere which thus fetches the other entities as well.

3) What do you mean by using the m:n relation fetch? Is there a code example of this?

I didn't look closely at your code, you're using teh m:n relation to fetch employee.

Lazy loading, of course that make perfect sense. So, what would be your suggestion for "flattening" out hierarchical information in a datagrid? Say I have an row with a employeeID as a foreign key. I probably don't want to display the key (at least exclusively) but would rather display the employees name or something. Do you do this with: 1) A view in your database (don't really want to create a new view and regenerate the code each time I want to do this). 2)Create a DataList on the fly (TypedListDAO), in code (looks like kind of a lot of code each time). 3)Use a ListView (same issues as #1) 4)Some other way that I am not thinking of. Thanks in advance.

I'd suggest a typed list. They're made for flattening out hierarchies to a single table-style structure.

Already implemented it. Works great (not as many lines of code as I thought). Thanks for the help!