none
Insert with relational data

    Question

  • I'm trying to do an insert of a new employee object to the database, but I'm having a lot of trouble configuring either my dbml file or the relation within the sql server.  What I have is a simple relationship all with a 1-1 relationship. I have an employee table as the primary parent with a PK of employee_id. This id links to the employee_info table with a FK constraint on employee_id. The info table contains an address_id field which is the FK to the address table and the PK of address_id, again 1-1. I only want 1 employee, linked to 1 record of information, to 1 address for that employee.  The reason I have the address table separated is because I would like to reuse that table for other entities like Accounts, Locations, etc.

    Now for the real problem.  I haven't played much with Linq since the RTM release and quickly found out that some of what Scott had blogged about Linq to SQL had changed. Primarily the .Add() function to the database replaced with InsertOnSubmit().  However, what I'm trying to accomplish is the .Add() of the child to the parent.  In his blog the code looks something like:

    Order myOrder = new Order();
    myOrder.OrderDate = DateTime.Now;
    myOrder.RequiredDate = DateTime.Now.AddDays(1);
    myOrder.Freight = 34;

    OrderDetail myItem1 = new OrderDetail();
    myItem1.Product = chai;
    myItem1.Quantity = 23;

    myOrder.OrderDetails.Add(myItem1);   <-------- This line

    My code looks like:

    // create employee object
    employee emp = new employee();
    emp.active = true;
    emp.username = "test_" + DateTime.Now.Ticks.ToString();
    emp.password = "test123";

    employee_info details = new employee_info();
    details.date_hired = DateTime.Now;
    details.firstname = "fname";
    details.lastname = "lname";
    details.created_by = 1;
    details.created_date = DateTime.Now;
    emp.employee_infos.Add(details);  <----- Problem line

    The problem with the line is the fact that the .Add() function doesn't exist.  All I get from the intellisense are the attributes of the employee_info object... (firstname, lastname, etc). 

    I know I may be missing something, some relationship somewhere is incorrect... I have set the associations in the dbml to one-to-one.  Any help is greatly appreciated, and willing to provide and additional details to help solve this.  When I originally read Scott's blog on it, I thought for sure that this would be something simple to accomplish.
    Sunday, April 06, 2008 6:29 PM

Answers

  • Hi,

     

    applying the changes I got the code and it run fine

    Code Snippet

    var DC = new DBDataContext();

    var newParent = new parent();

    newParent.text = "XXXX1";

    //DC.parents.InsertOnSubmit(newParent); // ok

    var newChild = new child();

    newChild.text = "YYYY1";

    newParent.child1 = newChild;

    var newGrandChild = new grandchild();

    newGrandChild.text = "ZZZZ1";

    newChild.grandchild = newGrandChild;

    DC.parents.InsertOnSubmit(newParent); // ok

    DC.SubmitChanges();

    Console.ReadKey();

     

     

    If you look into the generated code file (something like ??.designer.cs for your ??.dbml). Whenever the property is of type EntityRef<xxx> you must do a direct assignment. For type EntitySet<xxx> you have to use property.Add(....) since this type implements IList<xxx>.

     

    If I change the relationship between parent and child from 1:n to 1:1 the Build forces me to change the foreign key pid in child to Nullable=False. The property childs (I renamed it to child1) changes from type EntitySet<...> to EntityRef<...>, therefore I get newParent.child1 = newChild; instead of calling an Add() method.

     

    I changed the relationship between child and grandchild from 1:n to n:1 (needs some changes in db too). Now I can have only one grandchild for my child and the property is of type EntityRef<....> too. This leads to

    newChild.grandchild = newGrandChild;

     

    So I end up with direct assigments instead of Add() calls.

     

    regards

    Philipp

    Tuesday, April 08, 2008 10:59 PM

