LikeLinq

Hi,

In my database design I've several teachers who can teach different classes.

My database design (in short and no sql syntax):

table Teachers(int teacher_id, string firstName, string lastName)

table Classes(int class_id, string className)

table ClassDivision(int teacher_id, int class_id)

Now I want an output as follows:

Teacher A: Classes 1, 2 and 3

Teacher B: Classes 1

Teacher C: Classes 4 and 5

How can I query and output this through LINQ

thanks

kGenius




Re: LINQ Project General Linq multiple values to single string

Matt Warren - MSFT

You could just foreach it and print out all the data

Or you asking how to write a query over teachers that produces a sequence of strings where each string looks like "Teacher A: Classes 1, 2 and 3"

It would be awkward but possible to do with LINQ, however not directly possible to be done with LINQ to SQL and have it all translated back to the database server. What you'd have to do is project out the data and then switch to LINQ to Objects using AsEnumerable and then continue with a Select() call taking a statement lambda.






Re: LINQ Project General Linq multiple values to single string

kGenius

Hi,

thanks for your answer. This is what I've got now:

Code Block

TeacherDataContext tdc = new TeacherDataContext

var pers = from p in tdc.Teachers

select new

{

Name = p.firstName + " " + p.lastName,

Class = from pd in pdc.TeacherClasses

join c in pdc.Classes on pd.class_id equals c.class_id

where pd.personnel_id == p.personnel_id

select new

{

Class = c.className

}

};

Now with this code, I can bind my asp.net page to Name (which is a label databound to Name)

The Class label cannot be bound because this is a generic list. I want this list to flattened to a joined string. So it can be bound to the label on my asp.net page

Hope I make myself clear enough. (Poor English )






Re: LINQ Project General Linq multiple values to single string

Keith Farmer

Another approach might be to use groupby over the mapping table. Does the following work (I've added the suggested AsEnumerable() call to switch processing to LINQ to Objects, as well as a String.Join to build a class list string.)

Code Block

var pers =

tdc.TeacherClasses

.GroupBy(teacherClass => teacherClass.Teacher)

.AsEnumerable() // after this, it's LINQ to Objects

.Select(

teacher => new {

Name = teacher.Key.FirstName + " " + teacher.Key.LastName,

ClassString = String.Join(", ",

teacher.Value

.Select(teacherClass => teacherClass.Class.ClassName)

.ToArray()

)

}

);

In any event, you should be able to see how you could build your class label. Using LoadWith to pre-load the teachers and classes with the mappings would be a good idea with this approach.