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

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

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.

Fernando SotoRetiredCommented:
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 SotoRetiredCommented:
Did this help?
talker2004Author Commented:
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.

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Fernando SotoRetiredCommented:
Hi talker;

Have you been able to get back to this?


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
talker2004Author Commented:
sorry for the delay, i been real busy

It worked great.

Thanks so much for all your help.
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
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.