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 =
var table =
table.Column1 = value;
table.Column2 = context.ComputedTables.Count(x => x.Column1 == value);
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
Column1 = @Column1
ID <= @Id
2. I create a table that use the above function to compute values for Column2
3. Using the New Fluent Model Wizard
I generated the mapping for the ComputedTable
. The only modification I applied was to make the Column2
as it will be calculate by the database. I did that by changing .WithDataAccessKind(DataAccessKind.ReadWrite)
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.
OpenAccess ORM is now Telerik Data Access
. For more information on the new names, please, check out the Telerik Product Map