C# EF API Controller Not Loading Many to Many Array

Question

I have the following two classes in a C# ASP.Net application Entity Framework code-first setup

Facility Class;

public class Facility
{
    public int Id { get; set; }
    public string Name {get; set; }
    public ICollection<SubscriberList> SubscriberLists {get; set;}
}

SubscriberList Class;

Public class SubscriberList
{
    public SubscriberList()
    {
        Facilities = new HashSet<Facility>(); //not sure if I need this.
    }
    public int Id { get; set; }
    public int ClientId { get; set; }
    public ICollection<Facility> Facilities { get; set; }
}

With the following Configuration;

public class SubscriberListConfiguration : EntityTypeConfiguration<SubscriberList>
{
    public SubscriberListConfiguration()
    {
        HasMany(w => w.Facilities)
            .WithMany(s => s.SubscriberLists)
            .Map(m =>
            {
                m.ToTable("SubscriberListFacilities");
                m.MapLeftKey("SubscriberListId");
                m.MapRightKey("FacilityId");
            });
    }
}

Now I have the following ApiController

public List<SubscriberList> GetSubscriberLists()
{
    var list = _context.SubscriberLists
        .Include(c => c.Facilities)
        .ToList();
    return list;
 }

When calling the Get request to the /api/SubscriberLists I get the following Json which is missing the "facility name"

[
    {
        "Id": 2,
        "ClientId": 1000001,
        "Facilities": [
            {
                "$id": "1"
            }
        ]
    },
    {
        "Id": 3,
        "ClientId": 1000002,
        "Facilities": [
            {
                "$id": "2"
            },
            {
                "$id": "3"
            }
        ]
    }
]

As you can see it does not return the Facility.Name, only the Facility.Id. I tried by adding the virtual keyword before the ICollection. Tried changing ICollection<> to IList<> as well to List<> Also tried adding the attribute [JsonProperty("Facilities")] on top of the Facilities field. Also tried to iterate the returned list querying the facilities to trigger the loading. Nothing helped so far.

The interesting thing is that in debug mode, I can see everything loaded as expected, I see all the Facility.Name fields populated. I'm afraid the issue here lies at the Json Serializer, but have no clue how to troubleshoot this.

What's my next step?

Thanks in advance

Edit;

Worth to add. When navigating to the Api url in the browser which results in getting XML data instead of Json, I get the following result in the Facilities array.

<Facilities>
    <Facility xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="i1"/>
</Facilities>

Are you sure that the facility with id 4 has Name different then 'null' in your DB? — Vadim Martynov, Jan 03 '17 at 23:08
@VadimMartynov Yes, the Facility Id 4 (now 2) has a name, but this is even more strange, that the $id numbers it returns is not actually from the database, it just increments for each instance in the returned data. (I omitted a few of the responses for simplicity. I now updated it to 1,2 and 3) — Aron Einhorn, Jan 03 '17 at 23:25
This sounds like result of JSON serializer [`ReferenceLoopHandling`](http://www.newtonsoft.com/json/help/html/SerializationSettings.htm) set to `Ignore`. Do you have any issues deserializing that JSON? — Ivan Stoev, Jan 04 '17 at 00:27
@VadimMartynov actually come to realize that the "$id" does not represent the actuall Id field returned from the database. It's just a "Json.PreserveReferencesHandling" placeholder. Which begs the question, why is only this returned instead of the actual data. — Aron Einhorn, Jan 04 '17 at 06:07
@IvanStoev I've added the following line in the WebApiConfig.cs file but still have the issue. `config.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Serialize;` Is this what you've recommended, or else please explain. — Aron Einhorn, Jan 04 '17 at 06:08

score 1 · Answer 1 · edited Jan 04 '17 at 04:00

1

I think your mapping is a little off, maybe try this

modelBuilder.Entity<Facility>()
.HasMany<SubscriberList>(s => s.Facilities)
.WithMany(c => c.SubscriberLists )
.Map(cs =>
 {
   cs.MapLeftKey("FacilityId");
   cs.MapRightKey("SubscriberListId");
   cs.ToTable("SubscriberListFacilities");
});

Configure Many-to-Many relationship

edited Jan 04 '17 at 04:00

undefined

33,537
22
129
198

answered Jan 04 '17 at 00:08

Nonik

645
4
11

I'm not really sure what you've added in you answer. The only thing I see is that you've started the configuration with the Facility while I started the configuration with the SubscribeList. (In many to many relationship there is no difference on which class you do the configuration) I've updated the question to include the whole configuration class so you get the whole picture. Let me know if you still see that I missed anything from your answer. Thanks. – Aron Einhorn Jan 04 '17 at 05:23
More. I don't think the issue here is in my mapping. The database looks exactly as expected, and I've got the correct results with regular MVC controler, and I see the correct data in debug mode when inspecting locals. The only issue here is when it comes to get the data via the "API" controller. – Aron Einhorn Jan 04 '17 at 05:29
I see, there might be a problem with include in your case. http://stackoverflow.com/questions/28055327/entity-framework-include-is-not-working-within-complex-query. – Nonik Jan 04 '17 at 18:11
Also, to see actual queries generated by EF, you can turn on log https://blogs.msdn.microsoft.com/mpeder/2014/06/16/how-to-see-the-actual-sql-query-generated-by-entity-framework/ – Nonik Jan 04 '17 at 18:18
Would you elaborate on what's wrong with my Include statement, I see no issue with it. – Aron Einhorn Jan 04 '17 at 18:36
Include is not a guarantee. I may be wrong, but seems what you are doing is eager loading relationships, and EF is dropping your include. Reason why I am saying that you do eager loading, is because you didn’t mark your child collections as virtual. You can look at lazy loading vs eager loading, both have its own cons and pros and depending on your project’s needs. – Nonik Jan 04 '17 at 19:09
Several solutions that may help you Solution 1: var dbResult = _context.SubscriberLists; var list= dbResult.Include(c => c.Facilities).ToList(); return list – Nonik Jan 04 '17 at 19:10
Solution 2: var list= _context.SubscriberLists.Select(s => new { s, s. Facilities }).ToList(); Entity Framework has fetched SubscriberLists and Facilities entities from the database separately, but it glues them together by a process called relationship fixup. As you see, the Include is not necessary to make this happen. http://stackoverflow.com/questions/28055327/entity-framework-include-is-not-working-within-complex-query – Nonik Jan 04 '17 at 19:11
Solutions 3: you can enable lazy loading for entire DB in configuration, or you can turn on lazy loading for a particular property by making it virtual. – Nonik Jan 04 '17 at 19:11

C# EF API Controller Not Loading Many to Many Array

1 Answers1