Effectively Managing Duplicate and Mismatched Properties in EntityTableData and Existing Database Entities

[nicolasHul]

I’ve made several attempts to resolve this issue and have also reviewed similar issues such as this one and this SO post post. However, I ultimately decided not to use AutoMapper due to the lack of clear usage examples. My current solution partially works - creating new entities and retrieving them works fine, but updating an existing entity (and consequently, deleting them properly) does not.

My current setup/use case:

I have an existing table in my database called EntityClient. It has a primary key (PK) named Id of type int with Identity Specification set to true in my SQL Server, and an Identity increment and seed of 1. This is the first conflict, as the EntityTableData/DataSync also uses the Id property for syncing, but with the string type (GUID value).

To avoid the issue of duplicate column names, I changed the DataSync column name from Id to MobileId in my database table (all other DataSync columns were added as well, but their names remained the same).

In my EF Core Model setup, I used the Schema Column attributes to point to the correct database columns, while also maintaining the proper names for the DataSync library. So, my EF model looks like this:

public partial class EntityClient : EntityTableData
{
    [Column("Id")]
    public int ClientId { get; set; }

    [Column("MobileId")]
    public override string Id { get; set; }

    //.. omitted some other non-relevant properties
}

My EF Core configuration needed a small change since the HasKey property is now pointing to ClientId instead of Id.

public void Configure(EntityTypeBuilder<EntityClient> entity)
{
	entity.HasKey(e => e.ClientId).HasName("PK__EntityCl__3214EC0716CE6296");
    
	// .. other configuration omitted

	OnConfigurePartial(entity);
}

As stated, creating new entities or querying the entities works fine with this setup. But when doing a replace (update), I get the following error, which is logical because DataSync seems to be using Find(Async) behind the scenes. The error is:

The key value at position 0 of the call to 'DbSet<EntityClient>.Find' was of type 'string', which does not match the property type of 'int'.

Sadly, because of backward compatibility with another application, I cannot change the current int primary key in my database.

So, I am considering the following options:

• Use AutoMapper, but unfortunately, I am not sure how to set that up properly with my current requirements.
• Perhaps override the ReplaceAsync in my TableController<EntityClient>? And then do a correct lookup? But I can’t seem to figure out how to get this working.

[adrianhall]

Take a look at the EntityTableRepository, which contains all the information you need. I'll see if I can put together a sample that provides what you are looking for.

Basically, you have two entities - a "mobile entity" where the Id must be string and settable by the client, and a "normal entity" where the Id is int. You need to map between them. Since you have another client that is updating the records, you will also need to arrange for UpdatedAt, Version, and Deleted to be maintained by the other (non-mobile) client. UpdatedAt can be maintained through the use of a trigger, and Version is a [Timestamp], which is generally well supported. Deleted is usually the difficult one - instead of "hard deleting" a row, you will need to mark it as deleted and then hard delete it later.

So, basically you copy the EntityTableRepository and adjust each and every method to do what you need.

[nicolasHul]

Thank you for your fast reply!

I'll see if I can put together a sample that provides what you are looking for.

That would be awesome!

But I am still a bit confused about some things. I assume that by the "Mobile entity" you mean the DTO object? So, my DTO and mapping would look something like this:

My ‘mobile DTO’ object (I omitted some properties for brevity):

public class EntityClientDto : EntityTableData
{
    public int ClientId { get; set; }

    public string? Name { get; set; }
}

The EntityClient (database Entity) is now changed back, the Id property is now an int again like before and I added the MobileId property that will be mapped. I also added the ‘DataSync’ properties to the model and removed the EntityTableData inheritance:

public partial class EntityClient  
{
	public int Id { get; set; }
	
	public string? Name { get; set; }
	
	public string MobileId { get; set; }
	
	public DateTimeOffset UpdatedAt { get; set; } = DateTimeOffset.UtcNow;

	[Timestamp]
	public byte[] Version { get; set; }

	public bool Deleted { get; set; }
}

The mapping looks then as following:

public class MapperProfile : Profile
{
    public MapperProfile()
    {
        CreateMap<EntityClient, EntityClientDto>()
            .ForMember(dest => dest.Id, 
                opt => opt.MapFrom(src => src.MobileId))
            .ForMember(dest => dest.ClientId, 
                opt => opt.MapFrom(src => src.Id))
            .ReverseMap()
            .ForMember(dest => dest.MobileId, 
                opt => opt.MapFrom(src => src.Id))
            .ForMember(dest => dest.Id, opt => 
                opt.MapFrom(src => src.ClientId));
    }
}

However, as far as I can tell, this mapping does not change the fact that my EntityClient still needs to inherit from EntityTableData / ITableData, since both EntityTableRepository and the TableController itself require a class that inherits from ITableData, which in turn requires the class to have the ‘DataSync’ properties that cause the naming issue in the first place.

Also, when looking a bit more into the AutoMapper code found here, I can see that it uses a different controller named MappedTableRepository, and this does some mapping between the two objects, but still requires, besides the DTO object, an ‘Entity’ that inherits from the ITableData?

So, basically you copy the EntityTableRepository and adjust each and every method to do what you need

So, what you suggest is to create my own custom EntityTableRepository and modify all the endpoints (delete, replace, etc) so that they work using my int id instead of a string? But that would still require the ITableData properties, right?

TLDR; Either way I am looking at it now, my EntityClient needs to inherit from ITableData either way to make the DataSync work (using any of the controllers), though this would still cause my initial problem?

Regarding the non-mobile client, it seems to be fine as the entity (table) being used for offline data sync is currently only being updated from my mobile client. This other client is an older MVC application, which will likely be updated in the near future with direct database control using EF Core.

So, can’t I just set the UpdatedAt and Deleted fields directly when inserting, updating or deleting those entity records?
Or perhaps use a default GETDATE() or GETUTCDATE() value on my SQL column for the UpdatedAt field? Isn’t the Version (timestamp) field also auto generated in SQL Server?

TLDR2; I don’t see why I would need a trigger for these fields. Am I misunderstanding something?

I’m probably looking at the entire issue in the wrong way, so some examples or further guidance would be greatly appreciated!

[adrianhall]

I htink you are looking at this wrong.

I'm going to write a sample and a blog post around the same to describe it. I just need a couple of weeks to clear my desk.

[nicolasHul]

I htink you are looking at this wrong.

I'm going to write a sample and a blog post around the same to describe it. I just need a couple of weeks to clear my desk.

I already thought so. Thank you for the help!

[adrianhall]

I've just posted a new blog post @nicolasHul

See https://adrianhall.github.io/azure/2023/12/22/zumo-intid/

[nicolasHul]

Thank you! As it turns out, and as is often the case, it wasn’t as difficult as I initially thought. I appreciate your outstanding support!