Why is this LINQ query not working correctly?

Question

I'm having trouble making this next query work correctly

var result = await db.Set<Employee>()               
   .Include(ca => ca.Person)          
   .Include(ca=> ca.Person).ThenInclude(x=> x.GenderType)
   .Include(ca=> ca.Position).ThenInclude(x=> x.Department)
   .AsNoTracking()
   .ToListAsync();

When this gets executed the person entity is null but just in new registries, with this I mean that there is already employees in the DB that were inputted directly with SQL and with this registries it works fine.

I thought the problem may be with how the registries are saved since two different entities are saved at the same time with PersonId being the key to correlate the employee with person, here is how it's done:

 var person = await db.Set<Person>().AddAsync(obj.Person);
              await db.SaveChangesAsync();

obj.PersonId = person.PersonId;
db.Entry(obj.Person).State = EntityState.Detached;

await db.Set<Employee>().AddAsync(obj);
await db.SaveChangesAsync();

I use the EntityState.Detached since Employee.Person is already saved. This works fine for saving but when I try to get all the entities from Employee the query will return null even when the Employee.PersonId is correct.

If I make a more "direct" query it works:

var query = from e in a
    join person in db.Set<Person>().AsNoTracking()
    on new { e.PersonId, e.SubscriptionId}
    equals new { person.PersonId, person.SubscriptionId}
    select person;

So I'm sure the registry is there, that's why I can't seem to find the problem.

PS: Sorry for the ambiguous question

Answer 1

I believe you may be over-complicating things with your detaching + add, and trying to manually ensure referenced entities are saved first. Under most normal scenarios, when allowed to track entities normally, EF can manage this perfectly find on it's own. I also highly recommend that you define your entities to use navigation properties or FK fields, not both. I.e. Navigation properties + shadow properties for the FK, or simply FK fields if you don't need any of the related entity properties or they are something like cached lookups. If you do use both, rely on the tracked navigation properties and do not set relationships by FK.

By using db.Entry(obj.Person).State = EntityState.Detached; you've basically told the DbContext to forget about that entity. I agree though that if you later tell the DbContext to load an Employee and .Include(x => x.Person) that it would be quite strange for that Person entity to be #null. But perhaps you can avoid this "bug"/behavior:

This code here is a smell:

var person = await db.Set<Person>().AddAsync(obj.Person);
          await db.SaveChangesAsync();

obj.PersonId = person.PersonId;

EF manages FK assignments 100% automatically. When I see code like this, it hints at an SQL/ADO developer not trusting EF to manage the associations.

Taking the following simplified example code:

var person = new Person { Name = "Steve" };
var employee = new Employee { Title = "Developer", Person = person };

In SQL land, Employee has a Person ID, so we'd typically need to ensure the Person record is saved first, get it's ID, and associate that to our Employee.PersonId column. Hence code like this:

context.Persons.Add(person);
context.SaveChanges(); // Generate PersonId.
employee.PersonId = person.PersonId;
context.Employees.Add(employee);
context.SaveChanges();

However, if the relationship is mapped in EF, this is completely unnecessary and can potentially lead to "already referenced" errors, which might be behind reasons to be messing with detaching entities. In reality, all you would need instead all of the above would be:

context.Employees.Add(employee);
context.SaveChanges();

When the employee is added, EF goes through all the related entities. It finds a Person that it doesn't know about so it will treat that as an added entity too. Because of the relationship mapping it will know that the Person needs to be inserted first, and the Employee PersonId will be updated as a result before the Employee is inserted.

Where people typically get tripped up when dealing with relationships is untracked instances. Let's say the "Person" record already exists. We're creating an employee we want to associate to Person ID#14. The most common examples I see is when the Person was loaded from the DbContext, sent to the client, then passed back to the server and developers assume that it's still an "entity" rather than a deserialized POCO that the DbContext has no knowledge of. For instance:

public void CreateEmployeeForPerson(Person person)
{
   var employee = new Employee( Title = "Developer", Person = person );
   Context.Employees.Add(employee);
   Context.SaveChanges();
}

This ends up raising a confusing error that a row already exists. This is due to the person reference being treated as a new entity, it isn't tracked. EF wanted to generate an INSERT statement for the Person as well as the Employee. By tinkering with attached state, and AsNoTracking() with entity references you might want to use for Updates and such you can run into issues like this as well. This can be solved by using Attach to associate it to the Context, though that can be risky as if anything sets a modified state on it and the data has been tampered with by the client that could persist unintentional changes. Instead, we should look to always deal with tracked instances:

public void CreateEmployeeForPerson(int personId)
{
   var person = Context.Persons.Single(x => x.PersonId == personId);
   var employee = new Employee( Title = "Developer", Person = person );
   Context.Employees.Add(employee);
   Context.SaveChanges();
}

The person reference is known/tracked by the DbContext, and we've asserted the PersonId actually exists in the DB via the Single call. Now when the Employee is added, it's .Person reference is pointing at a known instance to the DbContext so EF just generates the appropriate INSERT statement for the Employee only.

While this might not pinpoint why .Include was not including your newly created Person instances, hopefully this can help simplify your persistence code overall and avoid weird behaviour around detached entities.

Answer 2

UPDATE:

I realized why this was happening, the correlation one to one in the FK had an error, since I didn't make that part of the code I didn't realized it earlier.

this was the problem :

modelBuilder.Entity<Employee>(entity =>
{
                entity.HasKey(e => new { e.EmployeeId, e.SubscriptionId });

                entity.HasOne(d => d.Person)
                    .WithOne(p => p.Employee)
                    .HasForeignKey<Person>(d => new { d.PersonId, d.SubscriptionId })
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Employee_PersonId_SubscriptionId");

When it should have been like this

modelBuilder.Entity<Employee>(entity =>
{
                entity.HasKey(e => new { e.EmployeeId, e.SubscriptionId });

                entity.HasOne(d => d.Person)
                    .WithOne(p => p.Employee)
                    .HasForeignKey<Employee>(d => new { d.PersonId, d.SubscriptionId })
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Employee_PersonId_SubscriptionId");

As you can see.HasForeignKey<Employee>(d => new { d.PersonId,d.SubscriptionId }) was HasForeignKey<Person>... I hope this can help someone facing the same problem.