Working on IQueryable Extension for SimpleSavant

Nov 9, 2010 at 6:22 PM
Edited Nov 9, 2010 at 6:26 PM

I've been using SimpleSavant for some personal projects, but recently I'm managing to get it into some of my work time! It's brilliant.

I hate SQL, so I haven't enjoyed using strings to do selects:

var returnedData = savant.Select<MyClass>("select * from MyClass where variable1 = '34' and variable2 < '89' order by variable3 desc");

I borrowed bits of code from the LinqtoSimpleDB provider here on Codeplex and have been integrating it into my own extension method. LinqtoSimpleDB uses a dictionary of attributes, whereas I wanted to be able to query using lambda expressions. Its not completely finished or tested but now I can do the following:

 


 

var returnedData = savant.AsQueryable<MyClass>()
                         .Where(x => x.variable1 == 34
                                  && x.variable2 < 89)
                         .OrderByDescending(o => o.variable3)
                         .ToList();

 


 

This processes the expression tree, formats a query string and passes it to SimpleSavant.Select. Linq should work too, but I'm not big on linq so haven't tested it yet!

It pays attention to all the SimpleSavant attributes, and uses the SimpleSavant Formatters so that number comparisons work correctly. Today I added OrderBy and OrderByDescending and when I get some time I hope to add Take, Count, StartsWith and EndsWith. There are some functions (like Join!) that don't exist with SimpleDB so I just throw an exception if I encounter them! I still need to check that I'm escaping all the attribute names and values when required.

My code defiantly needs a seconds pair of eyes. Maybe Codeplex needs a SimpleSavant contrib project.

Coordinator
Nov 9, 2010 at 6:48 PM
Edited Nov 9, 2010 at 6:49 PM

That sounds very cool and useful! I've been interested in porting or having someone port the LinqToSimpleDb code to Savant for quite a while.

I contacted the coordinator of LinqToSimpleDb but he didn't have the time or interest. I'm more interested in getting Linq support into the core Savant project than creating a contrib project. So please feel free to upload a patch (simple Zip file or SVN patch preferred) under the source code area if you're inclined to do so.

I would try to get back to you with my thoughts on the architecture and integration possibilities pretty quickly (a few days or a week at most).

 

Nov 9, 2010 at 7:34 PM

I'm pretty busy myself for the next week or so so there's no rush.

The LinqToSimpleDB code is nearly 3 years old, and ordering wasn't even possible with SimpleDB back then. IQueryable providers are massively complex so I'd have had no idea where to start without that existing code! I'll take a closer look myself at the Savant source. If my code works as an extension method, I can't imagine it'll be a huge amount of work to merge into the trunk.

It needs a good set of tests to check all the escaping and other things I no doubt haven't even considered yet. LinqToSimpleDB has a "GetQueryString" method which I'm using to pass to Savant, but its also useful for testing!

Nov 14, 2010 at 2:03 AM

Still haven't found any time to work on this. I did notice that LinqToSimpleDB uses the Microsoft Public Licence. Do you know how compatible it is with the LGPL?

Coordinator
Nov 14, 2010 at 2:53 AM

I'm not sure how compatible they are. Ms-PL allows derivative works and source code redistribution but says that the Ms-PL license must accompany distributions. You could argue that the LGPL is close enough to Ms-PL to replace it on derivative works, but it's probably best to just contact the LinqToSimpleDB author and ask what he expects.

Nov 14, 2010 at 11:39 AM

Don't you just love open source licensing! 

From my brief skim over the two the are similar. The key issue is that Ms-PL requires all released source code derivatives be under Ms-PL. Another possible stumbling block is that LinqToSimpleDB takes code from other Ms-PL works so simply asking Justin Etheredge might not be enough.

/**
 * Copyright (c) 2008 Justin Etheredge http://www.codethinked.com
 *
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Microsoft Public License (Ms-PL)
 * which accompanies this distribution, and is available at
 * http://www.opensource.org/licenses/ms-pl.html
 * If redistributing this code, this entire header must remain intact.
 * 
 * Some of the code in this application has been taken 
 * from the following sources (all of which are licensed under
 * the Ms-PL):
 * Matt Warren's Blog: http://blogs.msdn.com/mattwar/
 * MSDN: http://msdn2.microsoft.com/en-us/library/bb546158.aspx
 */
You could always have a separate Coditate.Savant.Linq assembly licensed under Ms-PL...

Coordinator
Nov 14, 2010 at 1:08 PM

I think it would be OK to just add the Ms-PL license and attribution to the header of the Linq source files.

Nov 22, 2010 at 10:16 PM

Finally my work load is a bit lighter and I've started looking into this. I've been looking through some other IQueryable implementations and noticed cleverer ways to build the query than in LinqToSimpleDB. I'll probably end up combining ideas from a few different implementations and tutorials that I've read. So far I've only got onto writing test cases. 

 

Coordinator
Nov 22, 2010 at 11:17 PM

I'm looking forward to seeing what you come up with!

Nov 23, 2010 at 11:31 PM

Right most of it's there. I'll upload a patch tomorrow once I've got all the xml commenting and I've tested it with some actual data!

I'm using VS2010 so all the project files have been modified with the upgrade. I'll leave these out of the patch...

Where, OrderBy, OrderByDescending, Intersect, Take and First are working.

Count needs a bit more work. Maybe I'll get it done tomorrow.

A few checks need adding to cover SimpleDB limitations:

  • Throw if OderBy/Descending has been attempted and you haven't used 'Where' on the property
  • Throw if 'Where' has been attempted on more than 5 properties
  • Throw if attempt is made to compare two properties Where(x => x.IntValue > x.IntValue2)
  • Throw if invalid methods are not attempted (Join etc)

