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

Posted on 2008-11-18
Medium Priority
Last Modified: 2013-11-11
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

Question by:talker2004
LVL 41

Accepted Solution

graye earned 1500 total points
ID: 23007759
...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.
LVL 14

Expert Comment

ID: 23020342
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?


Author Comment

ID: 23175718
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.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question