Entity Framework Generalise Id and Parent Name but have specific names like "ReferralId" and "ClientID"

Question

I have a lot of models in my program that do similar things but we have a database practice to give each column specific names so "PersonId" instead of "Id"

But I would like to deal with them in a generic way. So for example in a class I have included below I want to do something like:

public virtual List<TModel> GetAll(int parentId)
    {
        return Context.Where(i => i.ParentID == parentId).ToList();
    }

But I can't do that if ParentID unless its mapped to a database column The specified type member is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported

So I am now trying to map it to a database column:

public interface IGenModel
{
    int Id { get; set; }

    int ParentID { get; set; }
}
public class ChildNote : BaseModel, IGenModel
{


    [Key]
    [DisplayName("Child Note ID")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("ChildId")]
    public int Id { get; set; }


    [DisplayName("Referral")]
    [Column("ReferralId")]
    public int ParentID { get; set; }



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


    [ForeignKey("ReferralId")]
    public virtual Referral Referral { get; set; }

     /* Finally I tried to add something like this here
     [NotMapped]
    [Required]
    [DisplayName("Referral")]
    public int ReferralId => ParentID
    */



    /* Original set uup 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [DisplayName("Child Note ID")]
    public int ChildId { get; set; }

    [Required]
    [DisplayName("Referral")]
    public int ReferralId { get; set; }

    public int Id { get; set; }
    {
          get { return ChildId; }
          set { ChildId = value; }
    }

    public int ParentID { get; set; }
    {
            get { return ReferralId; }
            set { ReferralId = value; }
    }*/


}

But when I try and build or update the database I get errors due to:

[ForeignKey("ReferralId")]
    public virtual Referral Referral { get; set; }

As it says ReferralId does not map to something in the model.

Am I fighting a losing battle here? Is is simply not possible to generalise this? And just accept that all my accesses of context may have to be specialised? I am not going to be able to change the rule that we always prefix "Id" with the model name, apparently its very useful for searches within the database which makes sense to me.

and the classes I want to use

    public abstract class GenLoader<TSelf, TModel> where TModel : BaseModel, IGenModel where TSelf : class, new()
{
    private static TSelf instance = null;

    public static TSelf Instance
    {
        get
        {
            if (instance == null)
            {
                instance = new TSelf();
            }
            return instance;
        }
    }
    protected abstract  DbSet<TModel> Context { get; }


    public virtual TModel Get(int id)
    {
        return Context.FirstOrDefault(i => i.Id == id);
    }

    public virtual List<TModel> GetAll(int parentId)
    {
        return Context.Where(i => i.ParentID == parentId).ToList();
    }

    public virtual void OnAdd(TModel model)
    {
    }

    public virtual void Add(TModel model)
    {
        model.LastModifiedBy = WebSecurity.CurrentUserId;
        model.LastModified = DateTime.Now;
        OnAdd(model);

        using (TransactionScope scope = new TransactionScope())
        {
            Context.Add(model);
            ModelContext.Current.SaveChanges();



            scope.Complete();
        }
    }


    public virtual void OnUpdate(TModel model)
    {
    }

    public void Update(TModel model)
    {

        model.LastModifiedBy = WebSecurity.CurrentUserId;
        model.LastModified = DateTime.Now;

        OnUpdate(model);

        using (TransactionScope scope = new TransactionScope())
        {
            ModelContext.Current.Entry(model).State = EntityState.Modified;
            ModelContext.Current.SaveChanges();

            scope.Complete();
        }
    }


}

public class ChildNotes : GenLoader<ChildNotes, ChildNote>
{
    protected override DbSet<ClinicalNote> Context => ModelContext.Current.ClinicalNotes;


}

Answer 1

When applied to navigation property, ForeignKey attribute expects the FK property name, not the mapped db column name. So the following should fix the issue:

[ForeignKey("ParentID")]
public virtual Referral Referral { get; set; }

The alternative is, since you put many attributes on FK property, remove the ForeignKey attribute from navigation property and apply it on the FK property, in which case it expects the navigation property name as an argument:

[DisplayName("Referral")]
[Column("ReferralId")]
[ForeignKey("Referral")]
public int ParentID { get; set; }

...

public virtual Referral Referral { get; set; }