Multiple indexes on a table and other questions

Jan 3, 2011 at 9:43 PM

1)Do you have examples of creating multiple indexes on a table similar to a relational table. I am trying to take a 5 or 6 tables in my relational schema and port it over to windows phone for an app and decided to try Sterling out.

The entities are mapped to the table and the columns to properties. Each of the entities/tables has multiples indexes to support search by different keys.

2) How can i efficiently load all the rows in the table without iterating over them one by one like below..

      List<ItemGroup> itemGroups = new List<ItemGroup>();

             foreach (var itemGroup in database.Query<ItemGroupint>())
             {
                    itemGroups.Add(itemGroup.LazyValue.Value);
             }

             return itemGroups; 

3) Some of the tables have auto gen id and entities have been generated based on that, would it be possible to get some kind of auto gen iof the ids. I am planning to roll my own auto gen id tracking right now. If there are plans to implement, please let me know

4) Some of the queries would need 'join' to other entities to pull data for the viewmodel like shown below. Can this be done and if yes, would you be able to direct me to some samples? If I am using the wrong tool to do this kind of thing, please let me know right now

  var query = from i in store.Items
                        join ig in store.ItemGroups on i.ItemGroupId equals ig.ItemGroupId
                        join ild in store.ItemLocationDetails on i.ItemId equals ild.ItemId
                        join l in store.Locations on ild.LocationId equals l.LocationId
                        join lg in store.LocationGroups on l.LocationGroupId equals lg.LocationGroupId
                        where l.LocationId == locationId
                        select new ItemLocation()
                        {
                            Item = i,
                            ItemLocationDetail = ild,
                            ItemGroup = ig,
                            Location = l,
                            LocationGroup = lg
                        };
Coordinator
Jan 3, 2011 at 10:22 PM

1. The reference application has what you are looking for, including the definition of multiple indexes. You simply chain a set of .WithIndex commands.

2. Your best bet is to iterate. Keep in mind the speed from Sterling comes from the fact that the keys/indexes are in memory, not the entities. There is no "in memory" list of entities, those must be de-serialized. So, when you iterate, you are de-serializing - the lazy value is in essence the trigger to de-serialize from disk. Keep in mind because of the lazy value, once it is de-serialized the first time, it is now in memory.

3. There are plans down the road but not currently - GUIDs are by far the easiest, but if you have id for example then you can create a class that queries the max id, holds that in a static place and then provides the service for you.

4. Again, the reference documentation and help show examples of joining between different tables.

Let me know how it goes for you and how I can continue to be of assistance!

Jan 4, 2011 at 3:06 AM

Thanks for the reply. I will let you know once i am done fully wiring up the queries.  I am going to join on indexes and avoid the full load of the entities for a few queries.  I think that should work.  It would be real cool if i can pull this app off.

I am following a repository pattern and have abstracted out a repository for sterling database access following the documentation and code samples.  I looked at the documentation.  Is there more details on how you serialize the entities and indexes, tables etc.. More of a high level  design/arch diagram.