Use Interfaces Properly in Entity Framework Navigation Properties

Question

I am developing an application that can connect to several similar databases using the entity infrastructure and Devart. I did this by creating some interfaces that implement my EF models and it works fine, however I ran into a performance issue,

Take the following interfaces

public interface IEventBookEntry {

     int EntryId { get;set;}

     int EventBookId {get;set;}

     bool Flagged {get;set;

     IEntry Entry {get;set;}

}

and

public Interface IEntry {

    int EntryId {get;set;}

    DateTime EntryTimestamp {get;set;}

    ICollection<IEventBookEntry> EventBookEntries {get;set;}

}

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

This means that I can write queries in my BLL layer, which can work against any entity model, great stuff!

Take the following Linq method request:

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

The above code retrieves all entries in the event log where eventBookId == 123 and the timestamp of the entry for the last 3 days.

, GetList eventBookRepository

public IList<IEventBookEntry> GetList(Func<IEventBookEntry, bool> where, params Expression<Func<IEventBookEntry, object>>[] navigationProperties)
        {
            return new EventBookEntities().EventBookEntries.Where(where).ToList();
        }

, sql, , -

 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.EntryTimestamp > 22/01/2016

, , , , , , EventBookEntries, EventBookId = 123, , .

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


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

, ,

Code First Entity Framework , . , .

    [Table("ENTRIES")]
    public class Entry
    {
        public Entry()
        {
           EventbookEntries = new List<EventbookEntry>();
        }

        [Key, Column("ENTRY_ID", Order = 1)]
        public long EntryId { get; set; }

        [Column("ENTRY_TIMESTAMP")]
        public DateTime EntryTimestamp { get; set; }


        [ForeignKey("EntryId")]
        public virtual ICollection<EventbookEntry> EventbookEntries {get;set;}

    }

    [Table("EVENT_BOOK_ENTRIES")]
    public class EventbookEntry
    {

        [Key, Column("ENTRY_ID", Order = 1)]
        public long EntryId { get; set; }

        [Key, Column("EVENT_BOOK_ID", Order = 2)]
        public long EventbookId { get; set; }

        [ForeignKey("EntryId")]
        public virtual Entry Entry { get; set; }
    }

Db

, dbContext , , !

DbContext 2 DbSets

 /// <summary>
 /// Gets or sets the entries.
 /// </summary>
 /// <value>The entries.</value>
 public DbSet<Entry> Entries { get; set; }

/// <summary>
/// Gets or sets the eventbook entries.
/// </summary>
/// <value>The eventbook entries.</value>
public DbSet<EventbookEntry> EventbookEntries { get; set; }

linq

var start = DateTime.Now.AddDays(-20);
var eventBookId = 124;

var eventbookEntries = new EventBookContext().EventbookEntries.Where(eb=> eb.EventbookId == eventBookId && eb.Entry.EntryTimestamp > start).ToList();

: , , - , , ?

Answer 1

, Fluent API (http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx)

DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
        //one-to-many 
        modelBuilder.Entity<Entry>()
                    .HasRequired<EventbookEntry>(ev=> ev.EntryId ) 
                    .WithMany(ent => ent.EventbookEntries);     
}