Entity Framework relationship without foreign key constrant

Question

(Note - I have no control over the database and it's tables).

Giving an example of my problem:

Table A has on it ID (PK) and CreateDate Table B has on it ID (PK) and A_ID

A_ID is not a foreign key but table B needs to get the create_date from table A. How do I do this in Entity Framework?

Not sure if this is relevant but A can have 0 or Many of B and B can only have one of A

Edit: Forgot to mention using Fluent API

Edit 2: I've tried mapping the two tables.

I added public virtual A a {get;set} on to B's entity model.

Then from there, on to the Entity Configuration, I tried a couple different things such as HasRequired(x => x.A).WithOptional() and HasRequired(x => x.A).WithMany().HasForeignKey(x => x.A_ID) (even though with many is wrong but it's the only way I saw how to specify the foreign key, also knowing there is no foreign key). I also added HasKey(x => x.A_ID) but that didn't seem right to me anyways.

Basically, I tried every type of Has and With. I'm still pretty new to entity framework fluent api but I've searched the internet and found similar problems, but not similar enough to help me come up with an answer. Mostly, I found 'It's not possible' type answers.

In terms of doing a join, correct me if I'm mistaken, that's not fluent api and it's not mapping the tables to each other right? I would just be joining the tables inside my method?

Edit 3: Entity model and Entity configuration so far, with unnecessary stuff removed and name of things changed (but otherwise it's entirely accurate)

public class A
{
    /// <summary>
    ///     ID
    /// </summary>
    public virtual int ID { get; set; }

    /// <summary>
    ///     Relationship to B
    /// </summary>
    public virtual B b { get; set; }
}

public class B
{
    /// <summary>
    ///     ID of B
    /// </summary>
    public virtual int ID { get; set; }

    /// <summary>
    ///     Name of B
    /// </summary>
    public virtual string Name { get; set; }

    /// <summary>
    ///     DateTime B is created
    /// </summary>
    public virtual DateTime? DateTime { get; set; }
}

    public AConfiguration()
    {
        Property(x => x.ID).HasColumnName("a_id");
        Property(x => x.B_ID).HasColumnName("b_id");

        HasKey(x => x.ID);

        ToTable("a", "safe");
    }

    public BConfiguration()
    {
        Property(x => x.ID).HasColumnName("b_id");
        Property(x => x.DateTime).HasColumnName("b_datetime");
        HasKey(x => x.ID);

        ToTable("b", "safe");
    }

i think the answer to this is yes. i want to treat a_id as a foreign key, even though the database doesn't (it should be but for some reason it wasn't set up that way). — sam
– sam, Commented May 26, 2017 at 14:59
Have you tried anything yet? If not, you should. If yes, you should tell us what doesn't work as expected. — Tipx
– Tipx, Commented May 26, 2017 at 15:17
or lambda function, this is the easiest thing, you haven't showed any effort : db.tableB.FirstOrDefault( t => t.id_a == id_your_checking_from_tableA); so start coding and come back with a reel problem — Antoine Pelletier
– Antoine Pelletier, Commented May 26, 2017 at 15:31

David Browne - Microsoft · Accepted Answer · 2017-05-26 19:42:37Z

1

If you're having trouble with the fluent mapping for the Navigation properties, try creating a sample database that does have the foreign keys, and generate a code-first model from that. That should show you the proper configuration.

answered May 26, 2017 at 19:42

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

matt · Accepted Answer · 2021-06-24 15:58:28Z

1

What has worked for me is:

in A:

public int ID { get; set; }

in B:

public int A_ID { get; set; }
public virtual A A { get; set; }

in your entitybuilder for B:

builder
  .HasOne(b => b.A)
  .HasMany()
  .HasForeignKey(b => b.A_ID)
  // I'm assuming you already have A's primary key set up.
  // If not, add .HasPrincipalKey(a => a.ID)

I strongly recommend nagging the DBA to add the missing foreign keys, though, because bad data can screw up your whole query when you're faking them like this.

answered Jun 24, 2021 at 15:58

matt

1911 silver badge16 bronze badges

Collectives™ on Stack Overflow

Entity Framework relationship without foreign key constrant

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related