LINQ to SQL and One-To-Many Relationships

LINQ to SQL and One-To-Many Relationships

  • Comments 29

Recently a customer sent me a question related to the video I posted on creating a one-to-many form with LINQ to SQL. In that video I use the Northwind database to set up a one-to-many association between the Customers and Orders entities. We all love to use Northwind in demos because developers tend to have this ancient sample database lying around, but sometimes I forget how unrealistic some of the design in there really is, i.e. nullable foreign keys everywhere. All the foreign keys in the child tables like CustomerID in the Orders table and CategoryID in the Product table are nullable which means that the database will allow the child rows to be orphaned.

Typically those types of relationships are set up with non-nullable foreign keys (FK) because the child rows are required to belong to a parent -- meaning the database will keep the integrity of the data intact. Allowing nulls on a FK is usually only used for lookup-style relationships when the lookup value is not required. Setting the FK as not allowing null values and indicating the relationship between the tables also means you can't delete the parent without deleting the children first (unless you specifically set the relationship's delete rule to "Cascade"). Here's a more typical example with Customers and Orders tables, a non-nullable FK, and a relationship set up between them to enforce the data integrity:

Now when working with the O/R Designer and LINQ to SQL in this scenario this common relationship causes the designer to infer the delete behavior of the child incorrectly (if your relationship's delete rule is set to the default "No Action" and your FK does not allow nulls). So when you try to delete the child object which contains a non-nullable foreign key, an error will occur when you try to call SubmitChanges() on the DataContext. For instance, if we bind the LINQ to SQL classes to two grids, delete just the child Order row, and attempt to call SubmitChanges(), we get the following error "An attempt was made to remove a relationship between a Customer and a Order. However, one of the relationship's foreign keys (Order.CustomerID) cannot be set to null." (This error comes from the ChangeTracker called by the DataContext, never making it to the database.)

This behavior may not obvious when using the O\R designer and drag-dropping your database tables onto the design surface to create the entities. Let's look a little bit deeper into the association between our entities. When we drag the Customers and Orders table we defined above from the Server Explorer onto the O/R designer, the following entities and association is automatically created for us:

This model looks like this XML contained in the .dbml file if you look at it in a text editor. (Note: Right-click on the dbml file in the Solution Explorer and select Open With --> XML Editor) Notice the association element under the Order entity.

<?xml version="1.0" encoding="utf-8"?>

<Database Name="MyData" Class="MyDataDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">

 

<Connection Mode="AppSettings" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyData.mdf;Integrated Security=True;User Instance=True" SettingsObjectName="LINQtoSQLDeleteOnNull.My.MySettings" SettingsPropertyName="MyDataConnectionString" Provider="System.Data.SqlClient" />

 

  <Table Name="dbo.Orders" Member="Orders">

    <Type Name="Order">

      <Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />

      <Column Name="CustomerID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />

      <Column Name="OrderDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />

      <Association Name="Customer_Order" Member="Customer" ThisKey="CustomerID" Type="Customer" IsForeignKey="true" />

    </Type>

  </Table>

  <Table Name="dbo.Customers" Member="Customers">

    <Type Name="Customer">

      <Column Name="CustomerID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />

      <Column Name="Name" Type="System.String" DbType="VarChar(50)" CanBeNull="true" />

      <Association Name="Customer_Order" Member="Orders" OtherKey="CustomerID" Type="Order" />

    </Type>

  </Table>

</Database>

In order to be able to delete a child row independently in the database when calling SubmitChanges(), we must indicate DeleteOnNull="true" on this association. Unfortunately the only way to change this is to drop out of the designer and make the change manually in the .dbml file. Luckily, however, you can switch back to the designer and it will not remove this attribute if you continue to modify the model as long as you don't remove the entities entirely. Once we make this change we can now delete just a single Order from the grid and save normally:

<Association Name="Customer_Order" Member="Customer" ThisKey="CustomerID" Type="Customer" IsForeignKey="true" DeleteOnNull="true"/>

The other option to fix this issue is to modify the Delete Rule to "Cascade" on the relationship in the database. In that case the designer correctly infers this attribute on the association. This may be a better solution if you want to always automatically delete the related orders when a customer is deleted in your database no matter what application is working against the data. Additionally, if you apply the Cascade delete rule on your database relationship, then you will not have to manually delete the children first every time the parent is deleted when working with the DataContext. (Note: To enable cascading deletes, just right click on the parent table in the Server Explorer, select "Open Table Definition", right-click on any column and select "Relationships", select the relation and expand the "INSERT and UPDATE Specification" then for the Delete Rule set it to CASCADE.)

However, sometimes you don't have the ability to modify the database you're working with. In our example we can open the DataContext partial class and write some code in the DeleteCustomer partial method so that anytime we delete a customer, the orders will be deleted first. (Note: You'll be able to right-click on the O/R designer's surface and select "View Code" at RTM to access the partial class code, but if you're playing with Beta 2 you'll have to create the partial class file manually).

Partial Class MyDataDataContext

 

    Private Sub DeleteCustomer(ByVal instance As Customer)

        For Each o In instance.Orders

            'Always delete the orders before the customer is deleted.

            'This means that the database relationship's delete rule

            ' does not need to be modified to CASCADE.

            Me.ExecuteDynamicDelete(o)

        Next

        Me.ExecuteDynamicDelete(instance)

    End Sub

 

End Class

I've attached the example above which works with VS 2008 Beta 2 to illustrate the point. In general, LINQ to SQL is a very clean way of accessing your data as true objects in your application and I especially like how it handles changes and transactions automatically for you in the background. You just need to be aware of the types of associations it infers from your schema and drop away from the designer to enable these scenarios.

Enjoy!

Attachment: LINQtoSQLDeleteOnNull.zip
Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • PingBack from http://www.artofbam.com/wordpress/?p=4710

  • Thanks Beth for that great post. Hope the "DeleteOnNull" property can be set from the Designer in the final version...

  • This is great stuff but I'm still getting stuck.  Anyone else getting the error:

    Overload resolution failed because no accessible 'New' accepts this number of arguments

    After modifying the .dbml file?

    The error is in this line, Private db As New DropShipClassesDataContext.

    This error remains even when I remove the added DeleteOnNull="true" text from the .dbml file.

    Prior to editing the .dbml file the project compiles and everything on the form works fine (of course I can't delete rows from the order grid).

    I've tried this with multiple projects and datasources so I would think someone else must be running into this as well.

    Beths code and database is the only one I can get working with out the error.

    TIA to anyone who can offer some insight into this.

  • Hi Jason,

    In Beta 2 when you create the instance of the DataContext, pass it the connection string from your My.Settings, something like:

    Dim db As New DropShipClassesDataContext(My.Settings.MyConnectionString)

    HTH,

    -B

  • That's it Beth, thanks again.

    I don't know if the comments area is the right place for these questions, just let me know if there is a more appropriate place.

    When pressing the delete button I can’t find a way to stop the delete from happening.  I’d like to prompt the user with If MsgBox("Are you sure you want to delete?", MsgBoxStyle.YesNo, "Delete?") = MsgBoxResult.No Then Exit Sub

    Inside the click event of the delete button. The delete happens even if the user clicks no.  It seems as soon as the Sub ends the delete happens no matter where you exit/end the Sub.

  • Hi Jason,

    You can clear out the delete button's default behavior by selecting the BindingNavigator toolstrip and clearing out the DeleteItem behavior. Then you can create your own handler by double-clicking the delete button. When you want to actually delete the item, just call the BindingSource's RemoveCurrent method.

    You can also check out the VB Forums here: http://forums.microsoft.com/msdn/default.aspx?forumgroupid=10&siteid=1

    Cheers,

    -B

  • Thanks. I'll be using this in my linq session at DevConnections! :-)

  • Hey Beth.

    Hows the weather in San Fransisco?

    Great artical.

    You can also use DeleteOnSubmit() as an alternative. Either reference back to the original DataContext or if it isn't available, use Attatch() to attatch the item to a new context.

    e.g.

    DbDataContext _db;

    protected override void OnInit(EventArgs e) {

        _db = new DbDataContext();

        ...

        base.OnInit(e);

    }

    protected override void Dispose() {

        _db.Dispose();

        base.Dispose();

    }

    void Delete(object sender, EventArgs args) {

       ...

       _db.Products.DeleteOnSubmit(product);

    }

  • In yesterday&#39;s post I showed you how to bind LINQ to SQL classes to a Combobox in order to filter

  • In my previous posts here and here I showed how to use LINQ to SQL classes with a couple different Combobox

  • In yesterday's post I showed you how to bind LINQ to SQL classes to a Combobox in order to filter records

  • Awesome, Had the same issue and came accross this posting. Fixed. Shame that its not in the designer :-(

  • My first project at Catalyst just wrapped up this past week, and it involved creating an order fulfillment

  • I have just started using LINQ and everything worked fine until I wanted to put in the combo box.

    I am getting a string to double conversion error. Any ideas on how I correct this. My table uses an "int"

    Thanks in advance for any help

           Dim teamList = From Bowler In Db.Bowlers _

          Where Bowler.TeamNumber <> "" _

          Order By Bowler.TeamNumber _

          Select Bowler.TeamNumber

           For Each TeamNumber In teamList

               Me.ToolStripComboBox1.Items.Add(TeamNumber)

           Next

  • Hi Cheryl,

    If TeamNumber is an integer you cannot compare its value to a empty string. If it's a nullable field then try "Where Bowler.TeamNumber IsNot Nothing" otherwise you'll need to compare it to an integer value.

    You also may want to try the LINQ to SQL forums: http://social.msdn.microsoft.com/forums/en-US/linqtosql/threads

    HTH,

    -B

Page 1 of 2 (29 items) 12