Linq to SQL Design question

Question

Often I need to combine data from multiple tables and display the result in a GridView control.

I can write a Linq query inline in the Page_load event, return an anonymous type that combines all the fields that I need, and databind the result to the GridView control.

• Problem: I use 'helper methods' as described by Scott Guthrie on his blog. Such a helper method cannot return an anonymous type. The query would have to be inline for this approach.

I can write a database view that returns the data that I need, and write a helper method with a query against this (new and known) type that it returns.

• Problem: I will need a lot of views in my database schema, and I will introduce a lot of redundant aspects of my data. I also lose some of the advantage of using Linq - removing all business logic from the database.

I would like to take an approach that lets me keep the Linq queries in helper methods, yet allows me to access all the attributes that I need on the grid in their respective databinding expressions. Can this be done?

Answer 1

I don't know if there is a viable way to achieve this using anonymous types. But I have a suggestion that will work in WinForms, but I am not sure about ASP.NET.

What you need is a type with properties where neither the number of properties, nor the types and names of the properties are known at compile time. One way to create such a thing is ICustomTypeDescriptor.

You have to create a type implementing this interface with an private backing store of objects backing the properties returned by the query for one row from the query. Then you implement GetProperties() to return one PropertyDescriptor per column and PropertyDescriptor.GetValue() and PropertyDescriptor.SetValue() to access the backing array.

By implementing PropertyDescriptor.Name you will get the correct column name; this will probably require another backing store storing the property names. And there is a lot more to implement, but in the end your new type will behave almost like a normal type - and now the if - if the control you are binding to knows about and uses ICustomTypeDescriptor.

UPDATE

I just found an bit of text stating that ASP.NET data binding knows and uses ICustomTypeDescriptor.

Answer 2

I asked the wrong question, as I frequently do. What prompted me to look into anonymous types was an apparent limitation of the GridView - my inability to use a databinding expression in an <asp:BoundField> (the DataField parameter only accepts column names of the table that the Linq query pulls in).

Turns out that in a TemplateField it is possible to use Eval and access members of the Linq data item, and Linq takes care of the query for me. In other words, I can keep the query in my helper method, have it return a primary database table type (e.g. Account), and I bind the Accounts to the GridView. In the databinding expressions I can access data members of the Account objects that reside in other tables, without having to explicitly pull them in in the query. Perfect.

Answer 3

Scott's earlier post in the series talks about shaping the result set before inserting into a grid:

Part 3 - Querying our Database

Scroll down to "Shaping our Query Results".