Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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

Question by:talker2004
  • 4
  • 2
LVL 64

Expert Comment

by:Fernando Soto
ID: 22988407
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 64

Expert Comment

by:Fernando Soto
ID: 23007260
Did this help?

Author Comment

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 64

Accepted Solution

Fernando Soto earned 2000 total points
ID: 23141418
Hi talker;

Have you been able to get back to this?


Author Closing Comment

ID: 31517968
sorry for the delay, i been real busy

It worked great.

Thanks so much for all your help.
LVL 64

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

580 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