Entity Framework and forced Inner Join

by

EF seems to use INNER JOIN for including a required and LEFT OUTER JOIN for including an optional navigation property. Example:

public class Order
{
    public int Id { get; set; }
    public string Details { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

If I define Customer as a required property on Order…

public class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasRequired(o => o.Customer)
            .WithMany();
    }
}

…and issue this query…

using (var ctx = new MyContext())
{
    var result = ctx.Orders
        .Include(o => o.Customer)
        .Where(o => o.Details == "Peanuts")
        .FirstOrDefault();
}

…I get this SQL:

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Details] AS [Details], 
[Extent2].[Id] AS [Id1], 
[Extent2].[Name] AS [Name]
FROM  [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Customers] AS [Extent2] 
    ON [Extent1].[Customer_Id] = [Extent2].[Id]
WHERE N'Peanuts' = [Extent1].[Details]

If I change in the model configuration .HasRequired(o => o.Customer) to…

.HasOptional(o => o.Customer)

… I get exactly the same query except that INNER JOIN [dbo].[Customers] AS [Extent2] is replaced by:

LEFT OUTER JOIN [dbo].[Customers] AS [Extent2]

From model viewpoint it makes sense because you are saying that there can never be an Order without a Customer if you define the relationship as required. If you circumvent this requirement by removing the enforcement in the database and if you actually have then orders without a customer you violate your own model definition.

Only solution is likely to make the relationship optional if you have that situation. I don’t think it is possible to control the SQL that is created when you use Include.

More Related Contents:

  1. sort in Ascending Order [closed]
  2. How to get DateTime from the internet?
  3. Using a custom type discriminator to tell JSON.net which type of a class hierarchy to deserialize
  4. Random number in long range, is this the way?
  5. How does one configure HttpClient not to automatically redirect when it receives a 301 HTTP Status Code?
  6. Convert an int to bool with Json.Net [duplicate]
  7. Does not contain a constructor that takes 0 arguments
  8. How to get first record in each group using Linq
  9. Better way to query a page of data and get total count in entity framework 4.1?
  10. C# 4.0 optional out/ref arguments
  11. HttpClient vs HttpWebRequest
  12. Best ORM to use with C# 4.0 [closed]
  13. mapping multiple tables to a single entity class in entity framework
  14. Converting a LEFT OUTER JOIN to Entity Framework
  15. TPL TaskFactory.FromAsync vs Tasks with blocking methods
  16. How to create a method at runtime using Reflection.emit
  17. Drag-and-Drop multiple Attached File From Outlook to C# Window Form
  18. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  19. How to call a C# library from Native C++ (using C++\CLI and IJW)
  20. Will the dynamic keyword in C#4 support extension methods?
  21. Read PDF using itextsharp where PDF language is non-English
  22. Is Graphics.DrawImage too slow for bigger images?
  23. Entity Framework 4.1 default eager loading
  24. Getting all messages from InnerException(s)?
  25. Why does this (null || !TryParse) conditional result in “use of unassigned local variable”?
  26. Doesn’t Linq to SQL miss the point? Aren’t ORM-mappers (SubSonic, etc.) sub-optimal solutions?
  27. LINQ to Entities for subtracting 2 dates
  28. Fun (?) with Linq Expressions in extension methods
  29. EF5 How to get list of navigation properties for a domain object
  30. C#: Default implementation for == and != operators for objects

Leave a Comment