Simple SELECT FOREIGN KEY with ServiceStack V3.9

Question

I'm trying to make a simple select to insert an foreign key. I tried several things:

        var Id = Db.Select<DeviceInfo.DeviceInfo>("SELECT DeviceId FROM DeviceInfo WHERE (deviceType = 1 AND humanReadDevId = 001)");

        //var Id = Db.Select<DeviceInfo.DeviceInfo>(d => d.Id).Where(d => d.deviceType == 1 && a.humanReadDevId = 001);

        //var rows = Db.Select<DeviceInfo.DeviceInfo>(@"DeviceType = {0} AND HumanReadDevId = {1} ", "1", "001");

I have to table DeiviceInfo an deviceHistory, I have to put the DeviceId on the field ByDeviceId in DeviceHistory.

Here is class DeviceInfo:

public class DeviceInfo : IReturn<DeviceInfoResponse>
{
    /// <summary>
    ///     Gets or sets the id of the DeviceInfo. The id will be automatically incremented when added.
    /// </summary>
    [AutoIncrement]
    public int DeviceId { get; set; }

    // Data reveived
    public int DeviceType { get; set; }
    public string HumanReadDevId { get; set; }
    public string Hardware { get; set; }
    public string Model { get; set; }

}

And DeviceHistory:

public class DeviceHistory : IReturn<DeviceHistoryResponse>
{

    /// <summary>
    ///     Gets or sets the id of the DeviceHistory. The id will be automatically incremented when added.
    /// </summary>
    [AutoIncrement]
    public int DevHistId { get; set; }

    // Auto generated with data received "DeviceType" and "HumanReadDevId"
    [References(typeof(DeviceInfo.DeviceInfo))]
    public int ByDeviceId { get; set; }

    // Data reveived
    public int DeviceType { get; set; }             // To generate Id
    public string HumanReadDevId { get; set; }      // To generate Id

    public string Firmware { get; set; }
    public string SWVersion1 { get; set; }
    public string SWVersion2 { get; set; }
    public string Status { get; set; }

    // Auto generated with data received "mail" 
    public string UserEmail { get; set; }

}

I try a lot of different way to get the id, but every times my request return null. I create and different file for every class, I'm not sure if it's my implementation who is bad or my request... Maybe both of them.

Could you give me an example for a simple SELECT with the version 3.9 of service Stack please?

Answer 1

The easiest way to get the new Id would be to have the insert return it by using selectIdentity. You can do something like this:

var newId = Db.Insert(DeviceInfo, selectIdentity: true);

newId would then contain the AutoIncremented Id that was generated.

Another thing that draws my eye is that the primary key on your Poco's is not named "Id". I'm not certain if it is required conventionally, but I think it would allow use of some of the ormlite extensions. You could still store it in your DB as DeviceId by decorating it with the [Alias("DeviceId")] attribute.

To fetch a single entity by primary key you can do the following:

Db.SingleById<DeviceInfo>(id);