Quesiton about using a Join with Linq to dataset or C#

Posted on 2008-11-18
Last Modified: 2013-12-17
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 would do this join that would be great.
sql = "SELECT ServFile.ServProc, ServFile.ServDuration, " & _

                  "ServFile.ServGapDuration, ServFile.ServFinishDuration, Procedures.ProcDescr " & _

                  "FROM ServFile " & _

                  "LEFT JOIN Procedures ON ServFile.ServProc = Procedures.ProcKey " & _

                  "WHERE ServDes = " & Param1 & " AND ServProc = " & Param2 & "  Order by ServProc"

'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 joining the ' two tables located in my dataset to get 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 62

    Expert Comment

    by:Fernando Soto
    Hi talker2004;

    The following query should be almost there. If you have a SQL script to build a test db I would help to tweak it up or fix it.

      var joinedData = from t1 in DataTable1.AsEnumerable()
                       join t2 in DataTable2.AsEnumerable() on t1.Field<String>("ServProc") equals t2.Field<String>("ProcKey") into joinedResults
                       from jr in joinedResults.DefaultIfEmpty(new t2.NewRow())
                       where t1.Field<String>("ServDes") == Param1 && t1.Field<String>("ServProc") == Param2
                       orderby t1.Field<String>("ServProc")
                       select new

    Open in new window

    LVL 62

    Expert Comment

    by:Fernando Soto
    Did this help?
    LVL 7

    Author Comment

    Sorry, it looks great... have not had a chance to try it out since we are trying to get a build out the door.

    Anyway I think It's obvious you have earned the points for this thread... I would like to try it out so give me a few days and i will get back to you on this one.

    I am however having a slight issue with a question you answered for me a few days back. Not that it did not work because it did, it was just a slight change to the query that caused the issue. This one is more basic and much higher priority for me. I already gave you the points for that one so i created a new thread here.

    I believe this one should be real easy for you, i would appreciate if you could check it out.

    LVL 62

    Accepted Solution

    Hi talker;

    Have you been able to get back to this?

    LVL 7

    Author Closing Comment

    sorry for the delay, i been real busy

    It worked great.

    Thanks so much for all your help.
    LVL 62

    Expert Comment

    by:Fernando Soto
    Not a problem, glad I was able to help.  ;=)

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    733 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

    20 Experts available now in Live!

    Get 1:1 Help Now