Unknown column in field list - After MySQL changes

c# entity-framework-6 linq mysql mysql-connector

Question

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

1
0
2/17/2016 2:05:28 PM

Popular Answer

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.

0
2/17/2016 2:16:48 PM


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