0

I'm new to Entity Framework. I have multiple tables that have a 'SortOrder' column. This column declares the ordering in which the rows should appear when rendered.

Whenever an insert happens through my application I would like the DBcontext to 'detect' that a sortorder column exists, and have it automatically fill/overwrite this value with the MAX(sortorder) in this specific table + 1.

What is the best way of achieving this? I have experimented with using database triggers, but apparently that does not work well with EF.

Apart from that I can only think of some nasty reflection, or coding this logic in every insert I do in the application (or at least once for every tabel).

thanks,

Jeremy
  • 712
  • 1
  • 6
  • 24
  • I don't think what you want is easily or by default supported by EF. I would do the following: the SortOrder column will have default value of 9999 or something. Each insert will have this value. The sorting will be done by two columns, SortOrder and Id , if it is autoincremented. If not then I would add DateCreated, and the will sort by SortOrder and DateCreated. – Alex Nov 27 '14 at 08:45

2 Answers2

0

Try to add attribute to your SorderOrder columns

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

read Entity Framework auto incrementing field, that isn't the Id

Community
  • 1
  • 1
Artiom
  • 7,694
  • 3
  • 38
  • 45
  • Doesn't this have the consequence that I cannot update the values in the column? Will a unique constraint be enforced on the database? – Jeremy Nov 27 '14 at 13:21
  • This causes problems: "Cannot insert explicit value for identity column in table 'Engines' when IDENTITY_INSERT is set to OFF."}". After reading up, it seems that I would have to manage this IDENTITY_INSERT parameter myself for every table. This is not what I want to be doing – Jeremy Nov 27 '14 at 13:59
0

Have all instances that have the SortOrder column implement a common interface (IHasSortOrder) which exposes a SortOrder property:

public interface IHasSortOrder { int SortOrder { get; set; } }

Add an event handler to the ObjectContext.SavingChanges event. You get an instance of the ObjectContext by calling IObjectContextAdapter.ObjectContext (DbContext implements IObjectContextAdapter):

var octx = (this as IObjectContextAdapter).ObjectContext;
octx.SavingChanges += this.OnSavingChanges;

In this event handler, you have access to all objects pending insertion:

ctx.ChangeTracker.Entries().Where(x => x.State == EntityState.Added).Select(x => x.Entity).OfType<IHasSortOrder>()

Iterate through this collection and set SortOrder the way you want.

Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74
  • I was trying something very similar myself. I got stuck on the last sentence of your answer. "The way I want the sortorder" is the maximum value in the corresponding table. However, unless I'm mistaken (I'm a novice) I cannot easily find the corresponding table for every item in Entries(), since the objects contained in Entries are very "abstract". Could you elaborate? – Jeremy Nov 27 '14 at 14:24
  • You are right, I misunderstood... you have to go to the DB to get those values. – Ricardo Peres Nov 27 '14 at 15:04