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

talker2004 used Ask the Experts™
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 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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fernando SotoRetired
Distinguished Expert 2017

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

Fernando SotoRetired
Distinguished Expert 2017

Did this help?


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.

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Distinguished Expert 2017
Hi talker;

Have you been able to get back to this?



sorry for the delay, i been real busy

It worked great.

Thanks so much for all your help.
Fernando SotoRetired
Distinguished Expert 2017

Not a problem, glad I was able to help.  ;=)

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