How to force outer join with .Include query Entity Framework 6

.net c# entity-framework entity-framework-6 outer-join

Question

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

1
3
5/19/2017 10:33:28 AM

Accepted Answer

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.

4
5/19/2017 6:54:09 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow