Hierarchical navigational properties using EF Codefirst

Question

'm getting an invalid column error when calling the member.TotalBalance

Invalid column name 'Loan_ID'.

Invalid column name 'Loan_ID1'.

Heres my snippets

public class Member
    {
        public string ID { get; set; }

        private ICollection<Loan> _Loans;
        public virtual ICollection<Loan> Loans
        {
            get { return _Loans ?? (_Loans = new HashSet<Loan>()); }
            set { _Loans = value; }
        }

        public virtual double TotalBalance
        {
            get
            {
                return Loans.Select(p => p.TotalBalance).Sum();
            }
        }
    }

    public class Loan
    {
        public string ID { get; set; }

        public string MemberId { get; set; }

        private ICollection<LoanLedger> _LoanLedgers;
        public virtual ICollection<LoanLedger> LoanLedgers
        {
            get { return _LoanLedgers ?? (_LoanLedgers = new HashSet<LoanLedger>()); }
            set { _LoanLedgers = value; }
        }

        public virtual double TotalBalance
        {
            get
            {
                return LoanLedgers.Select(p => p.Amt).Sum();
            }
        }

    }

    public class LoanLedger
    {
        public string ID { get; set; }
        public double Amt { get; set; }

        public string LoanId { get; set; }
    }

This is how it was configured

//Member
HasKey(b => b.ID).Property(b => b.ID);

//Loan
HasKey(p => p.ID).HasRequired(a => a.Member).WithMany(i => i.Loans);

//LoanLedger
HasKey(p => p.ID).HasRequired(a => a.Loan).WithMany(p => p.LoanLedgers);

I really appreciate your help

Thanks

Answer 1

Anyone could help me here? 

Answer 2

Hi roggss,

Welcome to MSDN Forum.

Could you please clarify where the exception message is thrown?

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 3

When calling the 

Member member = new Member();

var bal = member.member.TotalBalance;

It will throw an exception on the loan class

 public class Loan
    {
        public string ID { get; set; }

        public string MemberId { get; set; }

        private ICollection<LoanLedger> _LoanLedgers;
        public virtual ICollection<LoanLedger> LoanLedgers
        {
            get { return _LoanLedgers ?? (_LoanLedgers = new HashSet<LoanLedger>()); }
            set { _LoanLedgers = value; }
        }

        public virtual double TotalBalance
        {
            get
            {
                return LoanLedgers.Select(p => p.Amt).Sum();                     <<<< this line causes the error.
            }
        }

    }

i really appreciate your help

Answer 4

Hi roggss,

Based on the exeption, there must be a "LoanID" and a "LoanID1" in your code, could you please clarify what they are? If possible, please upload the project to SkyDrive and post the link here, I will download it and help you to find the root cause.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 5

Hi Allen,

The classes should have a relationship like this.

Member has many loans,

Loan has many loanledgers.

Please see navigation properties of Member and Loan,.

/////// Entities//////

        