All replies

  • Hi,

     

    OrderDetails is an 1:n relationship and the property OrderDetails (of type EntitySet<OrderDetails>) implements IList<OrderDetails>. If you want to add new OrderDetails you call the Add() method.

    I never used 1:1 relationsships but I guess they similiar to <parent> property of the child object.

    So the relation between you employee and employee_info is a 1:1 relation and the property employee_infos should be of type EntityRef<employee_info> and expects one object instead of a list member.

    Your line should look like

    emp.employee_infos = details;

     

    hope this works

    Philipp

    Monday, April 07, 2008 8:29 AM
  • thanks for the response,  I have just one question to that... will that insert a new id in the identity column of employee_info? I read somewhere that all tables are read only unless they have a PK, which is why i added one to that table.
    Monday, April 07, 2008 2:58 PM
  • If your identity column is defined as Auto Generated Value in both the database and in O/R Designer then you don't have to take care for that. During submit to the database the new generated id will be fatched.

    Linq To Sql requires only a unique primary key. This must not be an autogenerated column.

    If you have the sample database NorthWind. Then the customer table has an primary key CustomerID which is of type nchar(5) but must be unique since it is a primary key. The table orders has an autogenerated primary key OrderID. Both works with Linq To Sql.

     

    regards

    Philipp

    Monday, April 07, 2008 3:14 PM
  • my fields are set as both auto generated in the db and in the o/r designer. I attempted to add the code as you had suggested with
    emp.employee_infos = details;
    db.employee.InsertOnSubmit(emp);
    db.SubmitChanges();

    however, i'm getting a null value exception for the employee_id field in the employee_info table. The value is not being generated and stored properly.
    Tuesday, April 08, 2008 7:15 PM
  • I tried to reproduce it but it run without an exception and I checked the database (the inserts are applied).

    The code I used

    Code Snippet

    var DC = new DBDataContext();

    var newParent = new parent();

    newParent.text = "XXXX";

    //DC.parents.InsertOnSubmit(newParent); // ok

    var newChild = new child();

    newChild.text = "YYYY";

    newParent.childs.Add(newChild);

    var newGrandChild = new grandchild();

    newGrandChild.text = "ZZZZ";

    newChild.grandchilds.Add(newGrandChild);

    DC.parents.InsertOnSubmit(newParent); // ok

    DC.SubmitChanges();

     

     

    The O/R Designer is

    Code Snippet
    <?xml version="1.0" encoding="utf-8"?>
    <Database Name="Test" Class="DBDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
     '>http://schemas.microsoft.com/linqtosql/dbml/2007">http://schemas.microsoft.com/linqtosql/dbml/2007">
     ; <Connection Mode="AppSettings" ConnectionString="Data Source=PHILIPP-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True" SettingsObjectName="ConsoleApplication2.Properties.Settings" SettingsPropertyName="TestConnectionString" Provider="System.Data.SqlClient" />
      <Table Name="dbo.parent" Member="parents">
        <Type Name="parent">
          <Column Name="pid" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
          <Column Name="text" Type="System.String" DbType="NChar(10) NOT NULL" CanBeNull="false" />
          <Association Name="parent_child" Member="childs" OtherKey="parent" Type="child" />
        </Type>
      </Table>
      <Table Name="dbo.child" Member="childs">
        <Type Name="child">
          <Column Name="cid" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
          <Column Name="text" Type="System.String" DbType="NChar(10)" CanBeNull="true" />
          <Column Name="parent" Type="System.Int32" DbType="Int" CanBeNull="true" />
          <Association Name="child_grandchild" Member="grandchilds" OtherKey="cid" Type="grandchild" />
          <Association Name="parent_child" Member="parent1" ThisKey="parent" Type="parent" IsForeignKey="true" DeleteRule="CASCADE" />
        </Type>
      </Table>
      <Table Name="dbo.grandchild" Member="grandchilds">
        <Type Name="grandchild">
          <Column Name="gcid" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
          <Column Name="cid" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
          <Column Name="text" Type="System.String" DbType="NVarChar(50) NOT NULL" CanBeNull="false" />
          <Association Name="child_grandchild" Member="child" ThisKey="cid" Type="child" IsForeignKey="true" />
        </Type>
      </Table>
    </Database>

     

     

    The settings for the ??id column are

    Auto Generated Value = True

    Auto-Sync = OnInsert

    Primary Key = True

    ...

     

    regards

    Philipp

    Tuesday, April 08, 2008 7:53 PM
  • i'll have to give that a try with the parent -> child and see what's going on... but are you setting the relations to be 1-1? cause i see that you are able to use the .Add() method.

    newParent.childs.Add(newChild);

    for the grandchild the fk id is in the child table. so my db would look like this.

    parent:
    pid
    name

    child:
    cid
    pid
    name
    gcid

    grandchild:
    gcid
    name

    the reason for this is that i need the GC table to contain values from other objects. because my GC table is addresses, I want to be able to link the same table (via fk) to accounts, locations, and whatever other needs i would use an address for, not just for the employee.
    Tuesday, April 08, 2008 10:00 PM
  • Hi,

     

    applying the changes I got the code and it run fine

    Code Snippet

    var DC = new DBDataContext();

    var newParent = new parent();

    newParent.text = "XXXX1";

    //DC.parents.InsertOnSubmit(newParent); // ok

    var newChild = new child();

    newChild.text = "YYYY1";

    newParent.child1 = newChild;

    var newGrandChild = new grandchild();

    newGrandChild.text = "ZZZZ1";

    newChild.grandchild = newGrandChild;

    DC.parents.InsertOnSubmit(newParent); // ok

    DC.SubmitChanges();

    Console.ReadKey();

     

     

    If you look into the generated code file (something like ??.designer.cs for your ??.dbml). Whenever the property is of type EntityRef<xxx> you must do a direct assignment. For type EntitySet<xxx> you have to use property.Add(....) since this type implements IList<xxx>.

     

    If I change the relationship between parent and child from 1:n to 1:1 the Build forces me to change the foreign key pid in child to Nullable=False. The property childs (I renamed it to child1) changes from type EntitySet<...> to EntityRef<...>, therefore I get newParent.child1 = newChild; instead of calling an Add() method.

     

    I changed the relationship between child and grandchild from 1:n to n:1 (needs some changes in db too). Now I can have only one grandchild for my child and the property is of type EntityRef<....> too. This leads to

    newChild.grandchild = newGrandChild;

     

    So I end up with direct assigments instead of Add() calls.

     

    regards

    Philipp

    Tuesday, April 08, 2008 10:59 PM
  • I must be missing something, because I'm doing what Philipp shows and I get the 

    ForeignKeyReferenceAlreadyHasValueException on the SubmitChanges. My code snippet is:

    CTXContent ctx = new CTXContent(CNTConnection.ConnectionString);

    AAParent parent = new AAParent();

    parent.PName = "Daddy";

    AAChild child = new AAChild();

    child.CName = "Sonny";

    parent.AAChilds = child;

    ctx.AAParents.InsertOnSubmit(parent);

    ctx.SubmitChanges();

    Thursday, February 12, 2009 7:46 PM