Udostępnij za pośrednictwem

LINQ to DataSet Data Binding – LinqDataView Restriction Joins

  • Artykuł

LINQ To DataSet Data Binding – LinqDataView restriction joins

In previous episodes

In the last post I made on LINQ To DataSet Binding, I talked about how to create a LinqDataView. I’m going to spend some time today talking about some of the interesting applications that the LinqDataView allows. This is far from a comprehensive list, and I’m sure that people will even more creative ways to use LinqDataView in the future!

Our old friend, the AdventureWorks Schema

All the examples that I’m working with today are based on the AdventureWorks database, in particular, the SalesOrderHeader, SalesOrderDetail and Customer tables. The examples use a Typed DataSet with this structure, but all these techniques are equally usable with both Typed and untyped Datasets.

Join – where art thou?

One of the most common requests for the DataView is to allow joins, yet the LinqDataView doesn’t allow the Join operator, so what gives? We wanted the LinqDataView to usable in all the same places and ways as the original DataView, and this required that we maintain the same capabilities. However, all is not lost. The most common reason for needed a join is to perform a restriction join, which is something that the LinqDataView can easily do. What do I mean by a restriction join?

There are a lot of situations where the joined table represents a lookup table, for example US states. A restriction join is where you need to filter the primary based on the values of a joined table. In this case, the US states. An example from our schema is Orders and Order Details. You may want to filter Orders based on some query against the Order Details, say when a particular quantity in one of the associated Order Details is over some threshold value.

Because the LinqDataView uses lambda methods (which eventually become delegates) for the Where clause, we can do whatever we want to in the delegate, including the join!

Parent Joins

The ability to have any code in the Where clause can be used in combination with the GetParentRow method (including the Typed DataSet versions, which are simply the name of the parent name) to perform a restriction join. For the DataSet that we are using, you might need to find all the orders for a particular customer. This can be done by doing a query on the SalesOrderHeader table, with a parent restriction join to the Customer table. Such a query might look like the following.

var query = from order in adventureWorksDS1.SalesOrderHeader

where order.CustomerRow.AccountNumber == "AW00000015"

orderby order.OrderDate descending

select order;

DataView view = query.AsDataView();

Child Joins

Sometimes you need to restrict a table by values in a child table. This is something that was quite difficult to do in the past, but with LINQ to DataSet it becomes much easier. You can put anything in the Where clause, including another query! This, in combination with the GetChildRows method (including the Typed DataSet versions, which is the name of the child DataTable), allows you to write powerful child restriction joins.

There is one little wrinkle. The GetChildRows methods return an array of DataRows from the child table. This is where some of the more exotic LINQ operators really come in handy. For example, the Any operator takes a lambda predicate, and returns true if any of the source elements match. To use Any (and most of the other operators I’ll be talking about) you need to use the lambda syntax – you can’t use the SQL like LINQ queries that you might be used to.

Say we want to find all the Orders with an Order Detail that has a quantity greater than 20, perhaps to send them an entire package or pallet. This query would look like the following

var query = from order in adventureWorksDS1.SalesOrderHeader

where order.GetSalesOrderDetailRows().Any(row => row.OrderQty > 20)

orderby order.OrderDate descending

select order;

DataView view = query.AsDataView();

If you don’t mind writing a sub-query, you can convert the above query into the following.

var query = from order in adventureWorksDS1.SalesOrderHeader

where (from od in order.GetSalesOrderDetailRows()

where od.OrderQty > 20

select od).Count > 0

orderby order.OrderDate descending

select order;

DataView view = query.AsDataView();

There are a number of other operators that really come in handy here. These operators include Any, All and Contains. These are all operators that work over a list (list the Child rows of a relation) and return a Boolean that can be used as the criteria for the join.

Summary

I’ve shown a few different ways that LinqDataView can be used, and some of the scenarios where it makes sense. It’s a powerful way to get data binding into your application, and is especially useful where your application is already based around DataViews.

As you work with LinqDataView, you might find additional scenarios that it works well for. I would love to hear about these – a creative use of technology is something I love to hear about.

In the next post, I’m going to talk about another data binding option with LINQ to DataSet – CopyToDataTable. There will also be a post on fast index usage in the mix somewhere.

BTW, I’d love to hear what parts of these posts are the most useful for you.

Thanks,
Erick

 

LinqDataView Restriction Join.zip

Comments

  • Anonymous
    October 25, 2007
    Just keep the posts coming!  They are all very informative, and are giving me some new Ideas to experiment with.