Since my last post on filtering child collections using Entity Framework, I have had more than a few emails about how to load filtered child DataTables that are part of a master-detail relationship and then get them to save properly through the TableAdapters. Many people are running into the problem where the designer won’t automatically generate the update/insert/delete commands for you when you are writing a select statement that uses multiple tables. Of course you could specify your own statements manually (and often you need to because your Select statement is too complex) but in this case there’s a much better way to specify this filter and still get the benefit of the designer creating your commands.
For instance, let’s take the same database tables Customer and Orders that we’ve been working with:
NOTE: If you are unfamiliar with how to set up and work with master-detail (parent-child) tables check out this "How Do I" video first.
So say we want to only pull up the Orders on the Customer who’s LastName is “Massi” — meaning we want to filter both the parent (Customer) and child (Orders) on Customer.LastName. There’s a couple ways to do this.
Specifying a New Query
TableAdapters are great at managing your queries. By default when you use the designer to select related tables it will automatically create default Fill and GetData select methods for you as well as generate the update//insert and delete statements for you. If you look in the properties for the TableAdapter you will see a SelectCommand generated for you that selects ALL the fields and ALL the rows of the table in the database. In 99.999% of the cases you want to filter these rows for scalability reasons. (And I urge you to do so).
Also notice that Visual Studio has created Insert/Update/Delete commands as well for us. We can easily specify a new query that filters our rows and still take advantage of these update/insert/delete commands. We want to do this for both our tables.
First right-click on the child TableAdapter (in this case OrdersTableAdapter) and select Add –> Query. Then choose your command type, here I’ll leave the default “Use SQL Statements”. Click Next. Then choose your query type, in this case choose the default “SELECT which returns rows”. Click Next. Now you can write your select statement.
It is VERY IMPORTANT that you only select the fields from the child (Orders) table and not include fields from other tables in the result set. Otherwise the generated commands will not work. You can filter the rows any way you like with a WHERE clause (and you definitely should) but keep in mind that if you select fields from other tables to be included in the result set, then you will need to specify your own Insert\Update\Delete commands above.
To write our filter on the child Orders table I’ll use the command builder to join the Customer table and then use the LastName in the WHERE clause.
Here’ I’m creating a parameterized query so that I can specify the LastName on the Form and not hard-code it here. Notice that the structure of the result set (the fields in the SELECT clause above) exactly match the fields in the OrdersDataTable.
FROM Orders INNER JOIN
Customer ON Orders.CustomerID = Customer.CustomerID
WHERE (Customer.LastName = @LastName)
Click OK to get out of the Query Builder (if you were in there) and then Click Next. Now you get to choose which methods to generate and what they should be named. For this example I named them FillByLastName and GetDataByLastName. This creates methods on the TableAdapter that you can now use to query the child rows.
Now repeat this process for the parent table (Customer). Select the CustomerTableAdapter in the dataset designer, right-click and select Add –> Query. Same steps as before except the SELECT statement is much easier, no JOIN necessary, just a filtered WHERE clause.
SELECT CustomerID, LastName, FirstName, Address, City, State, ZIP, Modified
WHERE LastName = @LastName
I named the methods the same as I did for the child, FillByLastName and GetDataByLastName.
Now you can design your master-detail form. From the Data Sources Window expand and then drag the parent (Customer) and then drag the related Orders (the one under Customer) onto your Windows form. Basically design your winform like normal and let Visual Studio generate the code to load and save for you.
The only thing you have to do now is change the method names on the TableAdapters (and make sure they load in parent then child order) in the form Load handler:
Private Sub Form1_Load() Handles MyBase.Load Me.CustomerTableAdapter.FillByLastName(Me.OMSDataSet.Customer, "Massi") Me.OrdersTableAdapter.FillByLastName(Me.OMSDataSet.Orders, "Massi") End Sub
Everything else will work like before but now we are filtering both the tables. You can add as many new queries to the TableAdapters as you need. As long as the fields in your query (SELECT clause) match the structure of your generated DataTable then you can use the generated update/insert/delete commands.
But what if you already have hundreds of forms that use the default Fill or GetData methods and you really need to set up a filter on these SelectCommands because you don’t want to have to change all your forms? That’s pretty easy too.
Filtering the Default GetData and Fill Methods
If we want to modify the default Fill and GetData methods to support this filter then there’s a little trick we can do. Once the DataSet is set up with your “unfiltered” parent-child tables, open up the properties window and expand the SelectCommand (like pictured above) and edit the CommandText property for both of the TableAdapters. Specify the same select statements as above. Once you modify the property Visual Studio will ask you “Do you want to regenerate updating commands based on the new command text? (Current updating commands will be overwritten)”. Just say NO.
Now the calls to GetData and Fill will have a filter set but the generated commands will not be overwritten!
I hope this clears things up for folks. Remember that it’s best practice to put filters on your queries to narrow your result sets – this puts a lot less strain on your databases and networks. There’s a couple tricks you need up your sleeve to do this in Visual Studio, but it is the way it is so that the dataset designer can handle complex and completely custom scenarios too, like multi-table views and stored procedures. Hmmm… maybe it’s time for a How Do I video on that…. ;-)