Linq to load objects

Mar 5, 2011 at 8:12 PM

I am trying to load data from my sterling db using linq and it is taking an unbelievably long time to load. In my db I have thousands of Verse objects and I am using this query to load them:

var verses = App.Database.Query<Verse, int>().Where(v => v.LazyValue.Value.BookId == Convert.ToInt32(bookid) && v.LazyValue.Value.ChapterNum == Convert.ToInt32(chapter)).ToList();

Is there something wrong with my query or is it always going to be this slow?

Mar 5, 2011 at 8:24 PM
Are you using the phone or the desktop version? That query will always take time, not because of Sterling, but because of the speed of isolated storage (slow on desktop, slower on phone). Anytime you access LazyValue.Value you are deserializing the full object.

This is why Sterling provides indexes. You are querying the book id and the chapter number, so why not use those as an index? That way you can query quickly and only load the full item when you need it. Define the index like this:

     v=>Tuple.Create(v.BookId, v.ChapterNumber)); 

Then, change your query above to be (notice I convert the chapters before hand so I'm not converting them every row):

var bookIdNum = Convert.ToInt32(bookid);
var chapterNum = Convert.ToInt32(chapter); 

var verses = App.Database.Query<Verse, int, int, int>("VerseBookChapterIndex").Where(v => v.Index1 == bookIdNum && v.Index2 == chapterNum).Select(v=>v.LazyValue.Value).ToList();

Now it will find immediately the book and chapter you are looking for, then only load the matching values.

Mar 5, 2011 at 8:27 PM

Good ideas. I'll give it a try. Thanks

Mar 5, 2011 at 8:36 PM

That seemed to do the trick. Hot damn that's cool.