我在EF Core中有2個相關實體(來自現有數據庫的數據庫優先設計)並且無法加載一對多關係 - 它是一個webapi ASP.NET核心1.0應用程序
品牌實體
[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; }
}
事件實體:
[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; }
}
來自DbContext中OnModelCreating中的FLUID API的代碼:
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; }
來自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);
}
來自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);
}
}
當我嘗試通過API加載品牌時,我得到一個例外:
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:錯誤:迭代時數據庫中發生異常查詢結果。 System.Data.SqlClient.SqlException(0x80131904):列名稱'EventId1'無效。
除此之外,有沒有辦法在不使用“包含”的情況下加載相關實體?如果我不使用include並使用標準LINQ查詢,則相關實體將加載為NULL
UPDATE
我現在收到一個無效的列錯誤 - 我在之前的代碼中註意到我沒有在ICollection上使用虛擬品牌
現在我無法弄清楚為什麼它在SQL中生成EventId1列
EF 7版本是1.0.0-rc1-final
UPDATE-2在使用代碼後,Exception在代碼中更改為循環依賴項異常,給出與上面完全相同的代碼 - 我不知道為什麼它之前生成了無效的列名(EventId1)
在這裡回答我自己的問題 - 想出來 -
這裡使用的2個實體,我在EF 7中使用了完全定義的關係 - 但是JSON序列化器並不喜歡這樣,因為這會創建一個循環依賴 - Brand Contains Events List,並且每個事件還包含父品牌屬性 -
所以這裡的解決方案是將[JsonIgnore]屬性添加到子項的關係屬性中
更新事件類:
[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; }
}