1

I'm facing a peculiar issue, when the following code completes, and

using (var db = new DbContext())
        {
            var msReceived = new MilestonesReceived()
            {
                    CreateDtTm = DateTime.Now,
            };
            db.r.Add(msReceived);
            db.SaveChanges();

            foreach (var msEvent in message.MilestoneEvents)
            {
                var milestoneEvent = new MilestoneEvent
                {
                    MilestonesReceivedKey = msReceived.MilestonesReceivedKey,
                    CreateDtTm = DateTime.Now,
                };
                db.e.Add(milestoneEvent);
            }
            db.SaveChanges();
        }

we query the database with

SELECT *            
FROM e
     LEFT OUTER JOIN r
     ON(e.MilestonesReceivedKey = r.MilestonesReceivedKey)
WHERE r.SerialNum IS NULL

We are sporadically seeing via queries in SSMS that the SQL query will in fact return a result - the event record (child) appears but the received record (parent) does not. Referential integrity is not set up by design. What's confusing is that we explicitly call SaveChanges in order to get the identity key MilestonesReceivedKey in order to insert the child, so the parent must exist. Run the same query again, milliseconds later and it does (and the query returns nothing)

This doesn't make sense. We can verify from the CreateDtm for each records are instantiated in code in order, but not appearing in query results. We believe the parent must be created first, one to get the key, and two because we explicitly call SaveChanges before proceeding to the child records

0

I'd change your query to be the following

    -- e being the child, r being the parent
    SELECT * 
    FROM e WHERE MilestonesReceivedKey NOT IN (
        SELECT MilestonesReceivedKey FROM r
    )

I might be reading you wrong but you're just trying to capture the presence of a child record that doesn't have a parent. In your statement, you could potentially have a parent record (r) that has a null value in the SerialNum field - which would return a row. Unfortunately, the rest of the logic isn't present.

As such, I don't think it's a problem with EF.

  • Thanks for pointing that out. We've independently confirmed we have no records with null SerialNum. – Kevin Jan 12 '17 at 0:00
  • @Kevin, I'd maybe try to log exactly what EF is doing SQL-wise (partially because I'm more of a SQL guy). It might help in finding out what EF is doing. I'm sure you've already done that but it might help you get an answer on here. – unclefredo Jan 12 '17 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.