How to add an entry to a table and set the id?

A table in our MySql DB has as it's identifier a string (application-specific guid of sorts). When I want to make a new entry in this table using LightSpeed, I do the following:

SvcRequest newReq = new SvcRequest();
newReq.State = "not_started";
newReq.Options = options;
newReq.Service = service;
newReq.Id = myGuid;  <----- ERROR HERE

The problem is that the Id property is read-only. How can I change this? I didn't intend for LightSpeed to do this, as it is certainly supported in the table schema for the column mapped to the Id property to be set during an INSERT operation.

Thank you

Gary

In general LightSpeed adopts the philosophy that you should not be concerned about the Id.  If there is business-meaningful identifying information, then this should be represented as a surrogate key.  So the first thing to do is to ask yourself, "Do I really need to control the allocation of IDs rather than letting the framework take care of it?"

Of course, there are cases where the answer is "yes," e.g. if you need to exactly duplicate the contents of an existing database, if your identity type is not supported by LightSpeed (not an integer, a GUID or a GUID-format string), if you have an existing database which depends on a particular application ID allocation strategy, etc.

In this case, you can override the Entity.GeneratedId() method to supply your own ID.  Note that this is not equivalent to *setting* the ID, because the ID once set is immutable.  Rather, LightSpeed will call this method when it needs to allocate an ID.

Thus in your case you could implement the SvcRequest class as follows:

class SvcRequest {
  [Transient] private string _allocatedId;
  public string AllocatedId { set { _allocatedId = value; } }
  protected override object GeneratedId() {
    return _allocatedId;
  }
}

You can then set AllocatedId instead of Id, and when LightSpeed comes to call GeneratedId() (e.g. when saving changes) it will pick up your ID rather than allocating its own.

(See http://www.mindscape.co.nz/forums/Thread.aspx?PostID=2789 for the design reasons for using a GeneratedId() virtual method rather than making the Id settable.)

Hi Ivan

Scenario: I have one table that doesn't require inserts/updates etc...other than in the initial data load (done daily and the table is truncated each morning).  The data is sourced from a legacy system.

From a UI perspective, this table is only for searching.

I've added the following as you suggested

[

Transient] private int _overrideId;

public int OverrideId { get { return _overrideId; } set { _overrideId = value; } }

protected override object GeneratedId() { return  _overrideId; }

When i called the the entity to the UoW and call the UoW.SaveChanges()

I can see the GeneratedId gets called and see the internal id set to the id that i wanted.

However, i get the exception

Can not insert null into column Id for table "Vehicle"

The table itself has an Id field as a primary key but was not set as an identity

What is the LightSpeedContext.IdentityMethod?  Or, if you have set the Identity Method at the entity level, what is the entity identity method?

If the identity method is IdentityColumn, then LightSpeed will not send the ID to the database (because it expects the database to fill it in), which would result in the error you describe.  In this case, change the identity method to something else (it doesn't particularly matter what because it isn't called if you override GeneratedId).  Since you have other tables that presumably *do* need inserts, you probably want to do this at the Vehicle entity level, not the context level: to do this in the designer, select the Vehicle entity and set Identity Method, to do it in code add a TableAttribute and set TableAttribute.IdentityMethod.

If it's not IdentityColumn, could you create and post a small project that reproduces the problem for us?  You can attach a zip file via the Options tab.  Thanks!

Hi Emickey,

I'm guessing you're using the IdentityMethod.IdentityColumn in your solution?

Please try setting the identitymethod on your entities that require manual id's to something other than IdentityColumn as this will not sent the Id to the database as it expects the database will create the identity. Try setting it to IdentityMethod.Guid but just on the entities that require it (you can do this in the designer).

I hope that helps,

John-Daniel Trask

UPDATE: Looks like it was a race between myself and Ivan and Ivan won!