Use LinqDataSource With Joined Tables

In ASP.NET with C#, the LinqDataSource is used for simple selects, updates, and deletes of rows of data in a single database table. That is fine when the programmer only needs to work with one table, but many programmers need to select from multiple tables joined together. LinqDataSource does not normally support joined tables, but there is at least one way to trick it into doing so.

The situation for which I concocted this method was when I had a single table from which I needed to select Products based on three fairly complicated parameters. The plain LinqDataSource did not support filtering based on my custom TreeView control, meaning I had to intercept LinqDataSource's OnSelecting event and write some code which would use LINQ to select the required Products, with custom WHERE clauses to handle my special TreeView parameters. Thus, only the LinqDataSource would work for me: the other DataSources do not allow the programmer to replace the results of the OnSelecting event with a LINQ query. Then, my business needs changed so that I absolutely had to join the Products table with an Events table to get access to some new required data.

That left me stuck between having to use a LinqDataSource for one essential feature, and also LinqDataSource missing the join-table feature which was also essential. I worked around this problem by tricking LinqDataSource into working with joined tables.

The crux of the hack is to intercept the LinqDataSource's OnSelecting event (which I was already doing), and also to add the data fields from the join table to the DBML business object. So in my case, I have a Product object in my DBML file, and I added two data fields to it, which would later hold the data from the join tables. Then, in the OnSelecting method, I fill in those data fields by selecting from the other tables.

var result = (

            from sourceProduct in Db.Products

            join sourceEvent in Db.Events

            on sourceProduct.product_id equals sourceEvent.product_id



            where _selectedCodes.Contains(sourceEvent.event_code)

            && sourceEvent.issue_time >= beginDate

            && sourceEvent.issue_time <= endDate



            select new { sourceProduct.product_id, sourceProduct.xxx, sourceEvent.event_code, sourceEvent.issue_time }

Notice the custom WHERE clause which filters based on a list called _selectedCodes, which is built from my custom TreeView; that feature is not supported by a plain LinqDataSource. Then notice how I choose two data fields from the sourceProduct, but then choose two others from sourceEvent, which is the join table. Those latter two, event_code and issue_time, are the fields which I added to the Product object in my DBML file.

If you do not add the extra fields to you DBML object, then ASP.NET will throw an error when you try to access those fields. (Strangely, it will not throw an error when, in your LINQ query, you assign values into those fields.) For me, this happened when a GridView tried to bind to the selected data in the LinqDataSource.

I hope this helps other programmers who need to use join tables to populate DBML objects gotten via a LinqDataSource.