Multiple FKs in 1 to many relationship

Is there a way using DataAnnotation or Conventions to have multiple fields on a DTO pointing to the same opposite table …

For example, the AdventureWorks database has a Person.Address table with FKs in a Sales.SalesOrderHeader table that look like the following:

public class AwAddressInfo   // this is the PK TABLE
    public int AddressId { get; set; }

    [Reference] // how do I tell it to use the BillToAddressId column in the FK table top populate this property
    public List<AwSalesOrderHeaderInfo> SalesOrderHeadersViaBillToAddressId { get; set; }

    [Reference] // how do I tell it to use the ShipToAddressId column in the FK table top populate this property
    public List<AwSalesOrderHeaderInfo> SalesOrderHeadersViaShipToAddressId { get; set; }

public class AwSalesOrderHeaderInfo  // this is the FK TABLE
    public int SalesOrderId { get; set; }

    [ForeignKey(typeof(AwAddressInfo), ForeignKeyName="FK_SalesOrderHeader_Address_BillToAddressID")]
    public int BillToAddressId { get; set; }

    [ForeignKey(typeof(AwAddressInfo), ForeignKeyName="FK_SalesOrderHeader_Address_ShipToAddressID")]
    public int ShipToAddressId { get; set; }

If there’s only one foreign key, pointing back to the address table, then the right data is set on the Address navigation property (i.e.: SalesOrderHeadersViaBillToAddressId or SalesOrderHeadersViaShipToAddressId), but when both are set, the data isn’t correct, which makes sense as there doesn’t appear to be a way to specifically tell in the “[Reference]” annotation which field on the foreign key table to use to get the list of object to assign in a join statement…

If this isn’t supported right now, that’s fine. It does seem to be a good thing possibly to support in the future, maybe (?).

Thanks …

You can only define Multiple 1:1 Self-References on the same table, but Multiple 1:M references isn’t supported, we also don’t have any way to model them yet.

It would be a nice feature, but don’t we’d need to come up with an intuitive way we add them, which isn’t clear atm.

Yeah, I saw that if I have BillToAddressId, BillToAddress, ShipToAddressId, ShipToAddress on the SalesOrderHeader side (in my example), those all work as described in the docs.

I think it would be a nice feature on the 1:M side too, but I’ll leave that up to you guys as far as timeline and implementation :smile: . I have seen this done in other ORMs with various strategies… I think in my opinion, extending the Reference annotation would be a nice way to do it that seems to be in the same spirit as other things I’ve seen (i.e.: [Reference(ForeignKeyColumnAlias(“BillToAddressId”)]), or using a “Via” convention like I have in my example, but really, whatever works best.

We can close this ticket, but just wanted to ask; I’m going to be a bit more explicit in my implementation I think at this time to get around this.

The Load* API’s shouldn’t be populating anything that doesn’t have a [Reference] which it will think it’s a blobbed property instead and yeah you can use [Ignore] to tell OrmLite to ignore the property completely. AutoQuery also has support for Customizable fields.

You can also always create a custom AutoQuery implementation that uses Select instead of LoadSelect which will not attempt to populate [Reference] properties.

public object Any(MyAutoQuery dto)
    var q = AutoQuery.CreateQuery(dto, Request.GetRequestParams());
    return new QueryResponse<AwAddressInfo> {
        Offset = q.Offset.GetValueOrDefault(0),
        Results = db.Select<AwAddressInfo>(q, include:q.OnlyFields),

Warning: may not compile - coding blind w/o an IDE :slight_smile:

Thanks, I’m pretty sure I can achieve what I need with these options, I was already going down the the custom AutoQuery implementation anyway, somehow I overlooked the customizable fields …

One last thing I ran into was with the Metadata feature (might affect Swagger, haven’t checked) where it seems that certain data structures just keep the operation UI hanging -> “/json/metadata?op=[name of operation]”. (i think an infinite recursion behind the scenes when the DTOs are reflected ??) when DTOs point back to each other, even though running the APIs and AutoQuery works fine, but I have to restart the process after I go into one of those Metadata operations… I know that DTOs pointing back to each other is not desired (i.e.: cyclical philosophy), but it’s useful to re-use the same DTOs when you control when and how you’re populating them (i.e.: querying data from different angles, and with the custom AutoQuery implementation which let’s you control that too …). This is only an issue in a codegen situation for me right now, so I’m getting around it right now by creating both an Info (value-type properties only) and a full object that inherits from the Info object (with all references, etc…) … and relation properties always refer to the Info types which guarantees that the Metadata reflection code doesn’t hang (that’s my guess at what’s happening), and because of the inheritance I can populate the properties with whatever references I need … Hopefully that makes sense, :pensive:

Yeah cyclical references in DTO’s are not supported, you should be able to [IgnoreDataMember] the public property or use a private field/public method. But they should never really be in DTO’s.

Thanks … I can manage around it …