Complex Linq Queries  using linq to dataset C#

talker2004 used Ask the Experts™
Question:  Ok, we are having issues with an algorithm that is becoming very database intensive. Our solution is that we are going to pre cache all the data into an dataset and query the dataset via linq.

We are going to cache all the data we need into the dataset with the exact same schema as it was in the database. Theoretically as long as we translate our linq queries to work exactly like our sql queries then the algorithm should behave exactly the same way. We feel this is going to minimize the rewrite effort.

Anyway here is my issue, we have a few complex queries in the algorithm and i am not sure how we would translate them to Linq. As a start if someone could give me an example on how i could simulate this same query via linq that would be great.
sql = "SELECT ProcKey, ProcDescr, ProcGap, ProcDuration, " & _
                "ProcGapDuration, ProcFinishDuration, ProcResource FROM " & _
                "Procedures WHERE ProcKey IN " & _
                "(Select ServProc From ServFile WHERE ServAllowBookOnline = True) " & _
                "AND ProcAllowBookOnline = -1 Order by ProcDescr"
'Lets assume that i have a dataset named dsServices and i fill it with the 'following two queries. Select * From ServFile, Select * From Procedures
' Now what i want to do is query the dataset with linq so that i am getting the same results as my original query.
' then i would like to convert it back to a datatable using the CopyToDataTable ' method 
' Now that i have this table it will fit right back into the flow of my
' algorithm with minimal changes.
Examples would be appreciated.
thanks in advance

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
...this is not necessarily a direct answer to your question, but you really should check out this magnificent collection of LINQ examples on the MSDN site...   I found it absolutely indispensable while I was doing similar conversion from "real" SQL to LINQ. 
try rewriting your query to:

SELECT ProcKey, ProcDescr, ProcGap, ProcDuration,
               ProcGapDuration, ProcFinishDuration, ProcResource FROM
                Procedures PR INNER JOIN
                (Select ServProc From ServFile WHERE ServAllowBookOnline = True)  SF
               ON SF.ServProc = PR.ProcKey
               WHERE ProcAllowBookOnline = -1 Order by ProcDescr

Why would you think getting your complete database tables over to code would be faster than doing it in your database?


This is quite a complex algorithm which is highly database intensive. My goal was to try to query cached data in memory in order to accomplish speed improvements. We have been able to achieve this in other parts of the algorithm and it was successful. This seems to be the most complex sql to linq conversion in our system. Remember we want to query using linq to dataset and not linq to sql.

It is much faster, none of the comments here really came close to the correct answer. Otherwise i would be happy to give out points.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial