Entity Framework relationship without foreign key constrant

0
votes

(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");
    }
2
entity-frameworkef-fluent-api
So you want a constraint, but not on the database? - Stefan
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
Have you tried anything yet? If not, you should. If yes, you should tell us what doesn't work as expected. - Tipx
Why can't you use a simple join? - Amanvir Mundra
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

2 Answers

1
votes

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.

0
votes

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.