EF Core: loading related entities - circular dependency

asp.net-core-mvc entity-framework-core

Question

I have 2 related entities in EF Core (database first design from an existing database) and having trouble loading one-many relationship - it's a webapi ASP.NET core 1.0 application

Brand Entity

 [Table("tblBranding")]
public class Brand {
    [Key]
    [Column("brandingId")]
    public int BrandId { get; set; }
    [Column("BrandingActive")]
    public bool Active { get; set; }
    [JsonIgnore]
    [Column("DeadBrand")]        
    public bool DeadBrand { get; set; }
    [Column("BrandingSiteTitle")]
    public string Name { get; set; }

    //navigation properties
    public virtual ICollection<Event> Events { get; set; }
}

Event entity:

[Table("tblEvents")]
public class Event
{   
    public int EventId { get; set; }
    [Column("eventActive")]
    public bool Active { get; set; }
    [Column("eventName")]
    public string Name { get; set; }        
    public DateTime EventCloseDate {get;set;}        
    public int PaxAllocationLimit { get; set; }

    //navigation properties                
    [Column("BrandingId")]
    public int BrandId { get; set; }        
    public virtual Brand Brand { get; set; }
    public virtual ICollection<Session> Sessions { get; set; }

}

code from for FLUID API in OnModelCreating in DbContext:

modelBuilder.Entity<Event>()
            .HasOne(e => e.Brand)
            .WithMany(b => b.Events).HasForeignKey(e=>e.BrandId);

    public virtual DbSet<Brand> Brands { get; set; }
    public virtual DbSet<Event> Events { get; set; }  

code from BrandsController:

    [HttpGet]
    public IActionResult Get() {
        //var brands = from b in _context.Brands
        //             where b.Active == true
        //             orderby b.BrandName
        //             select b;

        var brands = _context.Brands.Include(e => e.Events).Where(b => b.Active == true).OrderBy(b => b.Name);

        return new ObjectResult(brands);
    }   

code from EventsController

 // GET: api/values
    [HttpGet("{id:int?}")]
    public IActionResult Get(int? id) {
        var events = from e in _context.Events
                     where e.Active == true
                     orderby e.Name
                     select e;

        if (!events.Any()) {
            return HttpNotFound();
        }

        if (id != null) {
            events = events.Where(e => e.EventId == id).OrderBy(e => 0);
            if (events.Count() == 0) { return HttpNotFound(); }
            return new ObjectResult(events);
        }
        else {
            return new ObjectResult(events);
        }
    }

When I try to load brands through the API, I get an exception:

Microsoft.Data.Entity.Storage.Internal.RelationalCommandBuilderFactory: Information: Executed DbCommand (80ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [t].[EventId], [t].[EventCloseDate], [t].[eventActive], [t].[BrandingId], [t].[EventId1], [t].[eventName], [t].[PaxAllocationLimit] FROM [tblEvents] AS [t] INNER JOIN ( SELECT DISTINCT [e].[BrandingSiteTitle], [e].[brandingId] FROM [tblBranding] AS [e] WHERE [e].[BrandingActive] = 1 ) AS [e] ON [t].[BrandingId] = [e].[brandingId] ORDER BY [e].[BrandingSiteTitle], [e].[brandingId] Microsoft.Data.Entity.Query.Internal.SqlServerQueryCompilationContextFactory: Error: An exception occurred in the database while iterating the results of a query. System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'EventId1'.

Apart from this, is there a way to load related entities without using the "Includes" ? If I do not use include and use the standard LINQ query, the related entities are loaded as NULL

UPDATE

I'm now getting an invalid column error - i noticed in my previous code I hadn't used virtual on the ICollection in brand

now i can't figure out why is it generating EventId1 column in the SQL

EF 7 version is 1.0.0-rc1-final

UPDATE-2 After playing around with the code the Exception changed to circular dependency exception in code given the exact same code as above - I don't know why it was generating the invalid column name earlier (EventId1)

1
2
9/23/2017 1:28:32 PM

Accepted Answer

Answering my own question here - figured it out-

the 2 entities used here, I've used fully defined relationships in the EF 7 - however the JSON serializer doesn not like that,as this createsa circular dependancy - Brand Contains Events List, and each event also contains the parent brand property -

so the solution here was to add [JsonIgnore] attribute to relationship properties on the child

updated Events class:

[Table("tblEvents")]
public class Event
{   
    public int EventId { get; set; }
    [Column("eventActive")]
    public bool Active { get; set; }
    [Column("eventName")]
    public string Name { get; set; }        
    public DateTime EventCloseDate {get;set;}        
    public int PaxAllocationLimit { get; set; }

    //navigation properties             
    [JsonIgnore]
    [Column("brandingId")]
    public virtual int BrandId { get; set; }
    [JsonIgnore]
    public virtual Brand Brand { get; set; }
    //public virtual ICollection<Session> Sessions { get; set; }

}
4
4/12/2016 6:22:21 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow