I have a query which pulls some data after making a couple of joins, this worked fine when the application used SQL Server. However after making the transfer to MySQL I'm having some issues.
For example I keep getting the error 'Unknown column Extent.Group_ClientID'. I have identified the line at which this error occurs at but I don't understand why.
Entity:
[Table("tblsupplier")]
public partial class Supplier
{
[Key][Column(Order = 0)]
public int ClientID { get; set; }
[Key][Column(Order = 1)]
public int SupplierID { get; set; }
[StringLength(50)]
public string AccountNo { get; set; }
[StringLength(100)]
public string SupplierName { get; set; }
public string DisplayName {
get {
return this.SupplierName + " (" + this.AccountNo + ")";
}
}
public virtual Client tblClient { get; set; }
}
Query:
public IQueryable<Supplier> GetAllSuppliersByClientWithClaims(int ClientID, List<int> WrittenOffIDs) {
return (from s in alliance.Suppliers
where s.ClientID == ClientID
join h in alliance.Headers
on new { a = s.ClientID, b = s.SupplierID }
equals new { a = h.ClientID, b = h.SupplierID }
join d in alliance.Details
on new { h.ClientID, h.ClaimID }
equals new { d.ClientID, d.ClaimID }
join r in alliance.Reviews
on new { h.ClientID, h.ReviewID }
equals new { r.ClientID, r.ReviewID }
where r.ReviewPeriodID != 0
where d.SplitLine == false
where !WrittenOffIDs.Contains((int)d.WrittenOffID)
select s).Distinct().OrderBy(r => r.SupplierName);
}
Method:
public string GetSupplierAutoComplete(int ClientID) {
DashboardViewModel model = new DashboardViewModel();
GeneralMethods GeneralHelpers = new GeneralMethods(reviewPeriodRepo, supplierGroupRepo, detailRepo);
model.Suppliers = supplierRepo.GetAllSuppliersByClientWithClaims(ClientID, GeneralHelpers.GetWrittenOffCodes(ClientID));
//Fails here
return JsonConvert.SerializeObject(model.Suppliers.Select(r => r.DisplayName), Formatting.Indented);
}
However, I have done some playing around and I've found that one of the where's in the query is causing this issue. where d.SplitLine == false
. Now in the database SplitLine is a Tinyint. As suggested because this is the boolean type for MySQL. Now if I pull a single 'SplitLine', it will return true or false based on the 0 or 1. Whereas if I use it in a where statement, it fails. Why it this?
UPDATE:
This only seems to happen when I enumerate the list
Found the answer! I'm not entirely sure why the error was occurring! Anyway..
I have two entities:
Header and Group.
I have a foreign key in my header entity which create a link with group. It said that one header can have one group. Which after I reviewed, was incorrect. One header can have many groups. So I changed the foreign key from this:
public virtual Group Group { get; set; }
To:
public ICollection<Group> Groups {get; set; }
This then worked. However, I haven't made a reference to group so I'm not sure to why this threw an error. If anyone knows, please let me know in the comments.