CODEWITHSUNDEEP
linq-to-sql
user2241047
user2241047

Reputation: 45

linq to sql insert failed due to FK constraint, how can I submit all related objects at the same time?

I am using linq to sql and trying to insert new objects. Here's an example of my code:

public class Farm(){
    public List<FarmAnimals> FarmAnimals ();
    public string FarmName;
}

Public class FarmAnimal(){
    public string name;
}

public void Insert(FarmModel farm)
    {
        using (var context = new FarmDataClassesDataContext())
        {
            context.Farms.InsertOnSubmit(new Farm { FarmName = farm.FarmName });
            foreach (var animal in farm.FarmAnimals)
            {
                context.Responses.InsertOnSubmit(new FarmAnimal {name = animal.name, farmID = farm.Id });


            }
            context.SubmitChanges();
        }
    }

I get a FK constraint error when it tries to insert a farm animal, referencing the farmID (which equals 0). Since the farm hasn't been inserted yet, it doesn't have an ID for the farmanimals to refer to. How do I get the farm submitted so that the farm animals FK can be properly set?

Thanks,

Upvotes: 1

Views: 1082

Answers (3)

Pleun
Pleun

Reputation: 8920

The problem is you are thinking SQL way, and not ORM way.

The SQL way assigns a foreign key:

 InsertOnSubmit(new FarmAnimal {name = animal.name, farmID = farm.Id });

The ORM way assigns entities. Notice the part between ** ** in the following code sample.

 var myFarm = new Farm { FarmName = farm.FarmName };
 Con...InsertOnSubmit(myFarm)
 Con...InsertOnSubmit(new FarmAnimal {name = animal.name, **farm = myFarm**});

Because you assign the entity, proper insertions will be handled and as a bonus in one transaction.

Upvotes: 1

Matt Tsōnto
Matt Tsōnto

Reputation: 1701

To expand on Pleun's answer: You need to assign entities rather than IDs. The property that you're trying to assign to is mapped to a column with a foreign-key constraint, so it won't work for assigning an entity--to do that you instead need a property that maps to the relationship between two tables. How you do that varies by the tool you're using.

For the purposes of this explanation, I'll assume that you have a Farm table with a primary-key column called ID and another column called Name; and a FarmAnimal table with a foreign-key column named FarmFK that points to the Farm table and another column called Name.

Based on the DataContext part of the name I assume you're using the O/R Designer tool built in to Visual Studio, right? If so, go to the O/R Designer by opening your dbml file, select the association (represented as an arrow) between Farm and FarmAnimal (if there's not already an arrow, select the Association tool from the Toolbox and drag from Farm to FarmAnimal), and view the association's properties. You'll see properties called "Child Property" and "Parent Property". (The parent table is the table with the primary key in the relationship.) Expand those to see the "Name" sub-property of each. Those are the property names you'd use in code to access the two ends of the relationship. Typically they have poorly-chosen names based on the automatic generation, so rename them as needed. In this case let's rename the parent property's name to Animals and the child property's name to 'Farm'. You'd then be able to do the following in your code:

public void Insert(FarmModel farmModel)
{
    using (var context = new FarmDataClassesDataContext())
    {
        var farm = new Farm 
            {
                Name = farmModel.FarmName
            };
        context.Farms.InsertOnSubmit(farm);

        foreach (var animalModel in farmModel.FarmAnimals)
        {
            var critter = new FarmAnimal
                {
                    Name = animalModel.name,
                    Farm = farm
                }
            context.Responses.InsertOnSubmit(critter);
        }

        context.SubmitChanges();
    }
}

Does that answer your need?

Upvotes: 0

Andreas Johansson
Andreas Johansson

Reputation: 172

You have to submitChanges before inserting the FarmAnimals, and you need to have the column auto creating the key with autoincrement. Also make sure that the column in the table object in the DBML-file auto updated on insert.

public class Farm(){
    public List<FarmAnimals> FarmAnimals ();
    public string FarmName;
}

Public class FarmAnimal(){
    public string name;
}

public void Insert(FarmModel farm)
    {
        using (var context = new FarmDataClassesDataContext())
        {
            Farm newFarm = new Farm { FarmName = farm.FarmName }; <--- New

            context.Farms.InsertOnSubmit(newFarm); <---Edited

            context.SubmitChanges();  <--- New

            foreach (var animal in farm.FarmAnimals)
            {
                context.Responses.InsertOnSubmit(new FarmAnimal {name = animal.name, farmID = newFarm.Id }); <--- Edited


            }
            context.SubmitChanges();
        }
    }

Upvotes: 0

Related Questions