0

I have 2 tables in my database, IncomingCheckHistory and OutgoingCheckHistory and there's a third table Log that needs a unique column named HistoryId. This number should be unique between the 2 tables (IncomingCheckHistory and OutgoingCheckHistory) so I can't use the checks' Id column as the foreign key here. Also this number should be the same for 2 records per check so I can't use hte Id from the Log table either.

public IncomingCheckHistory()
{
   public int Id { get; set; }
}

public OutgoingCheckHistory()
{
   public int Id { get; set; }
}

public Log()
{
   public int Id { get; set; }
   public int HistoryId { get; set; } //should be unique for every 2 records
}

Is there a built-in method to generate a unique integer using Entity Framework?

If there's no such method, to me the best option seems to add fourth table, insert a record and use its Id as the value here or just generate a number based on the Id of the checks.

Clarifications:

The system uses these records to connect to an ERP system. Every check needs to have 2 records (one for the deposit account and one for the credit account) and an identifier number that should be the same for both checks (to basically connect the two) but this number shouldn't be uses for any other check.

For instance, suppose we have 2 checks, one incoming and one outgoing. IncomingCheck with Id 1 OutgoingCheck with Id 1

Now if I want to insert the respective records in Log table, I'll do this:

  1. Insert a record with HistoryId 1 for Id 1 (incoming) as deposit
  2. Insert a record with HistoryId 1 for Id 1 (incoming) as credit
  3. Insert a record with HistoryId 2 for Id 1 (outgoing) as deposit
  4. Insert a record with HistoryId 2 for Id 1 (outgoing) as credit
Community
  • 1
  • 1
Alireza Noori
  • 14,961
  • 30
  • 95
  • 179
  • 1
    Whats the sense behind the `should be unique for every 2 records`? Sounds strange to me and clearing that up might help for a solution... – Christoph Fink Jan 22 '18 at 15:07
  • 1
    Could you use a GUID, and then just use the same guid for each table? It would need to be generated prior to the inserts for each record, rather than by an identity column on the table etc, but would give you a unique value that you could share between them. – Diado Jan 22 '18 at 15:11
  • 2
    I have to agree that this doesn't make much sense at this point. Some clarity on the data would help immensely. Maybe post the table definition of the three tables and some sample data that demonstrates what you are trying to accomplish? – Sean Lange Jan 22 '18 at 15:11
  • 1
    Starting with version 2012 sql server supports sequences. For earlier versions it's quite easy to [mimic](https://stackoverflow.com/questions/45319279/auto-increment-sql-value/45319922#45319922) using a single table and a stored procedure. – Zohar Peled Jan 22 '18 at 15:13
  • Do you mean that one record in the log table can point to a record in the incoming table and also to a record in the outgoing table ? In other words, there is a record in the incoming table with the same id as another record in the outgoing table. Is that what you mean ? – GuidoG Jan 22 '18 at 15:20
  • why not have the log table have 2 IDs, one for the incoming and one for the outcoming. That way one record in the log table can still be linked to 2 records without the need for unique key sharing between the incoming and outgoing table. Or maybe i just dont understand the question that could also be – GuidoG Jan 22 '18 at 15:26
  • @ChrFin The system uses these records to connect to an ERP system. Every check needs to have 2 records and an identifier number that should be the same for both checks but this number shouldn't be uses for anything else. – Alireza Noori Jan 22 '18 at 16:18
  • @Diado Yes using GUID for my system is very easy to implement and fits the requirements but unfortunately I need a number to connect to another system. – Alireza Noori Jan 22 '18 at 16:19
  • @SeanLange please read the update. Hopefully I could explain what I need clearly. Let me know if I need to do more clarifications. Thanks – Alireza Noori Jan 22 '18 at 16:26
  • @GuidoG No that's exactly what I need to avoid. The HistoryId should be unique for both tables. Meaning that if I get a HistoryId 1, I should be able to point to exactly one row in either IncomingCheck or OutgoingCheck tables. – Alireza Noori Jan 22 '18 at 16:28
  • @GuidoG Unfortunately that's not possible for me because I need to insert this number in another database which is used by another system. That system needs 2 records with a common number between the 2 records. – Alireza Noori Jan 22 '18 at 16:28
  • Why not simply use the check number and a direction column? Then add a foreign key to the check number and a constraint to the direction. You might need a third column so you know what the check belonged to in the first place (some kind of transaction id or something). – Sean Lange Jan 22 '18 at 16:30
  • @SeanLange That's the logical solution if the system is designed by me. In fact I'm doing the exact thing on one of my tables. Unfortunately as mentioned in my other comment, I will need a number to insert in another database for an ERP system. I can't change their design. – Alireza Noori Jan 22 '18 at 16:32
  • Then maybe you need yet another table to generate this number. It would hold the TransactionID and check number along with an identity. Then you can easily reference that. Rather oddball but it would work. – Sean Lange Jan 22 '18 at 16:40

1 Answers1

3

I see you have this tagged with ef-code-first. You could try to add a class named History and let both incoming and outgoing inherit from History. Then configure it to use TPC (table per concrete type). Now you can log the History Id and use the Id column to auto-generate a unique int. Not sure this is optimal for your case, but it fulfills the requirement. Alternatively you could also use TPT (table per type) to use the common stuff on history.

Mixxiphoid
  • 1,044
  • 6
  • 26
  • 46
  • I also thought of this, but there is no `should be unique for every 2 records` logic in there - that's why I asked to clarify that point... – Christoph Fink Jan 22 '18 at 15:20
  • I thought OP meant unique when the records would be listed together. Which seem to make sense by the naming of the tables. – Mixxiphoid Jan 22 '18 at 15:25
  • This should work. I need to use the `Id` from the `History` table as the `HistoryId` for the system. One thing that worries me is the fact that I need a One-To-Many map between these and another table, for example `IncomingChecbook` table has `public virtual List Checks { get; set; }`. Can EF map and lazy load the records from these tables? – Alireza Noori Jan 22 '18 at 16:38
  • Yes EF can do that. Check the article I linked. You can also check [this link](https://msdn.microsoft.com/en-us/library/jj591617(v=vs.113).aspx#Type%20Mapping) – Mixxiphoid Jan 22 '18 at 16:52