Interfaces and Navigation Properties

[WaywardHayward]

I am developing an application which can connect to multiple similar databases using entity framework and Devart. I have done this by creating some interfaces which my EF models implement and it works ok, however I have come across a performance issue,

Take the following Interfaces

Code: Select all

public interface IEventBookEntry {
      
     int EntryId { get;set;}

     int EventBookId {get;set;}
     
     bool Flagged {get;set;

     IEntry Entry {get;set;}

}

and

Code: Select all

public Interface IEntry {
    
    int EntryId {get;set;}

    DateTime EntryTimestamp {get;set;}

    ICollection<IEventBookEntry> EventBookEntries {get;set;}

}

My two entity models (which connect to different databases) implement the above interfaces.

This means that i can write queries in my BLL layer which can run against either entity model, great stuff!

Let's take the following Linq method query:

Code: Select all

   var eventBookEntries = new EventBookRepository().GetList(eb => eb.EventBookId == 123 && eb.Entry.EntryTimestamp > DateTime.Now.AddDays(-3));

The above code gets all the eventbook entries where eventBookId == 123 and the timestamp of the entry is within the past 3 days.

You would expect the sql generated behind the scenes for this to be something like this

Code: Select all

      SELECT Extent1.EntryId, Extent1.EventBookId, Extent1.Flagged 
      FROM EventBookEntries Extent1
      INNER JOIN Entries Extent2 ON Extent1.EntryId = Extent2.EntryId
      WHERE Extent1.EventBookId = 123
      AND Extent2.Timestamp > 22/01/2016

Unfortunately, what happens is that we do not get a single query with a join to entries, what happens instead is that we get one query which retrieves all EventBookEntries where EventBookId = 123 then a query per row returned which gets each entry.

Code: Select all

      SELECT Extent1.EntryId, Extent1.EventBookId, Extent1.Flagged 
      FROM EventBookEntries Extent1
      WHERE Extent1.EventBookId = 123

Code: Select all

    SELECT Extent1.EntryId, Extent1.EntryTimestamp
      FROM Entries Extent1
      WHERE Extent1.EntryId = :EntityKeyValue1

So it looks like there's a problem when generating queries using navigation properties based on interface types,

My Question is how do you implement interfaces on navigation properties in such a way as to ensure entity framework will use inner joins in queries rather than the behavior exhibited above?

Thanks

Wayward

[Shalex]

I am developing an application which can connect to multiple similar databases using entity framework and Devart.

In case of similar databases we recommend you to implement approach with a single (!) EF model. Switch between databases by changing connection string used by the context. You can use in this case:
a) XML mapping (if there are no differences at all between databases)
b) Code-First mapping (if there are some differences and you should take them into account)

My Question is how do you implement interfaces on navigation properties in such a way as to ensure entity framework will use inner joins in queries rather than the behavior exhibited above?

As we understood, this is not a provider specific problem (it persists with System.Data.SqlClient as well). The question exceeds the goals of our support. Please ask the EF team about the issue: http://entityframework.codeplex.com/discussions.