0

I'm trying to get to grips with Entity Framework and there is one thing that's really tripping me up. It doesn't help that I'm still not totally sure of the terminology, and I'm trying to avoid learning LINQ at the same time, so googling is difficult.

I have two tables, company and addresses with a 1-to-many relationship. If I write the following:

ObjectQuery<Company> companies = queryContext.Companies.Include("Addresses");

It looks like I am getting what I want (companies -> Results View[0].Addresses.Count is > 0)

What I'd like to do now is bind the company names and all addresses to a gridview in an ASP.NET application

this.CompaniesGrid.DataSource = companies;
this.CompaniesGrid.DataBind();

<asp:GridView runat="server" ID="CompaniesGrid" AllowSorting="true">
    <Columns>
        <asp:BoundField DataField="Name" />
        <asp:BoundField DataField="Address" />
    </Columns>
</asp:GridView>

This on its own throws an error (A field or property with the name 'Address' was not found on the selected data source) - I think because companies -> Results View[0].Name exists but .Address doesn't (because it's buried in the Addresses relationship). Binding to Addresses.Address doesn't help either.

I found one really ugly workaround at the bottom of this thread but I would rather avoid it if possible.

Is there any way to 'flatten' my results so that the top level objects provide addess to all included fields?

Any help much appreciated!

tomfumb
  • 3,669
  • 3
  • 34
  • 50

2 Answers2

6

Your objectquery is returning a graph. While I think you really ought to suck it up and learn linq if you want to program in .NET (big grin ...except really, you should), you could write a projection query to bring back flattened results and bind those in ASP.NET.

The trick to flattening is to start with the "child" in the relationship.So something like this:

grid.datasource= context.Addresses.Select
          (a=>new {a.Company.CompanyName,a.Street, a.City}).ToList();

(code not guaranteed since the stackoverflow UI does not provide .net intellisense or compile time checking. sheesh. ).

Julie Lerman
  • 4,602
  • 2
  • 22
  • 21
  • Thanks for the advice. In the end I went for a hybrid approach with linq and your child-up approach: `IQueryable query = from adds in context.Addresses select new { adds.Company.Name, adds.Address1, adds.City };` – tomfumb Nov 24 '11 at 01:21
  • great. LINQ is really awesome. Worth the effort to learn. FWIW, my code *is* LINQ. The difference is that in the end I'm using the LINQ method, ToList, to execute the query and return the results. I'd normally separate that further so that the UI code doesn't even know about EF. But I'm binding the grid to the list of results, not directly to a query. – Julie Lerman Nov 24 '11 at 14:05
  • aha I'm still learning - I got the `ToList()` call but when I started looking at LINQ I only saw the query syntax, not the alternate method syntax using functions like Select() – tomfumb Nov 25 '11 at 17:51
  • **The trick to flattening is to start with the "child" in the relationship.** - That comment there is a life saver! I'm new to EF and have been raking my brains to get a hierarchy flattened. Now it is as clear as day. Thx! – Karthic Raghupathi Oct 26 '12 at 18:42
0

I don't completely get why you find the solution to use a template field is ugly?

Bound fields can only be bound to regular properties. To bind to a navigational property you have to use a Template field.

So the code in the thread you mentioned solves your problem.

If you really find this ugly you could implement your own bound field and make it support nested bindings but I think using a template field is a nicer solution.

Wouter de Kort
  • 39,090
  • 12
  • 84
  • 103
  • I guess I considered it ugly because I figured there must be a better way to do it - isn't this part of the reason we want to abstract data? If a company has 1 or more addresses I don't care if the db stores it in a separate table. Maybe I need to create a new entity which bridges the two tables? – tomfumb Nov 23 '11 at 22:18
  • 1
    Creating a separate DTO is a possibility but then you have the coupling at the point you transform your entity into a DTO. You could also initialize your Gridview by using a LINQ query that translates into an anonymous type with flattened data. – Wouter de Kort Nov 23 '11 at 22:22
  • Ultimately I went with your second suggestion (linq flattened data). Having spent more time on this and found a workable solution I'm more convinced that the eval workaround is ugly as it gives no compile-time checking and, in my experience, anything written between asp tags in an aspx is nasty to work with – tomfumb Nov 24 '11 at 01:22