SQL Sever has a "shortcoming" when doing sorting and distinct. An article on the subject can be found here: http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

I have the following query set up:

//Get subtags
var tagSource = this.GetArticleTagSource();

//Init params
showLeftNav = false;
showRightNav = false;

if (excludeIds == null) excludeIds = new List<int>();
if (pageIndex == null) pageIndex = 0;

int skip = pageIndex == 0 ? 0 : firstPageSize + ((pageIndex - (firstPageSize > 0 ? 1 : 0)) * pageSize);
int take = pageIndex == 0 && firstPageSize > 0 ? firstPageSize : pageSize;

//TODO: Should we properly handle the event that there is a tag with courses only? Plausible?
var itemAlias = new AqlAliasHjortArticleBase();
if (typeof(T) == typeof(Article)) itemAlias = new AqlAliasArticle();
if (typeof(T) == typeof(Note)) itemAlias = new AqlAliasNote();
if (typeof(T) == typeof(Course)) itemAlias = new AqlAliasCourse();
if (typeof(T) == typeof(InfoArticle)) itemAlias = new AqlAliasInfoArticle();
if (typeof(T) == typeof(Vacancy)) itemAlias = new AqlAliasVacancy();
var tagAlias = new AqlAliasBaseTag();
var tagJoin = new AqlAliasRelTaxonomyHierarchical(itemAlias, tagAlias);

var query = Session.CreateQuery();
query.From(tagJoin);
query.Select(itemAlias);
query.Where(Aql.In(tagAlias.NodeId, tagSource.Select(x => x.NodeId)));

// Exclude any items in the given list
if (excludeIds != null && excludeIds.Any()) query.Where(!Aql.In(itemAlias.NodeId, excludeIds));

// Set date filtering
if (monthFilter.GetValueOrDefault(0) > 0 && yearFilter.GetValueOrDefault(0) > 0)
{
	query.Where(Aql.DateTimePart(itemAlias.PublishDate, DateTimePart.Year) == yearFilter.Value & Aql.DateTimePart(itemAlias.PublishDate, DateTimePart.Month) == monthFilter.Value);
}

query.OrderByFirst(itemAlias.PublishDate, true);

// Order by name lastly
query.OrderBy(itemAlias.Name, false);

// Do paging
query.Skip = skip;
if (take > 0) query.Take = take;

var items = new List<T>();
query.RetrieveTotalCount = true;

query.Distinct();

var rs = query.Execute();

totalCount = rs.TotalRowCount;
pageCount = firstPageSize > 0 ? 1 : 0;
pageCount += (totalCount - firstPageSize + pageSize - 1) / pageSize;

if (!showLeftNav)
{
	if (pageIndex > 0) showLeftNav = true;
}

if (!showRightNav)
{
	if ((pageIndex + 1) < pageCount) showRightNav = true;
	else if (pageIndex == 0 && ((firstPageSize > 0 && totalCount > firstPageSize) || (firstPageSize == 0 && totalCount > pageSize)))
	{
		showRightNav = true;
	}
}

while (rs.Read())
{
	var item = (T)rs[0];
	if (item != null) items.Add(item);
}

return items;

Basically, I want to get some articles from a number of tags, distinct() them (because the articles do come in dupes even when I use IN()). I get the following error message from the SQL-layer:

The sql generated looks like this:

SELECT DISTINCT TOP 3 [t0rlll].[id] AS [Content]
, [t0rllr].[culture_id] AS [f0]
, [t0rlr].[revision] AS [f1]
, [t0rlll].[content_class_id] AS [f2]
FROM ((([native_node] AS [t0lll]
INNER JOIN [native_node_csd] AS [t0llr] ON [t0lll].[id]=[t0llr].[node_id])
INNER JOIN [native_content] AS [t0lr] ON [t0llr].[node_id]=[t0lr].[node_id] AND [t0llr].[culture_id]=[t0lr].[culture_id])
INNER JOIN ((([native_node] AS [t0rlll]
INNER JOIN [native_node_csd] AS [t0rllr] ON [t0rlll].[id]=[t0rllr].[node_id])
INNER JOIN [native_content] AS [t0rlr] ON [t0rllr].[node_id]=[t0rlr].[node_id] AND [t0rllr].[culture_id]=[t0rlr].[culture_id])
INNER JOIN [native_rel_hierarchical_taxonomy] AS [t0rr] ON [t0rlll].[id]=[t0rr].[hier_id]) ON [t0lll].[id]=[t0rr].[tag_id])
WHERE ([t0rlll].[content_class_id] IN(4))
AND [t0rlr].[culture_id]=@P0
AND [t0rlr].[revision]=@P1
AND [t0rllr].[r_group_id]=@P2
AND [t0rlll].[is_deleted]=@P3
AND [t0rlll].[is_published]=@P4
AND [t0rlr].[retain_date]=@P5
AND [t0rlr].[is_deleted]=@P6
AND ([t0lll].[content_class_id] IN(25,11,12,13))
AND [t0lr].[culture_id]=@P7
AND [t0lr].[revision]=@P8
AND [t0llr].[r_group_id]=@P9
AND [t0lll].[is_deleted]=@P10
AND [t0lll].[is_published]=@P11
AND [t0lr].[retain_date]=@P12
AND [t0lr].[is_deleted]=@P13
AND ([t0lll].[id] IN(2230,8247,331642,331644,331646,331648,331650,331654,331656,331658,16273,16275,331666,331670,331672,331664,331662,331660,1207))
ORDER BY [t0rlr].[publish_date] DESC
, [t0rlr].[name]]

I tried to run this directly on the sql server and the same error pops up of course. The fix seems to be to select the order by fields. Is this something you're aware of, or is there a workaround for it?

I realized I could just add query.Select(itemAlias.PublishDate) and query.Select(itemAlias.Name) directly on the AQL and that seemed to do the trick! I would anyhow like to get a statement from you guys on the matter. :)

Ok, thanks!