"LEFT JOIN" equivalent to ".With"

[brunoAltinet]

What I could see is that With is translated to "INNER JOIN". How can i get it to translate to "LEFT JOIN"? I need to query against different types which have different mappings.

[scil]

"left join" is not supported directly.

Write mannuly.

My code to fetch some comments, and names of pictures/articles who holds the comments. A orchardcore project.

First I get the count, then I use the same sqlBuilder to get comments.

key is sqlBuilder.From($" LEFT JOIN ArticleIndex AS b ON.

After year of php laravel, I just have to use C# net core and found I was with poor docs and poort productivity :(.

I wasted some night hours and a morning to resolve this simple left join issure, which I just use seconds with php Laravel.

maybe bz i'a newbie. Help my code could help you.

            IList<CommentDetailViewModel> result = new List<CommentDetailViewModel>();

                    var dialect = _store.Configuration.SqlDialect;
                    var sqlBuilder = dialect.CreateBuilder(_tablePrefix);
                    dynamic param = new System.Dynamic.ExpandoObject();

                    sqlBuilder.Select();

                    sqlBuilder.Table(_table, "a");

                    if (options.BussId > 0)
                    {
                        sqlBuilder.WhereAnd($"a.{dialect.QuoteForColumnName(nameof(CommentRecord.BussId))} = @bussId");
                        param.bussId = options.BussId;
                    }

                    sqlBuilder.Selector("count(*)");
                    count = await SqlMapper.QueryFirstAsync<int>(connection,
                        sqlBuilder.ToSqlString(), param);

                    if (pager != null)
                    {
                       sqlBuilder.Skip(pager.GetStartIndex().ToString()); 
                       sqlBuilder.Take(pager.PageSize.ToString());
                    }

                    // should use `nameof`
                    var ArticleIndexTableName = "ArticleIndex";
                    var ArticleIdField = "DocumentId"; 
                    var ArticleNameField = "Name";

                    sqlBuilder.Selector($"a.*, b.{dialect.QuoteForColumnName(ArticleNameField)} AS BussName");

                    sqlBuilder.From($" LEFT JOIN {ArticleIndexTableName} AS b ON a.{dialect.QuoteForColumnName(nameof(CommentRecord.BussId))} = b.{dialect.QuoteForColumnName(ArticleIdField)}");

                    var sql = sqlBuilder.ToSqlString()  ;
                    result = await SqlMapper.QueryAsync<CommentDetailViewModel>(connection,
                        sql, param);