Solved

join linq to sql with an array

Posted on 2011-02-25
729 Views
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
};


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
Question by:mcunn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 75

Expert Comment

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]
};

0

Author Comment

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]


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

Thanks

Michael

0

LVL 63

Assisted Solution

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]
};

0

LVL 75

Accepted Solution

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

Author Closing Comment

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

LVL 75

Expert Comment

ID: 34983190
NP. Glad to help  : )
0

LVL 75

Expert Comment

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

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

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month4 days, 18 hours left to enroll