.Include Forces EF to throw sequence contains no elements

existing SQL Server Tables

Two Tests :

Call this one A:

    [Test]
    public void AssertAccessPolicyWithIdAndChecksum()
    {
        var pol = Repo.GetPolicyFlightStatus(aut_id: 44544, checksum: "QXSDENY");
        Assert.NotNull(pol);
    }

call this one B

    [Test]
    public void AssertGetFriendlyPolicy()
    {
        var lineRepo = new tbl_StatusRepository();
        var pol = Repo.GetPolicyFlightStatus(aut_id: 44544, checksum: "QXSDENY");
        Assert.AreEqual("With Underwriter", pol.tbl_Status.txt_friendlyName);
        Assert.AreEqual("WC/Longshore", pol.tbl_Line.txt_friendlyName);
    }

Models :

public partial class tbl_Policy
{
    [Key]
    public int aut_id { get; set; }
    [ForeignKey("tbl_Status")]
    public int int_statusID { get; set; }
    public virtual tbl_Status tbl_Status { get; set; }
    [ForeignKey("tbl_Line")]
    public int int_lineID { get; set; }
    public virtual tbl_Line tbl_Line { get; set; }
}

public class tbl_Status
{
    [Key]
    public int aut_id { get; set; }
    public string txt_status { get; set; }
    public string txt_friendlyName { get; set; }
    public virtual tbl_Policy tbl_Policy { get; set; }
}

public class tbl_Line
{
    [Key]
    public int aut_id { get; set; 开发者_如何学Go}
    public string txt_Line { get; set; }
    public string txt_friendlyName { get; set; }
    public virtual tbl_Policy tbl_Policy { get; set; }
}

When Running

      internal static tbl_Policy GetPolicyFlightStatus(int aut_id, string checksum)
    {
        if (Transcoder.Transcode(aut_id) == checksum)
        {
            var ctx = new LIGDataContext();
            return ctx.tbl_Policy.Include("tbl_Line").Include("tbl_Status").Single(f => f.aut_id == aut_id);
        }
        return null;
    }

TestA passes TestB throws Exception on first Assert Line

Adding Includes For SubTables

 internal static tbl_Policy GetPolicyFlightStatus(int aut_id, string checksum)
    {
        if (Transcoder.Transcode(aut_id) == checksum)
        {
            var ctx = new LIGDataContext();
            return ctx.tbl_Policy.Include("tbl_Line").Include("tbl_Status").Single(f => f.aut_id == aut_id);
        }
        return null;
    }

Test A and Test B Throw LIG2010RedesignMVC3.LIGMVC2010FlightTrackerTests.AssertAccessPolicyWithIdAndChecksum: System.InvalidOperationException : Sequence contains no elements

---

LIG2010RedesignMVC3.LIGMVC2010FlightTrackerTests.AssertGetFriendlyPolicy: System.InvalidOperationException : Sequence contains no elements

---

There are some problems in your object model. Basically, you are trying to set up a 1:1 association with foreign keys while code first does not really support this scenario. As a result it turns all of your associations to Shared Primary Key Associations and none of the foreign keys on tbl_Policy become a foreign key in the database. First, you need to fix your model since that could cause a bunch of exceptions at runtime.

Currently, there ar two ways to map a 1:1 association in Code-First:
1. Shared Primary Key Associations
2. One-to-One Foreign Key Associations

See which one better describes your domain model and I can change your object model to match that.

---

I also had to rework my modeling (credit to Morteza but I wanted it here for my records

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<tbl_Line>()
            .HasMany(d => d.tbl_Policy)
            .WithRequired(c => c.tbl_Line)
            .HasForeignKey(c => c.int_lineID);
        modelBuilder.Entity<tbl_Status>()
            .HasMany(d => d.tbl_Policy)
            .WithRequired(c => c.tbl_Status)
            .HasForeignKey(c => c.int_statusID);
    }