Linq To SharePoint...within a SQL-CLR Stored Proc...

I'd like to see sample code and get learnings from anyone who has experience with using Linq to SQL from within a SQL-CLR stored procedure.

The end-goal is to have stored procedures and views on my SQL 2005 box that can query SharePoint 2010 lists effortlessly.

This kind of solution would get me:

o  Datastructure agnosticism -- the linq syntax could be set up to select * from the list, so that any column additions or deletions inside SharePoint would not affect my sp's or views.

o  No dealing with the weird table structures in SharePoint's SQL back-end that house its list data.  No having to use ; know GUIDs or whatever IDs to access the list data.

o  Reusable -- could pass in the name of my list to the SQL-CLR procedure.  No hard-coding list names or column names.

o  No third-party tools.

I see how Linq to SharePoint works.  There are examples all over the web.  And I know SQL CLR.  But I do *not* see anyone doing Linq to SharePoint within SQL-CLR...which seems to me to be the only way to achieve all these goals.

Thanks for sharing your experience.
Who is Participating?
Kevin CrossChief Technology OfficerCommented:
But you are doing this on the server that is part of the SharePoint system. It is already there. i.e., you already coupled to that. That is the proper place to create an interface to the outside world that allows loose coupling elsewhere. Otherwise, all the remote systems doing programming have to know the sharepoint structure and come up with their own abstraction layer. Think of it in code. If you are writing a web app and a client app. Using CLR on remote SQL Server is like using LINQ in both applications. Yes, it is abstraction, but you now have to maintain this twice. Whereas if you have a common layer that both apps communicate with then you benefit. That is the point here. Don't try to get SQL CLR to do something that does not meet good practice anyway. Go to the source. A SharePoint database by definition HAS to know about the SharePoint database structure. If I have to write any code that is specific to that structure, I would much rather do that there. Make the public interface to that say a stored procedure that says give me row for 'John Smith'. The procedure could rationalize that 'John Smith' is '8203F6B3-0CE0-4468-B924-1AACE148DB63' and pass that in remainder of code. The caller will only know the column names passed back. So you add columns, you have the power to show or not show that column and what alias it is shown as.

Anyway, as I said I must be missing what you are after.

You can use the 'Request Attention' and see if the Moderators can bring more Experts in as maybe they will read something differently and offer "fresh" ideas.
Kevin CrossChief Technology OfficerCommented:
Very interesting. Yes, you likely won't see this as most folks probably create a stored procedure on the SharePoint database server side and then you can just call the stored procedure there. No need to encapsulate it in LINQ as the system hosting SharePoint by definition knows its structure. The remote systems now are even more loosely coupled as they do not even have any code connecting it to the SharePoint server specifically in its CLR and can point to any SQL server with a stored procedure of the same name.

Beyond that point, I did not believe this is supported which is probably the bigger reason you have not seen it. In VS 2010, I can see reference for System.Xml.Linq that can be added; no Linq-to-Sql, so I got curious and went checking for confirmation.

bamapieAuthor Commented:
Thanks for this helpful info.  I'm a little lost on this:

>most folks probably create a stored procedure on the SharePoint
>database server side and then you can just call the stored procedure there
>No need to encapsulate it in LINQ as the system hosting SharePoint by definition knows its structure.

Do you mean that somehow you can write an stored proc on the SharePoint DB that does not have to know that "Address" is stored in field "VarChar17"?  Is the SharePoint database structured that differently now, that I wouldn't have to know the underlying structure?  I'm just not tracking you on this point at all, sorry.

Sorry, also, for it being so incredibly long since I got back to you on your reply.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kevin CrossChief Technology OfficerCommented:
Not quite what I meant. Sorry I phrased it that way as I re-read my comment and I see what you are asking. You are asking about encapsulating SharePoint on a remote system so that it is loosely coupled with SharePoint's underlying structure. My point is WHY would the remote system program this. Why not go the SharePoint database where the structure is known and program the encapsulating procedure there. In other words, the SharePoint DBA / Developer has to know the quirky structure, but once the interface is built there -- in theory, no one else does.

My point was more that instead of trying to create a LINQ to Sharepoint in a remote SQL Server CLR that you could simple create a procedure on the SharePoint SQL Server itself and call to it from the remote SQL instances.

Does that make more sense?

Maybe I am unclear on what you wanted to do in the first place.

bamapieAuthor Commented:
The problem is, sure, you can go write a stored procedure that says SELECT VARCHAR17, VARCHAR22, FROM X WHERE GUID = 'OMGOMGOMGOMG...', sure, but it's that first bulleted point in my question that cuts the knees out of this approach:

"any column additions or deletions inside SharePoint would not affect my sp's or views"

It's so nice to simply not have to deal with those column names.  To me, having to have a stored procedure that is this linked to your list's bizarre under-structure is the worst kind of coupling.  It's like some kind of binary compatibility on my database structure.

But hey, this has been enlightening.  I think there's still a use for what I'm after.  I think there's a way to use Linq to SharePoint in this way that removes the inelegance of having to code against the SharePoint List database structures.

bamapieAuthor Commented:
Close enough.  I'm looking ahead a couple of months anyway.  I'll burn this bridge when I get there.

Believe me, I'm not crazy about the idea of putting Linq to SharePoint inside CLR.  But to me, the point where my "cludge" indicator burns most brightly, is when I think about writing that stored procedure...then one of my SharePoint *users* with enough permissions mojo decides to remove a column or two and leaves my stored proc hitting columns that either don't exist (I doubt that, actually) or (more likely) are empty.

Linq to SharePoint would (should) be able to enumerate the columns in a list.  It would know that those columns are gonzo now.

That's really what I'm after, in a nutshell, if it's attainable.

But listen, your answers have been very valuable, and they actually give me some relief, in that I now could actually write a stored proc that is as bound to the SP data structure as you describe, and not go home and hate myself and start cutting or something.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.