public class Member
    {
        #region Logical
        [DisplayName("ID")]
        public string ID { get; set; }

        [DisplayName("Member No.")]
        public int MemberNo { get; set; }

        [Required]
        [DisplayName("Username")]
        public string UserName { get; set; }

        [Required]
        [DisplayName("Email")]
        public string EMail { get; set; }

        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
        [DisplayName("Date Applied")]
        public System.DateTime DateApplied { get; set; }

        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
        [DisplayName("Date Membered")]
        public System.DateTime? DateMembered { get; set; }

        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
        [DisplayName("Date Revoked")]
        public System.DateTime? DateRevoked { get; set; }

        [DisplayName("Member Type")]
        public int MemberTypeFlag { get; set; }

        [DisplayName("Member Status")]
        public int StatusFlag { get; set; }

        [DisplayName("Route Level")]
        public int RouteLevel { get; set; }


        #endregion Logical

        #region Navigation
        private ICollection<MemberAttribute> _MemberAttributes;
        public virtual ICollection<MemberAttribute> MemberAttributes
        {
            get { return _MemberAttributes ?? (_MemberAttributes = new HashSet<MemberAttribute>()); }
            set { _MemberAttributes = value; }
        }

        private ICollection<Address> _Addresses;
        public virtual ICollection<Address> Addresses
        {
            get { return _Addresses ?? (_Addresses = new HashSet<Address>()); }
            set { _Addresses = value; }
        }

        private ICollection<Share> _Shares;
        public virtual ICollection<Share> Shares
        {
            get { return _Shares ?? (_Shares = new HashSet<Share>()); }
            set { _Shares = value; }
        }

        private ICollection<ShareRelease> _ShareReleases;
        public virtual ICollection<ShareRelease> ShareReleases
        {
            get { return _ShareReleases ?? (_ShareReleases = new HashSet<ShareRelease>()); }
            set { _ShareReleases = value; }
        }

        private ICollection<Loan> _Loans;
        public virtual ICollection<Loan> Loans
        {
            get { return _Loans ?? (_Loans = new HashSet<Loan>()); }
            set { _Loans = value; }
        }

        private ICollection<MemberContact> _MemberContacts;
        public virtual ICollection<MemberContact> MemberContacts
        {
            get { return _MemberContacts ?? (_MemberContacts = new HashSet<MemberContact>()); }
            set { _MemberContacts = value; }
        }

        private ICollection<MemberFee> _MemberFees;
        public virtual ICollection<MemberFee> MemberFees
        {
            get { return _MemberFees ?? (_MemberFees = new HashSet<MemberFee>()); }
            set { _MemberFees = value; }
        }

        private ICollection<MemberRequirement> _MemberRequirements;
        public virtual ICollection<MemberRequirement> MemberRequirements
        {
            get { return _MemberRequirements ?? (_MemberRequirements = new HashSet<MemberRequirement>()); }
            set { _MemberRequirements = value; }
        }

        private ICollection<Payment> _Payments;
        public virtual ICollection<Payment> Payments
        {
            get { return _Payments ?? (_Payments = new HashSet<Payment>()); }
            set { _Payments = value; }
        }

        private ICollection<MemberStatus> _Statuses;
        public virtual ICollection<MemberStatus> Statuses
        {
            get { return _Statuses ?? (_Statuses = new HashSet<MemberStatus>()); }
            set { _Statuses = value; }
        }

        #endregion Navigation

        #region Derived
        [DisplayName("Membership Fee Balance")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public double MemberFeeBalance
        {
            get
            {
                return MemberFees.Select(p => p.Amount).Sum() - Payments.Where(p => p.PayType == EnumPayType.MembershipFee).Select(p => p.Amount).Sum();
            }
        }

        [DisplayName("Member Name")]
        public virtual string MemberName { get; set; } 

        [DisplayName("Member Type")]
        public virtual string MemberTypeFlagDescription
        {
            get
            {
                if (MemberTypeFlag == 2)
                {
                    return "Business";
                }
                else
                {
                    return "Person";
                }
            }
            set
            {
                MemberTypeFlagDescription = value;
            }
        }

        public EnumMemberType MemberType
        {
            get { return (EnumMemberType)MemberTypeFlag; }
            set { MemberTypeFlag = (int)value; }
        }

        public EnumMemberStatus Status
        {
            get { return (EnumMemberStatus)StatusFlag; }
            set { StatusFlag = (int)value; }
        }

        [DisplayName("Total Share")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double TotalShareAmount
        {
            get
            {
                return Shares.Select(p=>p.Amount).Sum();
            }
        }

        [DisplayName("Total Loan")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double TotalLoan
        {
            get
            {
                return Loans.Where(p => p.LoanStatus == EnumLoanStatus.Approved && p.LoanReleases.FirstOrDefault(a=>a.DateReleased != null) != null ).Select(p => p.Amount).Sum();
            }

        }

        [DisplayName("Total Balance")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double TotalBalance
        {
            get
            {
                return Loans.Select(p => p.TotalBalance).Sum();
            }
        }

        [DisplayName("Total Balance")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double NetBalance
        {
            get
            {
                return TotalShareAmount - TotalBalance;
            }
        }

        #endregion Derived
    }

                                      

 public class Loan
    {
        #region Logical
        public string ID { get; set; }

        [DisplayName("Loan No.")]
        public int LoanNo { get; set; }

        [Required]
        [DisplayName("Loan Amount")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        [Range(1, float.MaxValue, ErrorMessage = "Amount cannot be set to zero (0).")]
        public double Amount { get; set; }

        [Required]
        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
        [DisplayName("Date Applied")]
        public System.DateTime DateApplied { get; set; }

        [Required]
        [DisplayName("Months")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        [Range(1, int.MaxValue, ErrorMessage = "Month cannot be set to zero (0).")]
        public int Months { get; set; }

        [Required]
        [DisplayName("Schedule Type")]
        public int ScheduleTypeFlag { get; set; }

        [Required]
        [DisplayName("Loan Type Configuration")]
        public string LoanTypeConfigurationId { get; set; }

        public string MemberId { get; set; }

        [DisplayFormat(DataFormatString = "{0:N}")]
        [DisplayName("Schedule Amount")]
        [DefaultValue(0)]
        public double AmountSchedule { get; set; }

        public int LoanStatusFlag { get; set; }

        [DisplayName("Status Level")]
        public int StatusLevel { get; set; }

        [DisplayName("Date Bill Schedule")]
        public DateTime DateBillSchedule { get; set; }

        #endregion Logical

        #region Navigation
        public virtual LoanTypeConfiguration LoanTypeConfiguration { get; set; }

        public virtual Member Member { get; set; }

        private ICollection<LoanFee> _LoanFees;
        public virtual ICollection<LoanFee> LoanFees
        {
            get { return _LoanFees ?? (_LoanFees = new HashSet<LoanFee>()); }
            set { _LoanFees = value; }
        }

        private ICollection<LoanLedger> _LoanLedger;
        public virtual ICollection<LoanLedger> LoanLedgers
        {
            get { return _LoanLedger ?? (_LoanLedger = new HashSet<LoanLedger>()); }
            set { _LoanLedger = value; }
        }

        private ICollection<LoanRelease> _LoanReleases;
        public virtual ICollection<LoanRelease> LoanReleases
        {
            get { return _LoanReleases ?? (_LoanReleases = new HashSet<LoanRelease>()); }
            set { _LoanReleases = value; }
        }

        private ICollection<LoanRequirement> _LoanRequirements;
        public virtual ICollection<LoanRequirement> LoanRequirements
        {
            get { return _LoanRequirements ?? (_LoanRequirements = new HashSet<LoanRequirement>()); }
            set { _LoanRequirements = value; }
        }

        private ICollection<LoanSchedule> _LoanSchedules;
        public virtual ICollection<LoanSchedule> LoanSchedules
        {
            get { return _LoanSchedules ?? (_LoanSchedules = new HashSet<LoanSchedule>()); }
            set
            {
                _LoanSchedules = value;
            }
        }

        private ICollection<LoanStatus> _Statuses;
        public virtual ICollection<LoanStatus> Statuses
        {
            get { return _Statuses ?? (_Statuses = new HashSet<LoanStatus>()); }
            set { _Statuses = value; }
        }

        private ICollection<Payment> _Payments;
        public virtual ICollection<Payment> Payments
        {
            get { return _Payments ?? (_Payments = new HashSet<Payment>()); }
            set { _Payments = value; }
        }

        private ICollection<Bill> _Bills;
        public virtual ICollection<Bill> Bills
        {
            get { return _Bills ?? (_Bills = new HashSet<Bill>()); }
            set { _Bills = value; }
        }

        private ICollection<Adjustment> _Adjustments;
        public virtual ICollection<Adjustment> Adjustments
        {
            get { return _Adjustments ?? (_Adjustments = new HashSet<Adjustment>()); }
            set { _Adjustments = value; }
        }
        #endregion Navigation

        #region Derived
        public EnumScheduleType ScheduleType
        {
            get { return (EnumScheduleType)ScheduleTypeFlag; }
            set { ScheduleTypeFlag = (int)value; }
        }

        public virtual string ScheduleTypeFlagDescription
        {
            get
            {
                if (ScheduleTypeFlag == 1)
                {
                    return "SemiMonthly";
                }
                else
                {
                    return "Monthly";
                }
            }
            set
            {
                ScheduleTypeFlagDescription = value;
            }
        }

        public EnumLoanStatus LoanStatus
        {
            get { return (EnumLoanStatus)LoanStatusFlag; }
            set { LoanStatusFlag = (int)value; }
        }

        public virtual string LoanStatusDescription
        {
            get
            {
                if (LoanStatus == EnumLoanStatus.Approved)
                {
                    return "Approved";
                }
                else if (LoanStatus == EnumLoanStatus.Canceled)
                {
                    return "Canceled";
                }
                else if (LoanStatus == EnumLoanStatus.Closed)
                {
                    return "Closed";
                }
                else if (LoanStatus == EnumLoanStatus.Disapproved)
                {
                    return "Disapproved";
                }
                else if (LoanStatus == EnumLoanStatus.Opened)
                {
                    return "Opened";
                }
                else if (LoanStatus == EnumLoanStatus.Reloaned)
                {
                    return "Reloaned";
                }
                else 
                {
                    return "Unknown";
                }
            }
            set
            {
                LoanStatusDescription = value;
            }
        }

        [DisplayName("Loan Description")]
        public virtual string LoanTypeName
        {
            get
            {
                return LoanTypeConfiguration.LoanTypeLib.Description + "-" + LoanTypeConfiguration.Description + "( Loan No. " + LoanNo + " )";
            }
            set
            {
                LoanTypeName = value;
            }
        }

        public virtual ICollection<LoanLedger> LoanLedgersNoInterest
        {
            get
            {
                int interest = Convert.ToInt32(EnumLedgerType.Interest);
                return LoanLedgers.Where(p => p.LedgerTypeFlag != interest).ToList();
            }
        }

        public virtual ICollection<LoanLedger> LoanLedgersInterest
        {
            get
            {
                int interest = Convert.ToInt32(EnumLedgerType.Interest);
                return LoanLedgers.Where(p => p.LedgerTypeFlag == interest).ToList();
            }
        }

        [DisplayName("Total Interest")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double TotalInterestAmount {
            get
            {
                return LoanLedgersInterest.Select(p => p.Amount).Sum();

            }
         }

        [DisplayName("Total Fee")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double TotalFeeAmount
        {
            get
            {
                double totalFee = 0;
                try
                {
                    foreach (var i in LoanFees)
                    {
                        totalFee = totalFee + i.Amount;
                    }
                }
                catch { }
                return totalFee;
            }
        }

        public virtual double InterestRate
        {
            get
            {
                try
                {
                    return LoanTypeConfiguration.InterestRate / ScheduleMultiplier;
                }
                catch 
                { 
                    return 0; 
                }
            }
        }

        [DisplayName("Total Balance")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double TotalBalance
        {
            get
            {
                int interest = Convert.ToInt32(EnumLedgerType.Interest);

                return LoanLedgers.Where(p => p.LedgerTypeFlag != interest).Select(a => a.Amount).Sum();

            }
        }

        [DisplayName("Total Current Balance")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public virtual double TotalCurrentBalance
        {
            get
            {
                try
                {
                    Bill bill = Bills.First(p => p.Status == EnumBillStatus.Active);
                    return bill.BillLines.Sum(p => p.Amount) - LoanLedgersNoInterest.Where(p => p.DatePosted > bill.DateBilled).Select(p => p.Amount).Sum();
                }
                catch
                {
                    return 0;
                }

            }
        }

        public virtual int ScheduleMultiplier
        {
            get
            {
                switch (ScheduleType)
                {
                    case EnumScheduleType.Monthly:
                        return 1;
                    case EnumScheduleType.SemiMonthly:
                        return 2;
                    //case EnumScheduleType.Quarterly:
                    //    return 4;
                    default :
                        return 0;
                }
            }

        }

        public virtual int NoSchedules
        {
            get
            {
                return Months * ScheduleMultiplier;
            }
        }
        #endregion Derived
    }

    public class LoanLedger
    {
        #region Logical
        [DisplayName("ID")]
        public string ID { get; set; }

        [DisplayName("Loan ID")]
        public string LoanId { get; set; }

        [DisplayName("Date Posted")]
        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
        public DateTime DatePosted { get; set; }

        [DisplayName("Description")]
        public string Description { get; set; }

        [DisplayName("Amount")]
        [DisplayFormat(DataFormatString = "{0:N}")]
        public double Amount { get; set; }

        [DisplayName("Pay ID")]
        public String PayId { get; set; }

        [DisplayName("Pay Type")]
        public int LedgerTypeFlag { get; set; }

        #endregion Logical

        #region Navigation
        public virtual Loan Loan{ get; set; }
        #endregion Navigation

        #region Derived

        public EnumLedgerType LedgerType
        {
            get { return (EnumLedgerType)LedgerTypeFlag; }
            set { LedgerTypeFlag = (int)value; }
        }

         #endregion Derived

    }

this is the whole entity config

///entity mapping ///////////////

public class MapMember : EntityTypeConfiguration<Member>

    {
        public MapMember()
            : base()
        {
            HasKey(b => b.ID).Property(b => b.ID).HasColumnName("MBR_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            Property(p => p.MemberNo).HasColumnName("MBR_NO").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(b => b.DateApplied).HasColumnName("DTM_APPLIED");
            Property(b => b.DateMembered).HasColumnName("DTM_MEMBERED");
            Property(b => b.DateRevoked).HasColumnName("DTM_REVOKED");
            Property(b => b.MemberTypeFlag).HasColumnName("MBR_TYPE_FLG");
            Property(b => b.UserName).HasColumnName("USR_NAME");
            Property(b => b.StatusFlag).HasColumnName("MBR_STATUS_FLG");
            Property(b => b.RouteLevel).HasColumnName("ROUTE_LEVEL");


            Ignore(p => p.MemberTypeFlagDescription);
            Ignore(p => p.MemberName);
            Ignore(p => p.TotalBalance);
            Ignore(p => p.TotalLoan);
            Ignore(p => p.TotalShareAmount);
            Ignore(p => p.MemberFeeBalance);

            ToTable("MBR");
        }
    }

public class MapLoan : EntityTypeConfiguration<Loan>

    {
        public MapLoan()
            : base()
        {
            HasRequired(a => a.Member).WithMany(i => i.Loans).HasForeignKey(l => l.MemberId);
            HasKey(p => p.ID).Property(p => p.ID).HasColumnName("LON_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

            Property(p => p.LoanNo).HasColumnName("LON_NO").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(p => p.Amount).HasColumnName("AMT");
            Property(p => p.DateApplied).HasColumnName("DTM_APPLIED");
            Property(p => p.LoanTypeConfigurationId).HasColumnName("LON_TYPE_CONFIG_ID");
            Property(p => p.MemberId).HasColumnName("MBR_ID");
            Property(p => p.ScheduleTypeFlag).HasColumnName("SCHEDULE_TYPE_FLG");
            Property(p => p.Months).HasColumnName("MONTHS");
            Property(p => p.AmountSchedule).HasColumnName("AMT_SCHEDULE");
            Property(p => p.LoanStatusFlag).HasColumnName("LON_STATUS_FLG");
            Property(p => p.StatusLevel).HasColumnName("STATUS_LEVEL");
            Property(p => p.DateBillSchedule).HasColumnName("DTE_BILL_SCHEDULE");

            Ignore(p => p.LoanStatusDescription);
            Ignore(p => p.ScheduleTypeFlagDescription);
            Ignore(p => p.LoanTypeName);
            Ignore(p => p.InterestRate);
            Ignore(p => p.TotalInterestAmount);
            Ignore(p => p.TotalFeeAmount);
            Ignore(p => p.TotalCurrentBalance);
            Ignore(p => p.TotalBalance);
            Ignore(p => p.ScheduleMultiplier);

            ToTable("LON");
        }
    }

public class MapLoanLedger : EntityTypeConfiguration<LoanLedger> 

    {
        public MapLoanLedger()
            : base()
        {
            HasKey(p => p.ID).HasRequired(a => a.Loan).WithMany(p => p.LoanLedgers).HasForeignKey(l => l.LoanId);
            HasKey(b => b.ID).Property(b => b.ID).HasColumnName("LON_LEDGER_ID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

            Property(b => b.LoanId).HasColumnName("LON_ID");
            Property(b => b.DatePosted).HasColumnName("DTM_POSTED");
            Property(b => b.Description).HasColumnName("DESCRIPTION");
            Property(b => b.Amount).HasColumnName("AMT");
            Property(b => b.PayId).HasColumnName("PAY_ID");
            Property(b => b.LedgerTypeFlag).HasColumnName("LEDGER_TYPE_FLG");

            ToTable("LON_LEDGER");
        }

    }

thanks..let me know if you need some classes..

Answer 6

Any clue please?

Answer 7

It seems that the fluent API always looking for the "Loan_ID", instead of "LON_ID" which i defined on the mapping..

//Loan Ledger Class

HasRequired(a => a.Loan).WithMany(p => p.LoanLedgers).Map(m => m.MapKey("LON_ID"));

I really appreciate your help here.

Answer 8

since the error are 

Invalid column name 'Loan_ID'.

Invalid column name 'Loan_ID1'.

I added these two columns to my LON_LEDGER TABLE..

so it looks like this

/LON_LEDGER TABLE

LON_LEDGER_ID
LON_ID
DTM_POSTED
DESCRIPTION
AMT
PAY_ID
LEDGER_TYPE_FLG
LOAN_ID
LOAN_ID1

the error was gone.. 

Is this really fluent API works? 

That LOAN_ID & LOAN_ID1 should supposed to be that LON_ID column on my table... and why 2 columns?

even those two columns are null..  my code works fine, as long as it is there.. 

Hope it will make the reader understand more.. 

Need real help here..