EF CF Sub Navigation with intersect table not working

Question

I inherited a EF CF project and my knowledge of EF is inadequate. I am almost there. I have been reading and experimenting and I just can't figure out what I am doing wrong.

I have read numerous articles, tried reverse navigation, experimented and discovered strange new errors but no luck so far.

This is my setup. Of course the models have many more properties like Name that I left out for brevity.

public class VendorModel
{
    [key]
    public int Id { get; set; }

    [ForeignKey("VendorId")]
    public virtual List<VendorPersonnelModel> VendorPersonnel { get; set; }
}

//This model represents an intersect table in the DB
public class VendorPersonnelModel 
{

    [Key]
    public int Id { get; set; }

    public int VendorId { get; set; } //This is a FK to Vendor table defined in DB

    public int PersonnelId { get; set; } //This is a FK to Personnel table defined in DB

}

//This model definition is here but not used till the second half of the question
public class PersonnelModel
{
    [key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Phone { get; set; }
}

This is how I am navigating in the controller:

var models = await _db.Vendors
            .Include(o => o.Addresses)
            .Include(o => o.VendorPersonnel)
            .ToListAsync();

This is db context definition:

var ent = modelBuilder.Entity<VendorPersonnelModel>();
        ent.HasKey(obj => obj.Id);
        ent.ToTable("VendorPersonnel");

 //Navigation properties...do I put something here like this???
//ent.HasMany().HasForeignKey(y => y.PersonnelId);

This produces a json return like this:

[{
    "name": "Vendor ABC",
    "vendorPersonnel": [
        {
            "id": 1,
            "vendorId": 1001001,
            "personnelId": 1231
        },
        {
            "id": 2,
            "vendorId": 1001001,
            "personnelId": 1776
        }
    ]
}]

This is very close to what I want. Now I want to resolve the other half of the intersect table...the personnel. I do NOT need the intersect details I was just trying to step my way towards the solution.

I want the json to look something like this:

[{
    "name": "Vendor ABC",
    "personnelDetails": [
        {
            "id": 1,
            "Name": "Jane",
            "Phone": "333-123-4567"       
        },
        {
            "id": 2,
            "Name": "Joe",
            "Phone": "675-943-6732"
        }
    ]
}]

This is where I start getting all sorts of strange errors. I CAN make it work by doing two DIFFERENT queries and then mashing them together in a new object and return the new object but that just seems to me to be poor coding.

I'm sure there is a way to do this with EF I just don't know what to look for / read up on.

score 0 · Answer 1 · edited Dec 01 '19 at 14:45

I have now been able to read up and answer my question. This article helped me with what was missing:

https://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx

This mapping will create a self referencing "appearance" that will freak a json formatter out.

Simply add:

HttpConfiguration config = GlobalConfiguration.Configuration;

config.Formatters.JsonFormatter
            .SerializerSettings
            .ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;

Source: Entity framework self referencing loop detected

EF CF Sub Navigation with intersect table not working

1 Answers1