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:
[Alias("Address")]
[Schema("Person")]
public class AwAddressInfo // this is the PK TABLE
{
[Alias("AddressID")]
[AutoIncrement]
[PrimaryKey]
[Required]
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; }
}
[Alias("SalesOrderHeader")]
[Schema("Sales")]
public class AwSalesOrderHeaderInfo // this is the FK TABLE
{
[Alias("SalesOrderID")]
[AutoIncrement]
[PrimaryKey]
[Required]
public int SalesOrderId { get; set; }
[Alias("BillToAddressID")]
[ForeignKey(typeof(AwAddressInfo), ForeignKeyName="FK_SalesOrderHeader_Address_BillToAddressID")]
[Required]
public int BillToAddressId { get; set; }
[Alias("ShipToAddressID")]
[ForeignKey(typeof(AwAddressInfo), ForeignKeyName="FK_SalesOrderHeader_Address_ShipToAddressID")]
[Required]
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 (?).
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 . 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
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,
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.
. 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.
