Using Moq with Entity Framework 6 - Mocking Include and Where

Question

I'm trying to create some In-Memory dbContext mocks using Moq and using EntityFramework.Testing.Moq extension methods:

https://github.com/scott-xu/EntityFramework.Testing

I'm hitting a brick wall when I'm trying to unit test my eagerly loaded queries using mocked context. The issue is, nothing is selected at all. I know something should be selected, because when I point the same query using LINQPad to my source database, I get the 2 results back that I expect.

I never know how much code etc I need to post, so hopefully the below will help.

Data Diagram

I have two main tables, dbo.Applicant that holds basically user details and dbo.Application, which holds job applications, there are lookup tables that dbo.Application hooks into:

Example Models

This is a code first approach based off the above diagram. I have removed properties to keep the code as clean and to the point for the question:

[Table("Application")]
public partial class Application
{
    [Min(1)]
    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string ApplicationId { get; set; }

    [Min(1)]
    public int ApplicantId { get; set; }

    public virtual Applicant Applicant { get; set; }     
}

[Table("Applicant")]
public partial class Applicant
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Applicant()
    {
        Applications = new HashSet<Application>();
        RowStatus = "L";
    }

    public int Id { get; set; }

    [Required]
    public string FullName { get; set; }

    [Required]
    public string AddressLine1 { get; set; }

    [Required]
    public string Email { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Application> Applications { get; set; }
}

Context

Again a cut down context, but just in case I'm ask for it in the comments:

public partial class WorkExperienceContext : DbContext
{
    public WorkExperienceContext()
        : base("name=WorkExperienceContext")
    {
        this.Configuration.LazyLoadingEnabled = false;

    }

    public WorkExperienceContext(string userTestConnectionString)
        : base(userTestConnectionString)
    {
        Trace.Write("Using test context " + Add to dictionary);
    }

    public virtual DbSet<Applicant> Applicants { get; set; }
    public virtual DbSet<Application> Applications { get; set; }

}

Unit Test method

The following is (again) a cut down version of the Unit test method where I expect two Application records to be pulled through based off the email.

As you can see from the below, there are:

1. 2 Application records for Lucifer (ApplicantId = 666)
2. There is one Applicant record with an email of misunderstood@fireandbrimestone.co.uk

My issue is test is null, when querying the database itself with the same code, I get the correct results. I'm perplexed what I need to do. Remember I'm using the EntityFramework.Testing.Moq.SetupData() method which handles the mocking of the DbSet methods and transforming the List<> seed data to an IQueryable collection.

    public void InMemory_Find_Application_By_Email_EFMoq()
    {

        var applications = new List<Application>
        {
            new Application { Id = 1, ApplicantId = 666, ApplicationId = "1-a", DivisionId = 2, PublishingAreaId = 4, SourceId = 2, SkillsLearnt = "How to pick up  pen", RowStatus="L", ApplicantStartDateId = 1,
                ApplicantDisabilityId = 2, CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay } ,
            new Application { Id = 2, ApplicantId = 666, ApplicationId = "1-a", DivisionId = 3, PublishingAreaId = 4, SourceId = 2, SkillsLearnt = "How to pick up  pen", RowStatus="L", ApplicantStartDateId = 1,
                ApplicantDisabilityId = 2,CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay   } ,
            new Application { Id = 3, ApplicantId = 5, ApplicationId = "1-b", DivisionId = 3, PublishingAreaId = 1, SourceId = 2, SkillsLearnt = "Reading a book well" , RowStatus="L", ApplicantStartDateId = 1,
                ApplicantDisabilityId = 2,CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay     } ,
            new Application { Id = 4, ApplicantId = 5, ApplicationId = "1-b", DivisionId = 2, PublishingAreaId = 1, SourceId = 2, SkillsLearnt = "Reading a book well ", RowStatus="L", ApplicantStartDateId = 1,
                ApplicantDisabilityId = 2  ,CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay   } ,
            new Application { Id = 5, ApplicantId = 5, ApplicationId = "1-b", DivisionId = 7, PublishingAreaId = 1, SourceId = 2, SkillsLearnt = "Reading a book well" , RowStatus="L", ApplicantStartDateId = 1,
                ApplicantDisabilityId = 2 ,CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay    } ,
            new Application { Id = 6, ApplicantId = 24, ApplicationId = "1-c", DivisionId = 10, PublishingAreaId = 3, SourceId = 1, SkillsLearnt = "I can now re-iterate stuff", RowStatus="L", ApplicantStartDateId = 1,
                ApplicantDisabilityId = 2,CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay  },
            new Application { Id = 7, ApplicantId = 21, ApplicationId = "1-d", DivisionId = 2, PublishingAreaId = 2, SourceId = 1, SkillsLearnt = "I made some bread the other day", RowStatus="L", ApplicantStartDateId = 1,
                ApplicantDisabilityId = 2 ,CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay   }
        };

        var applicants = new List<Applicant>
        {
            new Applicant {Id = 5, FullName = "Pen Is", AddressLine1="Somewhere Over the Rainbow", County="West Sussex", Email="pen_is@hotmail.co.uk", RowStatus = "L",
            GenderId = 2, EducationId = 1, CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay, PostCode="PEN 1ST", Telephone="N/A"},
            new Applicant {Id = 24, FullName = "Gareth Bradley", AddressLine1="an address", County="West Sussex", Email="gareth.bradley@hachette.co.uk", RowStatus = "L",
            GenderId = 2, EducationId = 1, CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay, PostCode="bn13 3qb",Telephone="N/A"}  ,
            new Applicant {Id = 21, FullName = "Lizzy Windsor", AddressLine1="A Palace (Take your pick)", County="Berkshire", Email="HerRoyalMaj@TheCrownJewels.co.uk", RowStatus = "L",
            GenderId = 2, EducationId = 1, CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay, PostCode="BE1 1HM",Telephone="N/A"} ,
            new Applicant {Id = 666, FullName = "Lucifer MorningStar", AddressLine1="Hotsy Street", County="Down South", Email="misunderstood@fireandbrimes1one.co.uk" , RowStatus = "L",
            GenderId = 3, EducationId = 1, CreatedDate = DateTime.Now, CreatedTime = DateTime.Now.TimeOfDay, PostCode="HE11 6SS",Telephone="666-666"}
        };


        foreach (Applicant applicant in applicants)
        {
            applicant.Applications = applications.Where(a => a.ApplicantId == applicant.Id).ToArray();

        }

        foreach (Application application in applications)
        {
            application.Applicant = applicants.SingleOrDefault(a => a.Id == application.ApplicantId);
        }

        var mockSet = new Mock<DbSet<Application>>()
            .SetupData(applications);

        var mockSetUsers = new Mock<DbSet<Applicant>>()
            .SetupData(applicants);

        var mockContext = new Mock<WorkExperienceContext>();
        mockContext.Setup(c => c.Applications).Returns(mockSet.Object);
        mockContext.Setup(c => c.Applicants).Returns(mockSetUsers.Object);

        var mockService = new WorkExperienceFormService(mockContext.Object);

        var test = mockContext.Object
                 .Applications
                 .Include(a => a.Applicant)
                 .Where(e => e.Applicant.Email == "misunderstood@fireandbrimestone.co.uk")  
                 .ToList();

        Assert.AreEqual(2, test.Count());
    }

The following is proof of my LINQPad is pulling the data without a mocked context:

Answer 1

Maybe it's a typo when you posted the code, but the e-mail for 'Lucifer MorningStar' is misunderstood@fireandbrimes1one.co.uk instead of misunderstood@fireandbrimestone.co.uk