Entity Framework one to many with condition

Question

I have a Job class

[Table("Jobs")]
public class Job
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   [Column("fID")]
   public int ID { get; set; }

   public virtual ICollection<Note> Notes { get; set; }
}

The note class looks like this:

[Table("Note")]
public class Note
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  [Column("fID")]
  public int ID{get; set;}

  [Column("fld_int_NoteTypeID")]
  public int NoteTypeID { get; set; }
}

Whenever I request the Notes from a job like this:

 var job= context.Jobs.Include(x => x.Notes).FirstOrDefault(x => x.ID == jobId);

I would like the query to implicitly add Where NoteTypeId == 8.

Is it possible to somehow add this clause or do I have to explicitly add it each time?

Answer 1

What about workaround? Add NotMapped property which filters Notes collection:

[Table("Jobs")]
public class Job
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("fID")]
    public int ID { get; set; }

    public virtual ICollection<Note> Notes { get; set; }

    [NotMapped]
    public ICollection<Note> FilteredNotes
    {
        get
        {
            return Notes.Where(m => m.NoteTypeId == 8);
        }
    }
}

But the problem with this design is, when you select job like var job = context.Jobs.Include(x => x.Notes).FirstOrDefault(x => x.ID == jobId);, then you load all Notes to memory, then you can access filtered notes from memory like job.FilteredNotes. But when using LazyLoading it has advantage.

var job = context.Jobs.FirstOrDefault(x => x.ID == jobId);
var notes = job.FilteredNotes.ToList();

Update

You can also try Table-per-Hierarchy (TPH) mappimg. You have to create one abstract class and derived classes:

public abstract class Note
{
    public int Id { get; set; }
    public int NoteTypeId { get; set; }
}

public class JobNote : Note
{
}

public class OtherNote : Note
{
}

Then override OnModelCreating method:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Note>()
                .Map<JobNote>(m => m.Requires("NoteTypeId").HasValue(8))
                .Map<OtherNote>(m => m.Requires("NoteTypeId").HasValue(3)); // For example
}

Please note that, I do not have enough knowledge about TPH. I am just trying to show some hint. Please read further about TPH.