ASP Entity Framework - Code FIrst - Data Design - Keys

Question

I am attempting to structure my database appropriately for my web application. I am unsure the correct way to proceed with the keys and relationships. Basically..

My project contains a Product

Which can contain many Reports, however, the same Report can not relate to multiple Products.

I have Users, which may have many reports available to them.. regardless of the reports parent product.

My structure thus far

public class Product
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

---

public class Report
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Product Product { get; set; }
}

---

public class User
{
    [Key]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public virtual ICollection<Report> Reports { get; set; }
}

---

And to Seed

protected override void Seed(Insight.Data.InsightDb context)
    {
        context.Products.AddOrUpdate(p => p.ProductID,
            new Product { ProductName = "Product 1" },
            new Product { ProductName = "Product 2" }
        );

        context.Reports.AddOrUpdate(p => p.ReportID,
            new Report { ReportName = "Report A", ProductID = 1, },
            new Report { ReportName = "Report B", ProductID = 1, },
            new Report { ReportName = "Report C", ProductID = 1, },
            new Report { ReportName = "Report D", ProductID = 2, },
            new Report { ReportName = "Report E", ProductID = 2, }
        );

        context.Users.AddOrUpdate(u => u.UserID,
            new User { FirstName = "Frank", LastName = "Reynolds", },
            new User { FirstName = "Dee", LastName = "Reyonolds", }
        );
    }

I am unsure how to properly assign the Reports to the Users, or even know if I am on the right track. Also, is there a better practice than using int as a key?

Answer 1

While I haven't used the approach you have above.. here's my stab at it:

protected override void Seed(Insight.Data.InsightDb context)
{
    var product1 = new Product{ Name = "Product 1"};
    var product2 = new Product{ Name = "Product 2"};
    var reports1 = new List<Report>
    {
        new Report { Name = "Report A", Product = product1, },
        new Report { Name = "Report B", Product = product1, },
        new Report { Name = "Report C", Product = product1, },
        new Report { Name = "Report D", Product = product2, },
        new Report { Name = "Report E", Product = product2, }
    };

    context.Products.AddOrUpdate(p => p.ProductID,
        product1,
        product2
    );

    context.Reports.AddOrUpdate(p => p.ReportID,
        reports1
    );

    context.Users.AddOrUpdate(u => u.UserID,
        new User { FirstName = "Frank", LastName = "Reynolds", Reports = reports1 },
        new User { FirstName = "Dee", LastName = "Reyonolds" },
    );
}

I'm just not sure if setting up the reports part is correct (adding a list to the AddOrUpdate function), so that's something you may have to look in to if this doesn't work.

Answer 2

I have found a solution. It turns out I was interested in a many-to-many relationship, with a bridge table in between Reports and Users. These are my entities to establish this relationship.

public class Report
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public virtual Product Product { get; set; }

    public virtual ICollection<User> Users { get; set; }
}

public class User
{
    [Key]
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public virtual ICollection<Report> Reports { get; set; }
}

Then to populate my data with a code first approach

protected override void Seed(Insight.Data.InsightDb context)
{
    Product product1 = new Product { Name = "Product 1" };
    Product product2 = new Product { Name = "Product 2" };

    Report reportA = new Report { Name = "Report A", Product = product1 };
    Report reportB = new Report { Name = "Report B", Product = product1 };
    Report reportC = new Report { Name = "Report C", Product = product1 };
    Report reportD = new Report { Name = "Report D", Product = product2 };
    Report reportE = new Report { Name = "Report E", Product = product2 };

    List<User> users = new List<User>();

    users.Add(new User { FirstName = "Dee", LastName = "Reynolds", Reports = new List<Report>() { reportA, reportB, reportC } });
    users.Add(new User { FirstName = "Frank", LastName = "Reynolds", Reports = new List<Report>() { reportC, reportD, reportE } });

    users.ForEach(b => context.Users.Add(b)); 
}