How to create an optional foreign key to another table, using custom column names, with fluent api?

Question

I'm using Entity Framework 5, and I am writing code-first models that I wish to use on top of an existing database schema. So, I have no control over the schema design.

I have two tables, matters and users. The users table contains user accounts - actual users of the system. The matters table contains data similar to like an scheduled appointment, or something of that nature.

The matters table has a foreign key to users, in a column named manager. This column just says "hey, this users row specifies the manager of this matter." This is an optional key - a matter does not have to have a manager.

The model for the records in users does not need a property that maps back to these matters.

My current attempt at the mapping looks like this:

modelBuilder.Entity<Matter>().HasOptional(m => m.ProjectManager).WithRequired().Map(m => m.MapKey("project_manager"));

With that, I am trying to say that Matter has an optional FK, on the model's ProjectManager property, and that the user is required to exist, and that the column name, in the matters table is called project_manager.

This isn't working though, and the generated SQL is incorrect, but I can't figure out how to get what I'm looking for. If I were to write the SQL it'd look something like this

SELECT matters.*, users.*
FROM matters
LEFT JOIN users ON matters.project_manager = users.id

Answer 1

What I would do to get that sql is the following thing.

modelBuilder.Entity<Matter>()
.HasOptional(m => m.ProjectManager)
.WithMany(u => u.Matters)
.HasForeinKey(k=>k.project_manager);

This code should generate the sql statement that you are looking for. There is a nice tool that generates the code first tables and fluent api configuration, it is the EntityFramework Reverse POCO Generator, this was very helpfull for me.