Many-to-many CRUD

This guide demonstrates how to create CRUD pages for many-to-many relationship with MSSQL, MySQL or PostgreSQL data-source.

Source Code

Many-to-many relationships are usually achieved using junction table that maps two or more tables together by referencing the primary keys of each data table. Typical example is Orders -> OrderDetails -> Products where OrderDetails is the junction table referencing the primary keys of Orders and Products. With Radzen you can create CRUD pages for such scenario with minimal effort:

1. Create new application with .NET server-side project and add new MSSQL data-source connected to Northwind database.

2. Start Create New Page wizard, select Master/Detail DataGrid template, choose Order for Page Schema and OrderDetails for Child Data.

You have now CRUD master/detail with two DataGrid components between Orders and junction table OrderDetails joined with Products table using OData $expand query.

Since Products table is expanded with OrderDetails you can define columns in the details DataGrid component from both OrderDetails and Products

OrderDetails properties are referenced directly and Products properties are referenced using Product sub property.

You can define more columns from Products table, for example Product.UnitsInStock, Product.UnitsOnOrder and Product.Discontinued

You can also change the details heading and Add New dialogs titles to provide more info that this is many-to-many relation between Orders and Products.

If you do not want edit/update for the junction table records you can remove the RowSelect event action. In this case you will have only Add and Delete for the junction table.

3. Run the application

If you want to restrict adding of Products with the same type to only one you can set Add Order Detail page getProducts data-source method invoke $filter parameter to (OrderDetails/any(d:d/OrderID ne ${parameters.OrderID}))

Get Radzen