I have two related tables. For example
Students
----------------------
| Id | Name |
----------------------
| 1 | Federic |
----------------------
| 2 | Sancho |
----------------------
Teachers
----------------------------------
| Id | Name | Student_id |
----------------------------------
| 1 | Jose | 1 |
----------------------------------
| 2 | Carlos | 1 |
----------------------------------
| 3 | Eduardo| 2 |
----------------------------------
And the result should be
Students
----------------------------------
| Id | Name | Teachers |
----------------------------------
| 1 | Federic | Jose,carlos |
----------------------------------
| 2 | Sancho| Eduardo |
----------------------------------
I need to make it in Linq
Iam working with other tables but their work in the same way
var Technology = (from tTechnology in Context.Technology
join tUsers in Context.Users on tTechnology.EnableBy equals tUsers.UserId into collection
from subCase in collection.DefaultIfEmpty()
join tUsers2 in Context.Users on tTechnology.LastChangeBy equals tUsers2.UserId into collection2
from subCase2 in collection2.DefaultIfEmpty()
join tUsers3 in Context.Users on tTechnology.DisableBy equals tUsers3.UserId into collection3
from subCase3 in collection3.DefaultIfEmpty()
join tResolution in Context.Resolution on tTechnology.TechnologyId equals tResolution.TechnologyId
into collection4
from subCase4 in collection4.DefaultIfEmpty()
where tTechnology.DisableDate == null
//orderby
select new
{
tTechnologyTechnologyId = tTechnology.TechnologyId,
tTechnologyName = tTechnology.Name,
tTechnologyDescription = tTechnology.Description,
tCityName = tTechnology.City.Name,
tStateName = tTechnology.City.State.Name,
tCountryName = tTechnology.City.State.Country.Name,
tResolutions = subCase4.Measure,
tEnableBy = (subCase.Name == null ? null : subCase.Name) + "" + (subCase.FirstLastName == null ? null : subCase.FirstLastName) + " " + (subCase.SecondLastName == null ? null : subCase.SecondLastName),
tEnableDate = tTechnology.EnableDate,
tLastChangeDate = tTechnology.LastChangeDate,
tLastChangeBy = (subCase2.Name == null ? null : subCase2.Name) + " " + (subCase2.FirstLastName == null ? null : subCase2.FirstLastName) + " " + (subCase2.SecondLastName == null ? null : subCase2.SecondLastName),
tDisableDate = tTechnology.DisableDate,
tDisableBy = (subCase3.Name == null ? null : subCase3.Name) + " " + (subCase3.FirstLastName == null ? null : subCase3.FirstLastName) + " " + (subCase3.SecondLastName == null ? null : subCase3.SecondLastName),
});
And i want to put all the resolutions that a Technology has as the teachers for the students in the example