SELECT        service.id, service.short_name, service.long_name, service.code, service.service_type_id, service.is_deleted, service_price.price
FROM            service INNER JOIN
                         service_price ON service.id = service_price.service_id
WHERE        (service.is_deleted = 0) AND (service_price.date =
                             (SELECT        MAX(date) AS Expr1
                               FROM            service_price AS S
                               WHERE        (service_id = service_price.service_id) AND (date <= @date))) AND (service.long_name <> '')
ORDER BY service.code

IQueryable<service> service = context.service;
            if (!includeDeleted)
            {
                service = service.Where(x => (!x.is_deleted.HasValue || !x.is_deleted.Value));
            }

foreach (service srv in service)
            {
                service_price sp = srv.service_price.OrderByDescending(r => r.date).FirstOrDefault(r => r.date <= date);
                if (sp != null)
                    srv.price = sp.price;
            }

public IEnumerable<service> GetServices(Entities context, DateTime dt)
        {
            var res = from s in context.service
                      join sp in context.service_price on s.id equals sp.service_id
                      let max =
                          context.service_price.Where(x => x.service_id == sp.service_id && x.date <= dt && s.long_name != "")
                              .Max(x => x.date)
                      where (!s.is_deleted.HasValue || !s.is_deleted.Value) && sp.date == max
                      select new { s.id, s.short_name, s.long_name, s.code, s.service_type_id, s.is_deleted, sp.price };

            return res.ToList()
                .Select(x => new service()
                {
                    code = x.code,
                    id = x.id,
                    is_deleted = x.is_deleted,
                    long_name = x.long_name,
                    price = x.price,
                    service_type_id = x.service_type_id,
                    short_name = x.short_name
                });
        }