0

I realize this has been answered more than once, here for example, but nothing seems to be working for me and I'm not sure why.

My database has Sites and Users. A User might own a Site, or he might work at a Site. He might own Sites A and C, but work on Site B. However, each site has only one worker and one owner. Therefore, I have created a join table structure with the following three tables: User, Site, and User_Site, and User_Site contains a column called role that can be either "worker" or "owner".

To simplify things, I have created two views, view_Worker and view_Owner. view_Owner, for example, is 

SELECT User_Site.site_id, User.*
FROM User_Site
JOIN User ON User_Site.user_id = User.user_id
WHERE User_Site.role = "owner"

Therefore, each row in view_Owner contains all of the information on User and the site_id for which the User is joined as an "owner".

Now, I'm trying to build an API around this database using Entity Framework 6. I've gotten a number of errors trying a number of different things, so I'm not sure which errors to post here. I'll post my most recent error:

dbContext.cs

public DbSet<User> Users { get; set; }
public DbSet<Site> Sites { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Site>()
        .HasOptional<User>(s => s.Owner)
        .WithMany()
        .Map(m =>
        {
            m.MapKey("site_id").ToTable("view_Owner");
        });
}

user.cs

[Table("User")]
public class User
{
    [Key, Column("user_id")]
    public int ID { get; set; }
}

site.cs

[Table("Site")]
public class Site
{
    [Key, Column("site_id")]
    public int ID { get; set; }

    public virtual User Owner { get; set; }
}

The error message I get with this configuration is

The specified table 'view_Owner' was not found in the model. Ensure that the table name has been correctly specified.

So, the next step is to try and add this table to the model. I change [Table("User")] to [Table("view_Owner")] and I get the following error message:

(66,6) : error 3021: Problem in mapping fragments starting at line 66:Each of the following columns in table User is mapped to multiple conceptual side properties: User.site_id is mapped to (66,6) : error 3025: Problem in mapping fragments starting at line 66:Must specify mapping for all key properties (User.user_id) of table User.

Clarification

Ideally, I'd like a way to simply tell EntityFramework to load the Owner property on Site from the view_Owner table by joining view_Owner.site_id to the Site.site_id primary key. But, since the Owner property is still of type User, it doesn't need a new definition.

Community
  • 1
  • 1
Matthew James Davis
  • 12,134
  • 7
  • 61
  • 90
  • you might be able to define it in a much simpler way, by using migration to generate the sql code for you from your code (code first approach). I can help if that way works for you – LiranBo Oct 27 '15 at 12:23
  • the database already exists, i'm just building an api around it. i'm sure ef6 is powerful enough to wrap this structure. – Matthew James Davis Oct 27 '15 at 12:25
  • ok. so I think you are missing a reference from owner to user, you've created just the navigation property. add to site this: `public int OwnderID { get; set; }` and above it `[ForeignKey("Owner"]` you also need to add a collection nav property in `User` table since a user can be related to many different sites. The reason I suggest to let EF create the db for you is because your model needs to change quite a bit – LiranBo Oct 27 '15 at 12:30
  • okay, but its the other way around. `view_Owner` has a `site_id`. Does that make a difference? – Matthew James Davis Oct 27 '15 at 12:35
  • Ideally, I'd like a way to simply tell EF to load the Owner property from the view_Owner table by joining view_Owner site_id to the site_id primary key on site. But the Owner property is still of type User and doesn't need a new definition. – Matthew James Davis Oct 27 '15 at 12:37
  • I think you should still define your model correctly, but now that I think about it more, you are doing a simple DB first. [this](https://www.asp.net/mvc/overview/getting-started/database-first-development/setting-up-database) might help – LiranBo Oct 27 '15 at 12:52
  • http://stackoverflow.com/questions/21051612/entity-framework-join-3-tables – Erçin Dedeoğlu Feb 11 '17 at 05:59

0 Answers0