3

In Linq to SQL I could specify a relationship that didn't have to depend on the foreign keys and pks existing in the database, useful for creating composite relationships like this:

public class Equipment_CableNormalised
{
    ...

    [Association(ThisKey = "EquipmentId,PortNumber", OtherKey = "EquipmentId,PortNumber", IsForeignKey = false)]
    public List<EquipmentPort> EquipmentPorts
    {
        get; set;
    }

}

This then generated the sql similar to " .. join EquipmentPorts EP on EP.EquipmentId = blah and EP.PortNumber = Blah".

Can I do the same sort of thing in EF4.1 (using annotations or fluent api)? I know you can specify composite keys and use the [Keys] and [ForeignKeys] attributes, but this relationship doesn't map to keys...

Matt Roberts
  • 26,371
  • 31
  • 103
  • 180
  • What do you mean by "relation doesn't map to keys"? If it doesn't map to keys (PK/UK, FK) it is not a database relation. – Ladislav Mrnka Jul 11 '11 at 09:00
  • It isn't a database relation. As it happens, one of these entities actually maps to a view, not a table. It was a handy solution in L2S to wire these entities together with that association definition, that saved me from having to write more complex querying code. So it's not a "pure" representation of what's in the database – Matt Roberts Jul 11 '11 at 09:10

2 Answers2

0

How does the sample relation from your code works? I expect that EquipementId must be either PK or unique key (not supported in both L2S and EF) on one side because otherwise the relation could not exist (both one-to-one and one-to-many demands unique principal). Once it is PK on one side the port number is redundant.

Code first allows only mapping to keys. If you have existing database you can cheat it in your model and map new relations in the same way as you would map existing but you still have to follow simple rule - properties in principal are primary keys, properties in dependent entity are mapped as foreign keys.

If you want EF to generate DB for you, you will always have all relations in the database.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Thanks for the response. I'll never want to gen a DB from EF in this case, it's quite complex and has stored procs and some EF non-supported data types in there. In L2S you _can_ create relationships (associations) as per my example that don't actually map to a relationship that exists in the database. On one side of that example is a view (so no keys), and on the other side is a table that has it's own "id" PK. I see it as L2S allowing me to just define ad-hoc relationships, just as you might "JOIN" two tables in SQL that don't actually have a defined relationship. – Matt Roberts Jul 12 '11 at 09:01
0

Use HasKey http://www.ienablemuch.com/2011/06/mapping-class-to-database-view-with.html

Either use HasKey, put this on OnModelCreating

 modelBuilder.Entity<SalesOnEachCountry>().HasKey(x => new { x.CountryId, x.OrYear });   

Or use Key Column Order

public class SalesOnEachCountry
{        
    [Key, Column(Order=0)] public int CountryId { get; set; }
    public string CountryName { get; set; }
    [Key, Column(Order=1)] public int OrYear { get; set; }
     
    public long SalesCount { get; set; }      
    public decimal TotalSales { get; set; }
}

Regarding your question about foreign key, I haven't yet tried the pure code(OnModelCreating) approach, perhaps you can just put two ForeignKey attribute on child class itself, might need to put Column Order too.

This could be the answer composite key as foreign key

That answer confirms my hunch that you could put two ForeignKey attributes on child class itself.

Community
  • 1
  • 1
Michael Buen
  • 38,643
  • 9
  • 94
  • 118