I've no idea how it will work with:

  • Return counts over the SimpleDB 200 maximum (does Savant automatically handle this?)
  • Savant Async stuff

Further possibilites:

  • String.StartsWith/EndsWith/Contains using SimpleDB 'like' query
  • 'Select' specific attributes only. Would require deeper Savant integration (or better understanding from me!)
Coordinator
Nov 24, 2010 at 2:45 AM

Sounds like a great start! It's tough to give much meaningful feedback before seeing the code, but here are some thoughts based on your comments:

  • Throw if OderBy/Descending has been attempted and you haven't used 'Where' on the property

Not sure I understand why this is necessary.

  • Throw if 'Where' has been attempted on more than 5 properties

SimpleDB allows 20 predicates per query expression. Where does the 5 come into play?

  • Return counts over the SimpleDB 200 maximum (does Savant automatically handle this?)

By default Savant issues repeated requests to SimpleDB until it has retrieved all available results (see Advanced Select Queries). Ideally, the Linq provider would lazy-load successive batches of results from SimpleDB as the collection is enumerated (obviously the entire available set would need to be retrieved on conversion ToList()). Lazy loading may be a bit too ambitious for the initial implementation, but it would be nice to get there at some point.

  • 'Select' specific attributes only. Would require deeper Savant integration (or better understanding from me!)

Ideally, the Linq implementation should use the Savant.SelectAttributes() methods. Then if the Linq query used IQueryable.Select you could (I think) formulate your SimpleDB select to only retrieve the selected attribute. It's trivial to convert between data classes and the PropertyValues collection return from Savant.SelectAttributes(). I can provide an example if it would help.

Nov 24, 2010 at 10:14 AM
I read some of this from the SimpleDB Query 101

SimpleDB allows 20 predicates per query expression. Where does the 5 come into play?

I must have read "Users can specify up to 5 comparisons within a single predicate." and got the wrong end of the stick!

 

Throw if OderBy/Descending has been attempted and you haven't used 'Where' on the property

Not sure I understand why this is necessary.

"The sort attribute must be present in at least one of the predicates of the expression."

"select * from mydomain order by Year asc" - Invalid because Year is not constrained by a predicate in the where clause. - 400 Error

 

Agreed about the final implementation using lazy loading.

Select returns anonymous types which I'll have to read up about. I'm sure I've seen some IQueryable examples out there which cover this.

Coordinator
Nov 24, 2010 at 12:30 PM

Right, I forgot about the where being necessary when sorting. When I don't need a real where clause I just stick in a null check on the order by attribute to meet this condition:

"select * from mydomain where Year is not null order by Year"

That would be a nice workaround in the Linq provider if not too difficult to implement.

 

 

Coordinator
Feb 14, 2011 at 6:00 PM

@tim124: I'm assuming at this point that you will not be contributing your Linq code (for whatever reason) so I expect to begin working on my own Linq implementation in the near future. If that changes, please let me know as soon as possible.

Feb 14, 2011 at 6:05 PM

Sorry Ashley, I got moved onto another project and didn't get to devote any more time to it. I'll certainly upload a patch with what I've got as I'm sure it will at least be a good starting point.

Coordinator
Feb 14, 2011 at 6:12 PM

Great! Glad to hear it and thanks for responding quickly. I'll look forward to seeing what you've put together so far.

Feb 14, 2011 at 6:17 PM

Uploaded. Its mainly a port of the LinqToSimpleDb code with some additions. You know SimpleSavant a lot better than me so I'm sure you'll have better ideas for integration. I'll keep an eye on the svn as I'll certainly be interested in your implementation!

Feb 14, 2011 at 6:22 PM

My patch is missing a small bit of code from SimpleSavant.cs. Not really part of the implementation, but how I was integrating it for the time being.

 

        /// <summary>
        /// IQueriable Implimentation
        /// </summary>
        /// <typeparam name="T">The item type</typeparam>
        /// <returns></returns>
        public SavantQuery<T> AsQueryable<T>()
        {
            return new SavantQuery<T>(this);
        }

 

 

Coordinator
Feb 14, 2011 at 6:38 PM

Thanks! I'll check it out and definitely give you credit when integrated. If you have working unit tests for any of this code those would be helpful too.

Feb 14, 2011 at 6:50 PM

I'll see what I've got at work. There were only a handful and they used the GetQueryText() method to check a few examples that I found in the SimpleDB Query 101.

Ah don't worry about credit, I spent a few days on it and then never got round to submitting a patch, whereas you have donated this to the community and keep it well maintained and feature rich!

Apr 13, 2011 at 6:11 PM

Ashley, did you find any time to look at this?

Coordinator
Apr 13, 2011 at 7:35 PM

Hi Tim,

I did briefly, but I've been swamped with other work for the past couple of months. I'm hoping to get out a release with some other queued up changes in the next couple of weeks and then turn my attention to the Linq interface for the next release after that (which will hopefully come after another few weeks).

- Ashley

Sep 5, 2011 at 5:34 PM

Hey Ashley,

I just found some resources that are supposed to make it simpler to write your own IQueryable interface.

http://relinq.codeplex.com/

This library simplifies the expression tree and leaves you to override just the query methods that your database/datastore supports.

Having a search around, it appears its also used by ScrappyDB (an ORM for SimpleDB) http://scrappydb.codeplex.com. This code is likely to be more up-to-date than the ageing LinqtoSimpleDB library, and might port with much less work.

I'm away on holiday from tomorrow, but when I get back in a week or so, I'll be sure to check it all out more thoroughly! 

Best Regards,

Tim