SQL to LINQ translating probem

Posted by ognjenb on Stack Overflow See other posts from Stack Overflow or by ognjenb
Published on 2010-05-04T10:42:35Z Indexed on 2010/05/04 10:48 UTC
Read the original article Hit count: 132

Filed under:
|

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

                          };

© Stack Overflow or respective owner

Related posts about LINQ

Related posts about c#