none
Hierarchical navigational properties using EF Codefirst RRS feed

  • 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

    Friday, April 27, 2012 12:56 PM

All replies

  • Anyone could help me here? 
    Sunday, April 29, 2012 2:33 AM
  • 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

    Monday, April 30, 2012 5:43 AM
    Moderator
  • 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

    Monday, April 30, 2012 8:42 AM
  • 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

    Tuesday, May 1, 2012 8:11 AM
    Moderator
  • 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..

    Tuesday, May 1, 2012 3:37 PM
  • Any clue please?
    Thursday, May 3, 2012 12:32 AM
  • 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.

    Friday, May 4, 2012 9:57 AM
  • 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..

    Wednesday, May 9, 2012 11:49 AM