I have problem with convertion this SQL statement to LINQ :
SELECT
a.Id,
a.Name,
a.ArtiklNumber,
a.Notes,
a.Weight,
l.StartDate AS LastStartDate, l.LocationNameId, loc.Name AS CarrentLocation,
a.Reserved
FROM Accessories a LEFT OUTER JOIN Location l LEFT JOIN LocationName loc
ON l.LocationNameId = loc.Id
ON a.Id = (SELECT AccessoriesId
FROM Location
WHERE AccessoriesId = a.Id
HAVING MAX(StartDate) = StartDate )
This is part of my translated code:
testEntities6 accessoriesEntities = new testEntities6();
var max_StartDate = (from msd in accessoriesEntities.location
from d in accessoriesEntities.device
where msd.DeviceId == d.Id
select msd.StartDate).Min();
var accessories_query = from accs in accessoriesEntities.accessories
join l in accessoriesEntities.location on accs.Id equals l.AccessoriesId
join loc in accessoriesEntities.locationname on l.LocationNameId equals loc.Id
select new AccessoriesModel {
//Accessories
Id = accs.Id,
Name = accs.Name,
ArtiklNumber = accs.ArtiklNumber,
Notes = accs.Notes,
Weight = accs.Weight,
Reserved = accs.Reserved,
//Location
LocationNameId = l.LocationNameId,
StartDate = max_StartDate,//l.StartDate,
//Locationname
Loc_name = loc.Name
};
