Complex Linq Queries  using linq to dataset C#

Posted on 2008-11-18
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 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

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

    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?

    LVL 7

    Author Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Email Body 4 33
    c# string 6 28
    dispose of a DataTable created in another class 4 15
    Need help with a query. 10 27
    This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    This video discusses moving either the default database or any database to a new volume.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now