Complex Linq Queries using linq to dataset vb.net C#

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 ado.net 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

LVL 7
talker2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grayeCommented:
...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.
http://msdn.microsoft.com/en-us/vbasic/bb688088.aspx 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PockyMasterCommented:
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?

0
talker2004Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

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.