OrmLite and composite Id from other properties

1

I was wondering if there is a better way of doing this.

Most examples for ormlite are using int for id’s.

I’m not sure if the following scenario is supported somehow.

In this scenario, the Tenant.Id and TenantBin.Id are provided by another system and are strings.

Given these 2 classes:

public class Tenant
{
    private string _id;

    [PrimaryKey]
    [Required]
    public string Id
    {
        get => _id;
        set => _id = value.ToLowerInvariant();
    }
}


[UniqueConstraint(nameof(Tenant), nameof(BinNumber))]
public class TenantBin
{
    [PrimaryKey]
    [Required]
    [StringLength(50)]
    public string Id => $"{TenantId}_{BinNumber}";
    
    [Required]
    [References(typeof(Tenant))]
    public string TenantId { get;  set; }
    
    [Required]
    public string BinNumber { get;  set; }
    
    [Required]
    public bool Active  { get;  set; }
}

I want to make sure that for a single entry I never have the following situation:

A TenantBin entry is updated like so:

A previously persisted TenantBin entry let’s say with these values:

TenantIdOne_BinOne TenantIdOne BinOne True

Is updated and results in the Id being out of synch with the properties values:

TenantIdOne_BinOne TenantIdOne BinTwo True

The Tenant.Id and TenantBin.Id are known when the objects are created (always provided never need to be generated).

If this scenario is not supported id like to know if there’s a way to add a constraint so the above scenario can’t happen.

I’m using MySql.

2

Primary Keys should be immutable so creating a composite one like this on values that can change is a bad idea, it’s generally recommended to use a surrogate primary key (e.g. auto incrementing) whilst you can use your composite unique constraint to enforce uniqueness.

3

That’s what I’ll do. Thanks.

4

I have question on using Save if the Id is not known but another property is unique.

I have modified the classes in my initial question like so:

public class Tenant
{
        [PrimaryKey]
        [AutoId]
        public Guid Id { get; set;}

        [Unique]
        [Required]
        public string Name { get; set;}

		public string Description { get; set;}        
}


Tenant tenant = new Tenant
{
    Name = "tenantName",
    Description = "The description"
};

var resObjectPersisted = rawDb.Save(tenant);

//a guid is generated.

//Application exit.

Another application starts:

Tenant tenant = new Tenant
{
    Name = "tenantName",
    Description = "Updated description"
};

var raiseDuplicateKey = rawDb.Save(tenant); //This will throw a Duplicate entry key MySql exception.

//So I've been using this: 

if (!rawDb.Exists<Tenant>(x => x.Name == tenantName))
{
    rawDb.Save(tenant);
}
else
{
	//to get the Id.
    tenant = rawDb.Single<Tenant>(x => x.Name == tenantName);

    tenant.Description = "Updated description";

	rawDb.Save(tenant);
}

But since Name is unique and the Guid isn’t known ahead of time is there a better way of saving since Name is unique can it be used a the identity selector somehow?

5

You can attempt an UPDATE and check that if it doesn’t update any rows to INSERT it instead, e.g:

var rows = db.UpdateOnly(() => new Tenant {Description = "Updated description"}, 
   where: p => p.Name == tenantName);

if (rows == 0)
   db.Insert(tenant);
1 Like