Entity Framework - How to Insert a New Row Into a Table With/Without Loading Foreign Key Objects

Approach1: Retrieve foreign key objects from database

var newAudit = new Audit_UserReport();
newAudit.AccessedDate = DateTime.Now;
newAudit.Report = contextHelper.MIPortal.Report.First(report => report.ReportId == reportId);
newAudit.User = contextHelper.MIPortal.User.First(user => user.UserId == currentUserId);
contextHelper.MIPortal.AddToAudit_UserReport(newAudit);
contextHelper.MIPortal.SaveChanges();

Comments:

• This approach makes extra calls to the database

Approach2: Use stored procedure

Create an insert stored procedure and just call that.

Comments:

• In this way, you have to make changes to the stored procedure whenever you make any changes to the database table.

Approach3: Attach the foreign key objects

var role = new MIPortalModel.Role();
var defaultReport = new Report(){ReportId = 7, EntityKey = new EntityKey("MIPortalEntities.Report", "ReportId", 7)};
context.Attach(defaultReport);
role.DefaultReport = defaultReport;
context.AddToRole(role);
context.SaveChanges();

Comments

• The silly thing is that you must attach to the context first before setting the DefaultReport in this case, otherwise it will throw an exception at run time

Approach4: Use EntityReference

var role = new MIPortalModel.Role();
role.DefaultReportReference = new EntityReference()
{EntityKey = new EntityKey("MIPortalEntities.Report", "ReportId", 7)};
context.AddToRole(role);
context.SaveChanges();

Comments:

• This is the simplest and most efficient way maybe