TableAlias doesn't work with multiple joins

user_657477 0
Viewed 23

TableAlias isn't working with multiple joins.

The query:

var q = Db.From<Blog>(Db.TableAlias("b"))
    .LeftJoin<Blog, BlogToBlogCategory>((b,btb)=> b.Id == btb.BlogId, Db.TableAlias("btbc"))
    .Join<BlogToBlogCategory, BlogCategory>((bt,bc)=>bt.BlogCategoryId == bc.Id, Db.TableAlias("cats"))
    .GroupBy(x => x.Id);
    .Select("b.*, json_agg(cats) as BlogCategoriesJson");

var results = Db.Select<BlogQueryResponse>(q);

Generates this SQL:

SELECT b.*, json_agg(cats) as BlogCategoriesJson
FROM "blog" "b" LEFT JOIN "blog_to_blog_category" "btbc" ON ("b"."id" = "btbc"."blog_id") INNER JOIN "blog_category" "cats" ON ("blog_to_blog_category"."blog_category_id" = "cats"."id")
GROUP BY "b"."id"

This causes error because it is referencing "blog_to_blog_category" instead of btbc

servicestack
1 Answers

The Db.TableAlias() only provides an alias for the target join table, your inner join does not specify the alias to use for the source table so it references the full table name as expected.

You can use Sql.TableAlias() in your LINQ Expression to reference a table alias, e.g:

var q = Db.From<Blog>(Db.TableAlias("b"))
    .LeftJoin<Blog, BlogToBlogCategory>((b,btb)=> b.Id == btb.BlogId, Db.TableAlias("btbc"))
    .Join<BlogToBlogCategory, BlogCategory>((bt,bc)=>
         Sql.TableAlias(bt.BlogCategoryId, "btbc") == bc.Id, Db.TableAlias("cats"))
    .GroupBy(x => x.Id);
    .Select("b.*, json_agg(cats) as BlogCategoriesJson");
user_857850
Related