ServiceStack.OrmLite - how to enable a field from an external search?

Question

I am trying to use ServiceStack MVC PowerPack and trying to enable ORM ORmLite, and I am trying to get data from a table referenced by a foreign key, without any idea how to do this.

In OrmLite examples that use the Northwind database, would you return a Shipper object that included "ShipperTypeName" as a string viewed by the foreign key "ShipperTypeID"?

From http://www.servicestack.net/docs/ormlite/ormlite-overview , I would like to add the ShipperName field to the Shipper class, if possible:

[Alias("Shippers")] public class Shipper : IHasId<int> { [AutoIncrement] [Alias("ShipperID")] public int Id { get; set; } [Required] [Index(Unique = true)] [StringLength(40)] public string CompanyName { get; set; } [StringLength(24)] public string Phone { get; set; } [References(typeof(ShipperType))] public int ShipperTypeId { get; set; } } [Alias("ShipperTypes")] public class ShipperType : IHasId<int> { [AutoIncrement] [Alias("ShipperTypeID")] public int Id { get; set; } [Required] [Index(Unique = true)] [StringLength(40)] public string Name { get; set; } } 

Answer 1

To do this, you will need to use Raw SQL, which contains all the fields you need, and create a new model that matches SQL, so for this example you would do something like:

 public class ShipperDetail { public int ShipperId { get; set; } public string CompanyName { get; set; } public string Phone { get; set; } public string ShipperTypeName { get; set; } } var rows = dbCmd.Select<ShipperDetail>( @"SELECT ShipperId, CompanyName, Phone, ST.Name as ShipperTypeName FROM Shippers S INNER JOIN ShipperTypes ST ON S.ShipperTypeId = ST.ShipperTypeId"); Console.WriteLine(rows.Dump()); 

To deduce the following:

 [ { ShipperId: 2, CompanyName: Planes R Us, Phone: 555-PLANES, ShipperTypeName: Planes }, { ShipperId: 3, CompanyName: We do everything!, Phone: 555-UNICORNS, ShipperTypeName: Planes }, { ShipperId: 4, CompanyName: Trains R Us, Phone: 666-TRAINS, ShipperTypeName: Trains } ]