Solved

join linq to sql with an array

Posted on 2011-02-25
8
719 Views
Last Modified: 2013-11-11
Ok just getting started with Linq and I've stumbled on an issue.

I've got a Linq query that is coming from a SQL DB that I have read only access to.

 
RapCourseRosters rd = new RapCourseRosters();
            var query =
                from classes in rd.Events_and_Classes 
                join courses in rd.Courses on classes.Course_ID equals courses.Course_ID
                where classes.End_Date.CompareTo(startyyyymmdd) >= 0
                && classes.End_Date.CompareTo(endyyyymmdd) <= 0
                && courses.Course_Type == "ACC"
                orderby classes.End_Date.ToString()
              select new { 
                  End_Date = classes.End_Date,
                  End_Date_Norm = classes.End_Date.Substring(4,2) + "/" + classes.End_Date.Substring(6,classes.End_Date.Length) + "/" + classes.End_Date.Substring(0,4),
                  Start_Date = classes.Start_Date, 
                  Event_Name = classes.Event_Name, 
                  Event_ID = classes.Event_ID,
                  Course_ID = courses.Course_ID
              };

Open in new window


I want to join this data to data on EventID to data  that I have in a separate .Csv file lets say:

csvEventID            |       csvLastAccessed
--------------------------------------------------------
CE-1212-880         |       2/24/2011
CE-1212-881         |       2/21/2011
CE-1212-887         |       2/24/2009
CE-1212-771         |       7/02/2010

I can do this via temporary recordsets but as a learning exercise I'm trying to do it in pure Linq. Is there an elegant way to join them using Linq?

Thanks

Michael




0
Comment
Question by:mcunn
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34982373
Hmmm...   Interesting. Should be a simple enough task, though  = )

Let's try this (Note: there is no error checking):
var joined = from line in System.IO.File.ReadAllLines("C:\\path\\to\\file.txt")
             let parts = line.Split(new char[] { ',' })[0]
             join rcr in rd
             on rcr.Event_ID equals parts[0]
             select new { End_Date = rcr.End_Date,
                          End_Date_Norm = rcr.End_Date_Norm,
                          Start_Date = rcr.Start_Date,
                          Event_Name = rcr.Event_Name,
                          Event_ID = rcr.Event_ID,
                          Course_ID = rcr.Course_ID,
                          Last_Accessed = parts[1]
             };

Open in new window

0
 

Author Comment

by:mcunn
ID: 34982731
Kaufmed,

Thanks for the code I'm trying to understand what you are doing in this block

 
join rcr in rd
on rcr.Event_ID equals parts[0]

Open in new window


Im getting an error on the join "RCR is not in scope on left side of  join"

Thanks

Michael

0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 200 total points
ID: 34982870
Hi mcunn;

Modifying Kaufmed solution, try it like this.

Fernando
var joined = from line in System.IO.File.ReadAllLines("C:\\path\\to\\file.txt")
             let parts = line.Split(new char[] { ',' })[0]
             join rcr in query
             on parts[0] equals rcr.Event_ID 
             select new { End_Date = rcr.End_Date,
                          End_Date_Norm = rcr.End_Date_Norm,
                          Start_Date = rcr.Start_Date,
                          Event_Name = rcr.Event_Name,
                          Event_ID = rcr.Event_ID,
                          Course_ID = rcr.Course_ID,
                          Last_Accessed = parts[1]
             };

Open in new window

0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 300 total points
ID: 34982925
Thx FernandoSoto! I admit the code was untested  = )


As far as the intent: I took your requirement literally and read each line of the file into an (implicit) array. At that point, however, the text is still just a string, so it has to be split on the comma (since you said CSV). I'm holding the split in a temporary local (to the Linq query) variable called "parts". Given your description above, the first index of parts should be the "csvEventID"; the second index should be the "csvLastAccessed". The query is joining the Event_ID from "rd" to the "csvEventID" of the array (index 0).
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Closing Comment

by:mcunn
ID: 34983169
Thanks guys that's exactly what I was looking for.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34983190
NP. Glad to help  : )
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34983248
Hmmm....  you've probably already accounted for this, but I can't see why I originally included the [0] at the end of the split. That, from what I can see, would set parts to the value of the first index of the array. Unless I'm missing something, remove the [0] from the end of the "let" line  : )
0
 

Author Comment

by:mcunn
ID: 34997136
Yep, I caught it... totally forgot to post the correction as I was so excited it actually worked.

Thanks again!

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 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

24 Experts available now in Live!

Get 1:1 Help Now