4

My data model is below. UserPhone is required but navigation property is optional as long as User's table could be empty:

public class Request
{
    [Key]
    public int Id {get;set;}
    [Required]
    public string UserPhone {get;set;}
    [ForeignKey("UserPhone")]
    public virtual User User {get;set;}
}

public class User
{
    [Key]
    public string UserPhone {get;set;}
}

The Users table is populated after a request is entered into the system. And somewhere between the request is entered and the user is not populated I'm trying to fetch all the requests with a user, if exists (outer join).

db.Requests.Include(r=>r.User).FirstOrDefault();

Which gives me zero results because EF did inner join (my UserPhone field is defined and a foreign key and it is required for the Requests table).

How can I properly define the mapping to be able to use include mapping? I need the include because my case touched several more tables I would like to do all with manual joins

Mando
  • 11,414
  • 17
  • 86
  • 167

1 Answers1

5

It does INNER JOIN because you have configured the FK property as [Required]. So from EF point of view the related User record should always exist.

If that's not the case, it means your model and database are out of sync. Remove the [Required] annotation from the Request.UserPhone property and you are done.

Update: EF relies heavily to the model metadata provided through conventions, data annotations and fluent configuration. All the decisions are made based on that information. For instance, when you configure a persistent primitive property as required, all the null checks against that property in the query will be removed and resolved at query translation time to either constant true or false. The same applies to relationships. Inside queries (and Include is a specific query construct) they are used by EF to determine the cardinality of the joins and the type of the joins. When querying the one side of the relationship, the Required / Optional property of the FK controls whether a INNER or LEFT OUTER join is generated.

So the only way to control EF behaviors is by supplying it with correct metadata from the entity model. There is no way to dynamically control / alter the behavior of a specific operations.

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • I mentioned that my data model is defined as per requirements. My `request` table requires the `userPhone` to be defined (required). Same time Users table doesn't require user to be present (outer join). SO the question is how to tell EF not to do inner join. – Mando May 19 '17 at 11:09
  • I already told you. It doesn't matter what is your business/domain model requirement. EF "entity" model represents the storage model and FK relationship, thus should reflect the database table `NULL` / `NOT NULL` and FK constraints. This is EF **requirement**, if you don't follow it (lie EF), don't expect EF to produce correct queries for you. – Ivan Stoev May 19 '17 at 11:12
  • `I already told you` - no you don't. Your answer confirms that I should update my datamodel when I stated that it's my requirement. The answer in comment make more sense when you confirmed that this is EF requirement. You should put it in the answer. – Mando May 19 '17 at 18:36
  • Ah, sorry (English is not my natural language) - I thought it's obvious :) Apologies and let me think a bit how to formulate it better. – Ivan Stoev May 19 '17 at 18:40