How to use StringArray data type with SQL Server 2005/2008 ?

I noticed the "StringArray" data type in the LS designer.  How can I use this type with SQL Server 05/08 ?  I was hoping this would persist the string[] data into a single column with some kind of string separator, almost like storing it in a CSV format within that single column.  Is this possible?

I'm working on an ecommerce application and in my "OrderItems" table I'd like to save the custom options/attributes a customer has selected for a particular product.  This data doesn't need to be relational, strings are fine since that's all that's needed to fulfill the order.

The StringArray data type is used only to represent the MySQL "set" type.  It dates from before we had provider-defined types.  It cannot be used with SQL Server.  (Similar for Int32Array should you also be tempted by that.)  Sorry!

You could however do something like what you want with a user-defined type and a field converter.  See http://www.mindscapehq.com/blog/index.php/2010/08/22/ninja-data-type-mappings-in-lightspeed/ for details.  Note that some care may be required to ensure that the field gets marked as dirty when required -- for example, if you surface the data as an array, and modify an element of the array, that will NOT automatically call Entity.Set and LightSpeed will NOT know that the entity needs to be saved!

Awesome, I just created my own "StringList" FieldConverter by inheriting from your FieldConverter abstract base class.  It allows me to use an IList<string> datatype and persist it as a double-pipe || delimited string in a single database column.  Great stuff, thanks!

I just noticed your comment about having to possibly call the base Set() methods to make sure LS knows to persist/update my custom field.  I have attached the source code for my custom FieldConverter, does it look correct?  Or do I need to add something in there to call the base Set() method?  Thanks.

Your converter looks fine, but the Set rule means it won't mesh nicely with developer expectations.  Consider:

var e = _unitOfWork.FindById<MyEntity>(1);
e.Strings.Add("blah");
_unitOfWork.SaveChanges();

LightSpeed has no way of knowing that the list has changed and therefore doesn't know that the entity needs saving.  LightSpeed only thinks that an entity needs saving if a field has been modified via the Entity.Set method.  Your code will work only if the developer writes something like:

e.Strings = AppendAsNewList(e.Strings, "blah");  // Append creates a new list

That is, the developer has to remember to set e.Strings, and moreover set it to a different object than it was before.

The easiest fix for you to implement is to have your FieldConverter deal not in List<string> but in ReadOnlyCollection<string>.  That will prevent developers from making the e.Strings.Add() mistake and force them to write the e.Strings = Append(...) version.  It is not as nice to work with as the List<string> API but it has the benefit of working without being too difficult to implement!  And of course you can always add helper methods to make it nicer.

There is no easy solution that allows you to use mutable reference types (like List<T>) with LightSpeed.  It can be done with enough hackery but it is always going to be a bit kludgey.

Thanks very much, you guys rock at answering questions!  It's really helping a LightSpeed n00b like me!