Linq to Salesforce "SQL" provider

Question

So, I have this new project. My company uses the SalesForce.com cloud to store information about day-to-day operations. My job is to write a new application that will, among other things, more seamlessly integrate the CRUD operations of this data with existing in-house application functionality.

The heart of the Salesforce WSDL API is a set of "query()" web methods that take the query command as a string. The syntax of the query is SQL-ish, but not quite (they call it SOQL). I'm not a fan of "magic strings", so I'd like to use Linq in the codebase, and parse the IQueryable into the SOQL query I need in the wrapper for the service. It's certainly possible (L2E, L2Sql), but I'd like to know if there's a shortcut, 'cause if I say it'll take more than a day or two to roll my own, I'll be "encouraged" to find another method (most likely a method for each general-use query, which was the method used in older apps). If I succeed in making a general-purpose SOQL parser, we can use it in several other upcoming apps, and I'll be a hero. Even if I make a simple one that only supports certain query structures, it'll go a long way by allowing me to proceed with the current project in a Linq-y way, and I can expand on it in my free time.

Here are the options as I see it:

• Look harder for an existing Linq2SOQL provider (My Google-fu is failing me here, or else there simply isn't one; the only .NET wrapper only mentions Linq as a nice-to-have).
• Build an expression tree parser. It needs to support, at least, the Select and Where method calls, and needs to either parse lambdas or manipulate their method bodies to get the operations and projections needed. This seems to be a rather massive task, but like I said, it's certainly possible.
• Wrap the service in Linq2Sql or a similar existing Linq provider that will allow me to extract a close-enough query string, polish it up and pass it to the service. There must be dozens out there (though none that just drop in, AFAIK).
• Call Expression.ToString() (or Expression.DebugView), and manipulate that string to create the SOQL query. It'll be brittle, it'll be ugly (behind the scenes), and it will support only what I'm explicitly looking for, but it will provide a rudimentary translation that will allow me to move on.

What do you guys think? Is building a Linq parser more than a two-day task for one guy? Would a bodged-up solution involving an existing Linq provider possibly do it? Would it be terrible to chop up the expression string and construct my query that way?

EDIT: Thanks to Kirk for the grounding. I took some more looks at what I'd be required to do for even a basic SOQL parser, and it's just beyond the scope of me getting working application code written on any feasible schedule. For instance, I have to build a select list from either the Select() method lambda or a default one from all known columns from my WSDL object, a task I hadn't even thought of (I was focusing more on the Where parsing). I'm sure there are many other "unknown unknowns" that could turn this into a pretty big deal. I found several links which shows the basics of writing a Linq provider, and though they all try to make it simple, it's just not going to be feasible time-wise right now. I'll structure my repository, for now, using named methods that encapsulate named queries (a constant class of formattable query strings should reduce the amount of head-scratching in maintenance). Not perfect, but far more feasible. If and when a Linq2SOQL provider gets off the ground, either in-house or open-source, we can refactor.

For others looking for Linq provider references, here are those helpful links I found:

• Building a Linq Provider
• Walkthrough: Creating an IQueryable LINQ Provider
• Linq: Building an IQueryable provider series - in 17 parts! <-- this one, though long and involved, has a lot of real in-depth explanations and is good for "first-timers".

Answer 1

Let's take them one at a time:

Look harder for an existing Linq2SOQL provider (My Google-fu is failing me here, or else there simply isn't one; the only .NET wrapper only mentions Linq as a nice-to-have).

Yeah, I doubt one exists already, but hopefully you can find one.

Build an expression tree parser. It needs to support, at least, the Select and Where method calls, and needs to either parse lambdas or manipulate their method bodies to get the operations and projections needed. This seems to be a rather massive task, but like I said, it's certainly possible.

This is absolutely the way to go if you really are serious about this in the long-run.

Wrap the service in Linq2Sql or a similar existing Linq provider that will allow me to extract a close-enough query string, polish it up and pass it to the service. There must be dozens out there (though none that just drop in, AFAIK).

What do you mean by "drop in"? You can easily get the SQL straight from L2S.

Call Expression.ToString() (or Expression.DebugView), and manipulate that string to create the SOQL query. It'll be brittle, it'll be ugly (behind the scenes), and it will support only what I'm explicitly looking for, but it will provide a rudimentary translation that will allow me to move on.

I would strongly discourage you from this approach, as, at minimum, it will be at least as difficult as parsing the expression trees properly. If anything, in order to use this, you'd have to first put the parsed strings into a proper object model -- i.e. the existing expression trees you're starting with.

---

Really, you should think about building a query provider and doing this right. I think two days is a bit of a stretch though to get something working in even a primitive sense, though it might be possible. IMO, you should research it some at home and toy around with it so you have some familiarity with the basic pieces and parts. Then you might barely be able to get some usable queries going after two days.

Honestly though, fully implementing this kind of project is really in the realm of several weeks, if not months -- not days.

If that's too much work, you might consider option 3. I'm no expert on SOQL, so no idea what kind of work would be involved transforming ordinary SQL queries into SOQL queries. If you think it's rather algorithmic and reliable, that might be the way to go.