Insert data to multiple tables in single query

Question

currently I am learning ASP.NET and I stuck on storing data to multiple tables. Database model is shown bellow:

What I want to achieve is when I add new contact to Contact table to get last insert ID and automatically insert data for tables Phone/Tag/Email (if there is some data for that tables). Is there a chance to do it in single query or do I need to run fresh query for each table?

Here is model that is used in Controller:

 public partial class Contact
    {
        public Contact()
        {
            this.Emails1 = new HashSet<Email>();
            this.Phones1 = new HashSet<Phone>();
            this.Tags1 = new HashSet<Tag>();
        }

        public int id { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string address { get; set; }
        public string city { get; set; }
        public Nullable<byte> bookmarked { get; set; }
        public string notes { get; set; }

        public virtual ICollection<Email> Emails1 { get; set; }
        public virtual ICollection<Phone> Phones1 { get; set; }
        public virtual ICollection<Tag> Tags1 { get; set; }
    }
}

Here is model For Phone/Tags/Email tabels (it's same with different name in one column)

public partial class Email
    {
        public int id { get; set; }
        public int id_contact { get; set; }
        public string email1 { get; set; }

        public virtual Contact Contact1 { get; set; }
    }

Here is controller class that ads new row to database:

 public string AddContact(Contact contact)
        {
            if (contact != null)
            {

                db.Contacts.Add(contact);
                db.SaveChanges();
                    return "Contact Added";
            }
            else
            {
                return "Invalid Record";
            }
        }

Answer 1

Answer is using a stored procedure:

CRUD using Stored Procedure::

Entity Framework has ability to automatically build native commands for the database based on your LINQ to Entities or Entity SQL queries, as well as build the commands for inserting, updating, or deleting data. You may want to override these steps and use your own predefined stored procedures. You can use stored procedures either to get the data or to add/update/delete the records to one or multiple database tables.

Here Stored procedures for create, update, delete operation will be done using DbContext. That means context will execute stored procedure instead of DDL statements on context.SaveChanges().

Will use

1. sp_InsertStudentInfo stored procedure to insert a new student into the database
2. sp_UpdateStudent to update the student

3. sp_DeleteStudent to delete the student in the database.

   CREATE PROCEDURE [dbo].[sp_InsertStudentInfo]
       -- Add the parameters for the stored procedure here
       @StandardId int = null,
       @StudentName varchar
   AS
   BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
   
        INSERT INTO [SchoolDB].[dbo].[Student]([StudentName],[StandardId])
        VALUES(@StudentName, @StandardId)
   
       SELECT SCOPE_IDENTITY() AS StudentId
   
   END
   
    CREATE PROCEDURE [dbo].[sp_UpdateStudent]
       -- Add the parameters for the stored procedure here
       @StudentId int,
       @StandardId int = null,
       @StudentName varchar
   AS
   BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
   
       Update [SchoolDB].[dbo].[Student] 
       set StudentName = @StudentName,StandardId = @StandardId
       where StudentID = @StudentId;
   
   END
   
   CREATE PROCEDURE [dbo].[sp_DeleteStudent]
       -- Add the parameters for the stored procedure here
       @StudentId int
   AS
   BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
   
       DELETE FROM [dbo].[Student]
       where StudentID = @StudentId
   
   END
   

First of all, add these stored procedures into EDM and make sure Import selected stored procudures and function into the entity model checkbox is unchecked, as we will map these procedures with Student entity directly.

Now Model Browser will add procedures into Storage model but not in Function Imports

Now, in the EDM designer, right click on Student entity and select Stored Procedure Mapping to open Mapping details:

In the Mapping Details, you will see , , . Select the appropriate stored procedure for each one eg. Select sp_InsertStudentInfo for Insert function as shown below:

sp_InsertStudentInfo returns new auto generated StudentId. So map that with Student Entity’s StudentID as shown below:

Complete the mapping of Insert, Update and Delete procedures as shown below:

Now, we need to validate it before executing, in order to make sure that there will not be a run time error. To accomplish this, right click on Student entity in the designer and click Validate and make sure that there are no warnings or errors:

Now you can add, update and delete student as shown below:

using (var context = new SchoolDBEntities())
{
    Student newStudent = new Student() { StudentName = "New Student using SP"};

    context.Students.Add(newStudent);
    //will execute sp_InsertStudentInfo 
    context.SaveChanges();

    newStudent.StudentName = "Edited student using SP";
    //will execute sp_UpdateStudent
    context.SaveChanges();

    context.Students.Remove(newStudent);
    //will execute sp_DeleteStudentInfo 
    context.SaveChanges();
}

The code shown above will execute the following stored procedures on each SaveChanges():

exec [dbo].[sp_InsertStudentInfo] @StandardId=NULL,@StudentName='New Student using SP'
    go

    exec [dbo].[sp_UpdateStudent] @StudentId=47,@StandardId=NULL,@StudentName='Edited student using SP'
    go

    exec [dbo].[sp_DeleteStudent] @StudentId=47
    go

Note: Once context call SaveChagnes after adding a new student, it assign new StudentID to StudentID property of the Student entity because sp_InsertStudentInfo returns StudentId. This is necessary in order to use that entity object for a further operation.

See here more practically.

Answer 2

Your Contact object has collections of emails, phones and tags, so before you call db.SaveChanges(), you can add them to your Contact's collections.

public string AddContact(Contact contact)
{
    if (contact != null)
    {
        db.Contacts.Add(contact);

        contact.Emails1.Add(new Email { Email1 = "email@email.com"})
        contact.Emails1.Add(new Email { Email1 = "email2@email.com"})

        contact.Phones1.Add(new Phone1 { PhoneNumber = "1234516123"})

        db.SaveChanges();
        return "Contact Added";
    }
    else
    {
            return "Invalid Record";
    }
}

Just make sure your foreign key references are set up correctly and Contact object has an identity column if it will be a new contact.

Answer 3

You can save the entity graph as one can read in this article about Entity framework:

//Create student in disconnected mode
Student newStudent = new Student() { StudentName = "New Single Student" };

//Assign new standard to student entity
newStudent.Standard = new Standard() { StandardName = "New Standard" };

//add new course with new teacher into student.courses
newStudent.Courses.Add(new Course() { CourseName = "New Course for single student", Teacher = new Teacher() { TeacherName = "New Teacher" } });

using (var context = new SchoolDBEntities())
{
    context.Students.Add(newStudent);
    context.SaveChanges();

    Console.WriteLine("New Student Entity has been added with new StudentId= " + newStudent.StudentID.ToString());
    Console.WriteLine("New Standard Entity has been added with new StandardId= " + newStudent.StandardId.ToString());
    Console.WriteLine("New Course Entity has been added with new CourseId= " + newStudent.Courses.ElementAt(0).CourseId.ToString());
    Console.WriteLine("New Teacher Entity has been added with new TeacherId= " + newStudent.Courses.ElementAt(0).TeacherId.ToString());
}

This code uses a scope_identity() function to get the last added record Id.

So, as I understand, you've already write whole code for your classes. Also please note that you should dispose your DataContext.