Creating one to many relationship in Entity Framework using Fluent API

Question

I have the following POCO Classes:

public class Client
{
    public string ClientId { get; set; }
    public string CompanyId { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }

    [ForeignKey("AccountId")]
    public virtual ICollection<Note> Notes { get; set; } 
}

public class Note
{
    public decimal NoteId { get; set; } 
    public string AccountId { get; set; }
    public string NoteValue { get; set; }

    [ForeignKey("ClientId")]
    public virtual Client Client { get; set; }
}

and the following mapping classes:

    public ClientMap(string schema)
    {
        ToTable("CLIENTS", schema);

        // Primary Key
        HasKey(t => t.ClientId);

        // Properties
        Property(t => t.ClientId)
            .HasColumnName("CLIENT_ID")
            .IsRequired()
            .HasMaxLength(16);

        Property(t => t.CompanyId)
            .HasColumnName("COMPANY_ID")
            .IsRequired()
            .HasMaxLength(16);

        Property(t => t.LastName)
            .HasColumnName("LAST_NAME")
            .IsRequired()
            .HasMaxLength(50);

        Property(t => t.FirstName)
            .HasColumnName("FIRST_NAME")
            .IsRequired()
            .HasMaxLength(50);
    }

    public NoteMap(string schema)
    {
        ToTable("NOTES", schema);

        // Primary Key
        HasKey(t => t.NoteId);

        // Properties
        Property(t => t.NoteId)
            .HasColumnName("NOTE_ID")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        Property(t => t.AccountId)
            .HasColumnName("ACCOUNT_ID")
            .IsRequired()
            .HasMaxLength(16);

        Property(t => t.NoteValue)
            .HasColumnName("NOTE")
            .HasMaxLength(4000);
    }

In this model (Using Fluent API), there is a one to many relationship between clients and notes. ClientID is the PK in clients and NoteId is the PK in Notes. There are no foreign keys in the DB. ClientId maps to the AccountId in Notes.

I can not get this to work. When I run it, I can get most of the client data back, but when trying to navigate to a note, I get a Funcation Evaluation Timed out error when trying to look at Notes. I can not get the relationship between clients and notes to work. Where have I gone wrong? (I would like to do this using Fluent API)

Thanks

ocuenca · Accepted Answer · 2015-08-12T15:27:41.943

1

You are trying to create a typical one to many relationship. First, remove the data annotations that you are using in your model, you don't need them if you are going to use Fluent Api. Second, add the ClientId FK property in your Note entity

public class Note
{
    public decimal NoteId { get; set; } 
    public string AccountId { get; set; }
    public string NoteValue { get; set; }

    //FK property
    public int ClientId{get;set;}

    public virtual Client Client { get; set; }
}

Then, in the constructor of your NoteMap class, add this Fluent Api configuration:

 HasRequired(n=>n.Client).WithMany(c=>c.Notes).HasForeignKey(n=>n.ClientId);

edited Aug 12 '15 at 15:27

answered Aug 12 '15 at 15:22

ocuenca

38,548
11
89
102

Progress, now the function does not time out, but I don't get any notes back. – kurabdurbos Aug 12 '15 at 15:27
A time out? Could you drop your DB (if already exist) and try again? – ocuenca Aug 12 '15 at 15:29
When I had it with the annotations, it was timing out. But I forgot to fix an additional change I had made earler. I get the full client record back but when trying to navigate to the notes, I get an CommandExecution error. Its saying that Oracle is not reconizing the ClientId is an invalid identifer. So it seems to me that the query is not building properly since client id was in proper case vs all uppercase. – kurabdurbos Aug 12 '15 at 15:32
But after remove the annotations and add the fluent api configuration that I suggested? Have you tried add some new data?. Have you checked if your database schema has changed? What do you mean with " I can get the full client record back"? – ocuenca Aug 12 '15 at 15:37
Apparently you are working with an existing DB, I think you should add this configuration in the `NoteMap` constructor:`Property(t => t.ClientId).HasColumnName("CLIENT_ID");// the FK column name in the Notes table` – ocuenca Aug 12 '15 at 15:46
We had to change ....HasForeignKey(n=>n.ClientId) to (n=>n.AccountId) to get it to work. But it is now returning data. Thanks for your help!! – kurabdurbos Aug 12 '15 at 17:37

Creating one to many relationship in Entity Framework using Fluent API

1 Answers1