CODEWITHSUNDEEP
linqentity-frameworklinq-to-entities
AnonyMouse
AnonyMouse

Reputation: 18630

Entity Framework multiple relationships between tables

I've have two tables in my project which are User and InvoiceLine.

It has been specified that an InvoiceLine can have a User known as a Checker.

My models are:

public class InvoiceLine : IEntity
    {
        public virtual int Id { get; set; }
        public virtual int? CheckerId { get; set; }
        public virtual string CreatedByUserName { get; set; }
        public virtual DateTime CreatedDateTime { get; set; }
        public virtual string LastModifiedByUserName { get; set; }
        public virtual DateTime? LastModifiedDateTime { get; set; }

        // Navigation properties
        public virtual User Checker{ get; set; }
}



public class User : IEntity
    {
        public int Id { get; set; }
        public string CreatedByUserName { get; set; }
        public DateTime CreatedDateTime { get; set; }
        public string LastModifiedByUserName { get; set; }
        public DateTime? LastModifiedDateTime { get; set; }

        //Navigation properties
        public virtual ICollection<InvoiceLine> InvoiceLines { get; set; }
    }

So this was fine I have a 0..1 to many relationship from User to InvoiceLine.

This meant with Linq I could get the InvoiceLines the User needs to check via:

user.InvoiceLines

However there is another requirement that an InvoiceLine also has an Auditor so I modified the InvoiceLine to:

public class InvoiceLine : IEntity
    {
        public virtual int Id { get; set; }
        public virtual int? CheckerId { get; set; }
        public virtual int? AuditorId { get; set; }
        public virtual string CreatedByUserName { get; set; }
        public virtual DateTime CreatedDateTime { get; set; }
        public virtual string LastModifiedByUserName { get; set; }
        public virtual DateTime? LastModifiedDateTime { get; set; }

        // Navigation properties}
        public virtual User Checker { get; set; }
        public virtual User Auditor { get; set; }
}

So what I was really wanting was to go:

user.InvoiceLines

and get the Checkers and Auditors or alternatively get them seperately via:

user.CheckerInvoiceLines
user.AuditorInvoiceLines

I'm getting null back from user.InvoiceLines though which is understandable.

Could someone please point me in the right direction on how to use Linq to get the InvoiceLines from the User?

Edit Update:

My model configuration code is like:

public class VectorCheckContext : DbContext
    {
        ...

        public DbSet<InvoiceLine> InvoiceLines { get; set; }
        public DbSet<User> Users { get; set; }

        ...

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        }
    }

Upvotes: 3

Views: 4244

Answers (1)

Eranga
Eranga

Reputation: 32437

You need to use fluent mappings to configure the relationships when EF can not resolve them by conventions.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

        //other mappings

        modelBuilder.Entity<InvoiceLine>()
           .HasOptional(i => i.Checker)
           .WithMany(u => u.CheckerInvoiceLines)
           .HasForeignKey(i => i.CheckerId);

        modelBuilder.Entity<InvoiceLine>()
           .HasOptional(i => i.Auditor)
           .WithMany(u => u.AuditorInvoiceLines)
           .HasForeignKey(i => i.AuditorId);
    }

Upvotes: 7

Related Questions