asked on

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

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.

Fernando

  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
                       {
                           t1.Field<String>("ServProc"),
                           t1.Field<String>("ServDuration)",
                           t1.Field<String>("ServGapDuration"),
                           t1.Field<String>("ServFinishDuration"),
                           jr.Field<String>("ProcDescr")
                       };

Open in new window

Fernando Soto

Did this help?

talker2004

ASKER

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.

https://www.experts-exchange.com/questions/23924252/Linq-to-dataset-question-C-vb-net-LINQ-ASP-Net.html

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

Thanks
talker

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

talker2004

ASKER

sorry for the delay, i been real busy

It worked great.

Thanks so much for all your help.

Fernando Soto

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