Composite fields auto increment

[Juremir]

Hello!

I want to know if it's possible to do the following:

Table 1
   Id - Primary Key autoincrement
   Column1 - FK
   Column2 - Column populated based on column1

Output expected:

  Id - Column1 - Column2
  1           1                1
  2           1                2
  3           1                3
  4           2                1

[Boyan]

Hello Juremir,

If I understood you correctly, you wish to have a column which is automatically populated on based on the value inserted in another column of your table. Could you confirm that?
If so, generally speaking this could be done in several ways:
1. You could create custom logic that calculates the values for the auto-generated column and sets a property of the persistent class mapped to that table before SaveChanges() is called. This could be implemented in different places in your application depending on you scenario.
2. You could configure Telerik Data Access to use stored procedures on inserting new row in a table. You could modify this procedures adding logic for calculating and populating given columns of your table.
3. You could use database computed columns and then configure Telerik Data Access to work with them as described here.
4. You could use database triggers.
5. You could use database views.

In order to assist you better and suggest a solution optimal for you, I would like to ask you for more information on your scenario. What problem you would like to solve using such functionality? On what criteria Column2 is actually calculated? Is Column2 used for marking sequential entries with the same foreign key, and if so does the primary key already contain this information?

I am looking forward to hearing from you. 

Regards,
Boyan
Telerik

[Juremir]

Hello Boyan,

[quote]you wish to have a column which is automatically populated on based on the value inserted in another column of your table

 

[/quote], yes thats it.

[quote]Is Column2 used for marking sequential entries with the same foreign key[/quote], yes too. The primary key don't affect the logic, just the Column1 and Column2.

I would like to do this using fluent mapping.

Thanks for reply.

[Boyan]

Hello Juremir,

From the setup you provided it seems that Column2 keeps the sequence in which records in Column1 are inserted. If you take into consideration deletes and edits, this means that each new value inserted in Column2 is actually based on multiple records in Column1. Please note that such setup poses serious performance risks if your table became too large. The same would not be true if Column2 depended on Column1 just on the same row. 
Additionally, the same information could be obtained by the following query (provided that the primary key is incremental) where the ordering of the resulting collection would correspond to the values of Column2:

context.Table1.Where(x => x.Column1 == value).OrderBy(x => x.ID).ToList();

That being said, I could suggest a couple of ways to implement your scenario.
Firstly, you could calculate the new values of Column2 manually before inserting each record in the table. For example:

var context = new FluentModel();

var table = new ClientSideComputedTable();

table.Column1 = value;

table.Column2 = context.ComputedTables.Count(x => x.Column1 == value);
context.Add(table);

context.SaveChanges();

Please note that custom logic that updates the table could be applied when deleting a record as well. You could call context.GetChanges() before SaveChanges() and analyze the result if it contains deletes/updates/inserts operations and take the appropriate actions. This custom logic could be placed in a partial class that extend your context class either in separate methods or be plugged in by overriding SaveChanges() if that is feasible. I would suggest you to go with this approach if you expect quite a few CUD operations a lot of selects

As alternative approach you could use computed columns that would be recalculated by the database server when necessary. In order to do that, I followed those steps:
1. On the database server I create a user defied function that would calculate correct values for Column2:

CREATE FUNCTION ufn_CountFK (@Id int, @Column1 INT)
RETURNS INT
AS
BEGIN
    DECLARE @result int;

    SELECT @result = COUNT(*)
    FROM ComputedTable
    WHERE Column1 = @Column1 AND ID <= @Id

    RETURN @result
END

2. I create a table that use the above function to compute values for Column2:

CREATE TABLE ComputedTable
(
    ID int Primary Key Identity,
    Column1 int,
    Column2 AS (dbo.ufn_CountFK(ID, Column1))
)

3. Using the New Fluent Model Wizard I generated the mapping for the ComputedTable. The only modification I applied was to make the Column2 property read-only as it will be calculate by the database. I did that by changing .WithDataAccessKind(DataAccessKind.ReadWrite) to .WithDataAccessKind(DataAccessKind.ReadOnly).

4. When inserting/deleting the values of Column2 would be recalculated to contain the appropriate values.

As an alternative to computed columns, the custom logic that recalculates the values in the database could be performed by database triggers.

From your simplified scenario it seems you are trying to map an ordered collection or an ordered association between two classes. If that is the case you could check this article as it is supported out of the box by Telerik Data Access.

If you have any more question or need any further assistance, please let us know.

Regards,
Boyan
Telerik