HI everyone.

I know this aint an Umbraco specific problem. But who is better to help then this community :)

My problem is that i can't get my count to work with INNER JOINS.

Language and DocumentType and Versions are all ResultColumn's in the Document object. Language and DocumentType are other poco table object, while Versions is an Integer(int32).

This is working, but here i miss the number of Versions.

using Umbraco.Core;
using Umbraco.Core.Persistence;

var dbContext = ApplicationContext.Current.DatabaseContext;
var db = dbContext.Database;

var sql = Sql.Builder
            .Append("SELECT * FROM Documents AS a")
            .Append("INNER JOIN [Languages] AS b ON a.FkLanguage = b.Id")
            .Append("INNER JOIN [DocumentTypes] AS c ON a.FkDocumentType = c.Id");

// TypeOf( List<Document> ) 
var result = db.Fetch<Document, Language, DocumentType, Document>((a, b, c) => { a.Language = b; a.DocumentType = c; return a; }, sql);
return result;

This aint working, and the part where i need some help ;)

And if Google is down, then it's because of me..

using Umbraco.Core;
using Umbraco.Core.Persistence;

var dbContext = ApplicationContext.Current.DatabaseContext;
var db = dbContext.Database;

var sql = Sql.Builder
            .Append("SELECT *,*,*,count(*) FROM Documents AS a")
            .Append("INNER JOIN [Languages] AS b ON a.FkLanguage = b.Id")
            .Append("INNER JOIN [DocumentTypes] AS c ON a.FkDocumentType = c.Id")
            .Append("LEFT JOIN [DocumentVersions] AS d ON a.Id = d.FkDocumentId");

// TypeOf( List<Document> ) 
var result = db.Fetch<Document, Language, DocumentType, int, Document>((a, b, c, d) => { a.Language = b; a.DocumentType = c; a.Versions = d; return a; }, sql);
return result;

ERROR

Server Error in '/' Application.

Column 'Documents.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Column 'Documents.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.