C3rd

Writing Entity Framework (EF) Code-First
Posted: 4 Jan 2013, 15:05pm - Friday

Few days ago, I've been solving the issue of my project's EF Code First. Thanks to Decker Dong's tips in ASP.net and Ladislav Mrnka in stackoverflow.com. I solved the issue but not sure its the best answer/solution. I am not an expert in EF, Code-First nor ASP.NET. But so far, it works fine. Correction is welcome. :) Here are the common errors I always encounter; 

Introducing FOREIGN KEY constraint 'FK_dbo.Requestors_dbo.Projects_ProjectId' on table 'Requestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
and ... 
Unable to determine the principal end of an association between the types 'QICSkillsForce.Models.Requestor' and 'QICSkillsForce.Models.Project'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.
Honestly, I do not understand much on EF, Code-First, Poco and etc. But trying to learn them. My reference are;
  • http://stackoverflow.com/questions/5368029/need-some-help-trying-to-understand-entity-framework-4-1s-code-first-fluent-api
  • http://stackoverflow.com/questions/4442915/entity-framework-inserting-into-multiple-tables-using-foreign-key
My very question or problem is about relationship, defining them to EF. Defining the following relationships:
  • 1 Team has 0-many Users. (and a User is in 1 Team)
  • 1 User has 0-or-1 Foo's (but a Foo has no property going back to a User)
  • 1 User has 1 UserStuff
Answer in Entity Framework (sample): 
public class User
{
    public int Id { get; set; }
    ...
    public Foo Foo { get; set; }
    public Team Team { get; set; }
    public UserStuff UserStuff { get; set; }
}

public class Team
{
    public int Id { get; set; }
    ...
    public ICollection<User> Users { get; set; }
}

public class Foo
{
    public int Id { get; set; }
    ...
}

public class UserStuff
{
    public int Id { get; set; }
    ...
}

public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Foo> Foos { get; set; }
    public DbSet<Team> Teams { get; set; }
    public DbSet<UserStuff> UserStuff { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasRequired(u => u.Team)
            .WithMany(t => t.Users);

        modelBuilder.Entity<User>()
            .HasOptional(u => u.Foo)
            .WithRequired();

        modelBuilder.Entity<User>()
            .HasRequired(u => u.UserStuff)
            .WithRequiredPrincipal();
    }
}
And the result is; [caption id="attachment_849" align="aligncenter" width="654"]EDMX View EDMX View[/caption] Just as I wanted! Wew! In addition, I'm also bothered how to insert data to both tables that is related. So I learn that this is how I to do it; 
public void populate_projects()
{
            AppDbEntities db = new AppDbEntities();

            var proj = new Project { ProjectName = "Test Project", Description = "description here", DesiredCompletionDate = DateTime.Now, Notes = "notes here", Deleted = false, RemoteJob = true, RequestStatus = "Completed", SkillSet = "ASP.NET, C#", UserId = 1 };
            var req = new Requestor { Name = "User sample", Email = "user@sample.com", DeptId = 2, ApprovedBy = "Admin" };

            proj.Requestor = req;
            db.Projects.Add(proj);
}
I think that's it.. I have a lot of learning for 3 days straight solving the issues. There are more problems ahead and I eager to solve them. Yeah! (Somehow, there's some point thinking to give up and use ADO.NET and pure SQL commands! hahahahaa!) --- after couple of hours --- And this is how to query the rows... CONTROLLER: 
        public ActionResult Index()
        {
            // auto insert sample data to the db... [start]
            var count = db.Projects.ToList().Count().ToString();

            var proj = new Project { ProjectName = "Test Project " + count, Description = "description here", DesiredCompletionDate = DateTime.Now, Notes = "notes here", Deleted = false, RemoteJob = true, RequestStatus = "Completed", SkillSet = "ASP.NET, C#", UserId = 1 };
            var req = new Requestor { Name = "User " + count, Email = "user@sample.com", DeptId = 6, ApprovedBy = "Quack" };
            proj.Requestor = req;
            db.Projects.Add(proj);
            db.SaveChanges();
            // auto insert sample data to the db... [end]

            var projects = db.Projects.ToList();
            return View(projects);
        }
MODEL: 
            <table border="0" width="100%">
                <tr>
                    <th>Project Name</th>
                    <th>Description</th>
                    <th>Requestor</th>
                    <th>Status</th>
                </tr>
                @foreach (var proj in Model) {
                    <tr>
                        <td>@proj.ProjectName</td>
                        <td>@proj.Description</td>
                        <td>@proj.Requestor.Name</td>
                        <td>@proj.RequestStatus</td>
                    </tr>
                }
            </table>
As you noticed, I access the extended table from Project to Requestor to get its Requestor Name by calling @project.Requestor.Name and it will show the related row from Requestor table. I found that handy... W00t!

Back to list
Search
Pages
Categories