Solved

# join linq to sql with an array

Posted on 2011-02-25
717 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
• 4
• 3

LVL 74

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 62

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 74

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 74

Expert Comment

ID: 34983190
NP. Glad to help  : )
0

LVL 74

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

### Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …