Sql – Linq2Sql Insert Records To Related Tables

asp.net-mvclinq-to-sql

Similar situation to : How to add several dependent records with LINQ2SQL
(this seems logical, but it doesn't work for me)

ASP.NET MVC + Linq2SQL

I have 2 tables called Challenges and Participants.

Challenge { challengeId,
ChallengeDesc, applicantId,
respondantId }
Participants { participantId, FirstName, LastName }

There are 1-many relationships between participants and Challenges – 1 for each key (applicantId, RespondantId).

I have an input form that collects all fields to create a new applicant and respondant and the challenge. The Databinder binds all the fields to Challenge and it's child participants correctly, however, the participant objects' names don't match the key names in the challenge object when browsing the structure created by Linq2Sql (applicantId matches with Participant object and respondantId matches with Participant1 object).

So when i try to SubmitChanges() after InsertOnSubmit(aChallenge) i get a foreign_key constraint validation message back from Linq2Sql. In SQL Server Profiler, I can see that the participants are being created properly, but when the challenge is being saved, the IDs of these newly inserted participants are not being set to the challenge object so the system is throwing a foreign key violation message.

How do i get past this?

Best Answer

You have to write it this way I think (assuming you have classes Participant and Challenge):

Participant applicant = new Participant();
Participant respondant = new Participant();
//update your participants here

Challenge insertedChallenge = new Challenge();
//update your challenge values here

applicant.Challenges.add(insertedChallenge);
respondant.Challenges1.add(insertedChallenge);

submitChanges();

Linq-to-SQL should automatically assign these properties (Challenges and Challenges) so it can set the key values for you.

Hope it helps.

Related Solutions

C# – When are records from related tables loaded with LINQ2SQL

I'm assuming that there is an 1-M FK relationship between reports and comments (1 Report can have many Comments)?

One option is to use the DataLoadOptions.LoadWith method - something like the following:

var reports = db.Reports();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Reports>(r => r.Comments);      // Ask for Comments along with reports
reports.LoadOptions = dlo;

Now, every time you select a report on that data context, the comments will be fetched from the db along with it.

Just beware that ALL fields from comments will be selected - there is no way using this method to select a subset of fields.

Another option is to be specific about what you want to select in the Linq query, e.g.

var myReportsList = from report in db.Reports
                    select new {  // Using anonymous type, but could use a custom class
                       Report = report,
                       Comment = report.Comment.Detail,   // for example
                       Subject = report.Comment.Subject
                    };

To understand when the query gets run and the database connection closed, you will need to understand:

The deferred execution model of Linq and Linq To Sql (Basically, for Linq to SQL, the query only runs when the results are asked for e.g. by iterating over the collection or binding to a grid)
The difference between IQueryable and IEnumerable

Jon Skeets "C# in depth" gives a great overview of these, and i've also heard very good things about "Linq in Action" - plus there are plenty of blog posts about these concepts which do the subjects more justice than I can do here ;o)

Sql – LINQ to SQL

If you aren't using the LinqToSql class designer, you should think about using it. The classes it generates will support the insertion scenario you outlined.

I can tell you aren't using the designer because it would give you a Notes (plural) property on the Person... as Person is 1 to Many with Notes.

How can I config LTS to load the notes on demand?

The designer will generate a property of type EntitySet(Note), which will load Notes on demand.

Related Topic