Take method just doesn't work
Yeh that's what I did:
var offers = (from c in meta.Offer
join o in meta.AuditOffer on c.OfferId equals o.OfferId
where o.MemberId == memberId && c.IsActive == true
orderby c.EndDate descending
select c).ExcludeFields(p=>p.CompanyOverview, p=>p.Voucher, p=>p.Image, p=>p.Image1, p=>p.Image2).Take(3)
.WithPath(path => path.Prefetch<RedemptionLevelEntity>(p => p.RedemptionLevelUsingOfferTypeId).Prefetch<AlliancePartnerEntity>(p => p.AlliancePartnerUsingAlliancePartnerId)).ToList();
Works like a charm now 
Couple of questions and a couple of remarks 
questions: 1) what are 'useless subqueries' ? With linq the provider is slowly working through the expression tree to make it a sql executable query. Linq isn't 1:1 projectable onto sql, so conversions have to be made. In the end things might have been optimized abit, but with the subqueries the query should work.
2) when you ordered on a field not in the select list, did you still get an exception that ordering isn't allowed?
A couple of remarks: - when ordering on a field not in the select list, distinct can't be used. The reason is this: the RDBMS adds silently the field(s) ordered to the select list, then sorts and filters for distinct. As the ordering fields are extra columns, they could make a row 'unique' even though they're not in the select list. This is a problem with many databases, it's how they optimize the pipeline. LLBLGen Pro provides an abstraction layer for the database, so it's not the problem of the developer if a query using method ABC is going wrong, so it detects problems and switches to an alternative so you get the results you wanted.
-
Adding the extra columns to the select list is not going to help as you then still get duplicates which could give wrong results and thus TOP is producing the wrong subset.
-
To help you further, it's best if you try to limit down the query to the point where problems arise and from there, please describe the problems in 1 thread at a time. So if you run into multiple problems, please open multiple threads if possible, so the discussion in this thread is easy to follow
Some success!
I changed the audit part of the query to this:
var audit = (from c in meta.AuditOffer
join o in meta.Offer on c.OfferId equals o.OfferId
where c.MemberId == 3374815 && o.IsActive == true && o.IsApproved == true
group c by c.OfferId into g
//orderby g.Max(p=>p.DateCreated) descending
select new { OfferId = g.Key, DateCreated = g.Max(p => p.DateCreated) }).OrderByDescending(p=>p.DateCreated).Distinct().Take(3);
I had tried moving the order by out of linq into a OrderByDescending method call before but I didn't try it with Distinct() as well. This apparently makes all the difference. This way it looks like LLBL is able to identify that the group by aggregrate and the orderby are referencing the same field thus allowing a top, distinct order by query. Cue balloons and party hats!
I must say this LINQ stuff is... obscure