Join with Correlation

ServiceStack.OrmLite
1 
var query = _db.From<Table1>()
    .LeftJoin<Table1, Table2>((t1, t2) =>
        t1.Col1 == t2.Col1 && t1.Col2 == t2.Col2)
    .LeftJoin<Table1, Table2, Table3>((t1, t2, t3) =>
        (t2.Col3 == t3.Col3 && t2.Col2 == t3.Col2) ||
        (t1.Col4 == t3.Col5 && t1.Col2 == t3.Col2))
    .Where(t1 => t1.Col1 == someValue && t1.Col2 == someOtherValue)
    .SelectDistinct<Table3>(t3 => t3.Col5);

I am trying to do this from my C# code so I will get result but in sql it works perfectly but when I am adding OR join I am getting error like this that i need to use correlation names

The objects "Table2" and "Table2" in the FROM clause have the same exposed names. 
Use correlation names to distinguish them.

Now either I have to separate them into two queries which ideally I dont want to do. Or else I found that i can use the correlation names but I tried using TableAlias but that didnt help me much

2

Please show the generated SQL when reporting OrmLite SQL Expression issues.

If the issue is because the tables are ambiguous you would need to use Table Aliases.

3

Here is my generated sql from profiler

exec sp_executesql N'SELECT DISTINCT "Core"."Matter"."Id" 
FROM "Core"."Context" LEFT JOIN "Core"."Client_Contact" ON (("Core"."Context"."ContactId" = "Core"."Client_Contact"."ContactId") AND ("Core"."Context"."SystemId" = "Core"."Client_Contact"."SystemId")) LEFT JOIN "Core"."Client_Contact" ON ((("Core"."Client_Contact"."ClientId" = "Core"."Matter"."ClientId") AND ("Core"."Client_Contact"."SystemId" = "Core"."Matter"."SystemId")) OR (("Core"."Context"."MatterId" = "Core"."Matter"."Id") AND ("Core"."Context"."SystemId" = "Core"."Matter"."SystemId")))
WHERE (("Core"."Context"."ContactId" = @0) AND ("Core"."Context"."SystemId" = @1))',N'@0 int,@1 int',@0=231,@1=1
4

@mythz I saw at few places custom join expression works but I tried that too but still wont get the results

5

Please provide a stand-alone example (with the generated SQL) that I can run that includes the table aliases so the tables are not ambiguous.

6

Okay so this is the SQL query I have which is working perfectly and giving me back the results

SELECT   DISTINCT m.Id
FROM   Core.Context cx
    LEFT JOIN Core.Client_Contact cc ON cx.ContactId = cc.ContactId AND cx.SystemId = cc.SystemId
    LEFT JOIN Core.Matter m ON (cc.ClientId = m.ClientId AND cc.SystemId = m.SystemId)
        OR (cx.MatterId = m.Id AND cx.SystemId = m.SystemId)
WHERE   cx.ContactId = @ContactId
    AND   cx.SystemId = @SystemId;

This is the query I tried to write in ORMLite which is giving error of ambiguous table

_db.From<DbCore.Context>()
            .LeftJoin<DbCore.Context, DbCore.ClientContact>((cx, cc1) => cx.ContactId == cc1.ContactId && cx.SystemId == cc1.SystemId)
            .LeftJoin<DbCore.Context, DbCore.ClientContact, DbCore.Matter>((cx, cc1, m) =>
                (cc1.ClientId == m.ClientId && cc1.SystemId == m.SystemId) ||
                (cx.MatterId == m.Id && cx.SystemId == m.SystemId))
            .Where(cx => cx.ContactId == contactId && cx.SystemId == systemId)
            .SelectDistinct<DbCore.Matter>(m => m.Id);

This query which I wrote translates into this in SQL Profiler

exec sp_executesql N'SELECT DISTINCT "Core"."Matter"."Id" 
FROM "Core"."Context" LEFT JOIN "Core"."Client_Contact" ON (("Core"."Context"."ContactId" = "Core"."Client_Contact"."ContactId") AND ("Core"."Context"."SystemId" = "Core"."Client_Contact"."SystemId")) LEFT JOIN "Core"."Client_Contact" ON ((("Core"."Client_Contact"."ClientId" = "Core"."Matter"."ClientId") AND ("Core"."Client_Contact"."SystemId" = "Core"."Matter"."SystemId")) OR (("Core"."Context"."MatterId" = "Core"."Matter"."Id") AND ("Core"."Context"."SystemId" = "Core"."Matter"."SystemId")))
WHERE (("Core"."Context"."ContactId" = @0) AND ("Core"."Context"."SystemId" = @1))',N'@0 int,@1 int',@0=231,@1=1

And I get error for "Core"."Client_Contact" table

7

Your expression does not use any table aliases? But you’re using it your query you’re trying to emulate, it should have at least have the same number of table aliases.

8

Sorry, beg your pardon.

I guess queries are getting little messed up

This is my SQL query working perfectly.

	SELECT   DISTINCT m.Id
FROM   Core.Context cx
    LEFT JOIN Core.Client_Contact cc ON cx.ContactId = cc.ContactId AND cx.SystemId = cc.SystemId
    LEFT JOIN Core.Matter m ON (cc.ClientId = m.ClientId AND cc.SystemId = m.SystemId)
        OR (cx.MatterId = m.Id AND cx.SystemId = m.SystemId)
WHERE   cx.ContactId = 231
    AND   cx.SystemId = 0;

Now I am writing that as follows

 var query = _db.From<DbCore.Context>()
     .LeftJoin<DbCore.Context, DbCore.ClientContact>((cx, cc) => cx.ContactId == cc.ContactId && cx.SystemId == cc.SystemId)
     .LeftJoin<DbCore.Context, DbCore.ClientContact, DbCore.Matter>((cx, cc, m) =>
         (cc.ClientId == m.ClientId && cc.SystemId == m.SystemId) ||
         (cx.MatterId == m.Id && cx.SystemId == m.SystemId))
     .Where(cx => cx.ContactId == contactId && cx.SystemId == systemId)
     .SelectDistinct<DbCore.Matter>(m => m.Id);

So the alias I am using are same as my sql query that is m, cc, cx

Now when I run this query in my SQL Server Profiler I get this,

exec sp_executesql N'SELECT DISTINCT "Core"."Matter"."Id" 
FROM "Core"."Context" LEFT JOIN "Core"."Client_Contact" ON (("Core"."Context"."ContactId" = "Core"."Client_Contact"."ContactId") AND ("Core"."Context"."SystemId" = "Core"."Client_Contact"."SystemId")) LEFT JOIN "Core"."Client_Contact" ON ((("Core"."Client_Contact"."ClientId" = "Core"."Matter"."ClientId") AND ("Core"."Client_Contact"."SystemId" = "Core"."Matter"."SystemId")) OR (("Core"."Context"."MatterId" = "Core"."Matter"."Id") AND ("Core"."Context"."SystemId" = "Core"."Matter"."SystemId")))
WHERE (("Core"."Context"."ContactId" = @0) AND ("Core"."Context"."SystemId" = @1))',N'@0 int,@1 int',@0=231,@1=1

Here you cant see alias because thats how the ORMLite converts query in EFCore

I guess now this will help you understand better

And then I am left with following error
The objects "Core.Client_Contact" and "Core.Client_Contact" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

9

Discourse is showing you’ve visited the Table Alias docs but your query still doesn’t include any? It should have the same aliases as your working SQL.

10

The generated query if you are saying then it is getting formed by EFCore and yes thats where I am facing issue because it is not adding any alias instead it is using full names and that’s why getting the error

11

Also if I have to add explicit alias how do I add them?

12

I don’t understand what’s going on, you’re showing an OrmLite SqlExpression, what does this have to do with EF Core?

Please read the Table Alias Docs.

13

That’s true.
Apologies for that.

The only reason I am mentioning the EFCore because when I write ORMLite SQLExpression in my C# and run my application then that SQLExpression is getting passed to EFCore DbContext which is converting my SQLExpression in the query I showed above with the entire table name and wont add any alias

Sorry about the confusion caused!

14

It’s not including any aliases because you haven’t add any, I’m hoping you’ve read the Table Alias docs by now, i.e. your OrmLite query needs to use db.TableAlias(), starting with:

var query = _db.From<DbCore.Context>(db.TableAlias("cx"))
...
15

Yes I read the docs to add alias.
I am able to add alias like this

  var query = _db.From<DbCore.Context>(_db.TableAlias("cx"))
      .LeftJoin<DbCore.Context, DbCore.ClientContact>((cx, cc) =>
          Sql.TableAlias(cx.ContactId, "cx") == Sql.TableAlias(cc.ContactId, "cc") &&
          Sql.TableAlias(cx.SystemId, "cx") == Sql.TableAlias(cc.SystemId, "cc"), _db.TableAlias("cc"))

But when I have to use three tables in my lambda expression

LeftJoin<DbCore.Context, DbCore.ClientContact, DbCore.Matter>((cx, cc, m) =>
         (cc.ClientId == m.ClientId && cc.SystemId == m.SystemId) ||
         (cx.MatterId == m.Id && cx.SystemId == m.SystemId))

Its not letting me add the alias saying LeftJoin has no overload with two params.
I tried writing it like this

        var query = _db.From<DbCore.Context>(_db.TableAlias("cx"))
            .LeftJoin<DbCore.Context, DbCore.ClientContact>((cx, cc) =>
                Sql.TableAlias(cx.ContactId, "cx") == Sql.TableAlias(cc.ContactId, "cc") &&
                Sql.TableAlias(cx.SystemId, "cx") == Sql.TableAlias(cc.SystemId, "cc"), _db.TableAlias("cc"))
            .LeftJoin<DbCore.Context, DbCore.ClientContact, DbCore.Matter>((cx, cc, m) =>
                (Sql.TableAlias(cc.ClientId, "cc") == Sql.TableAlias(m.ClientId, "m") &&
                 Sql.TableAlias(cc.SystemId, "cc") == Sql.TableAlias(m.SystemId, "m")) ||
                (Sql.TableAlias(cx.MatterId, "cx") == Sql.TableAlias(m.Id, "m") &&
                 Sql.TableAlias(cx.SystemId, "cx") == Sql.TableAlias(m.SystemId, "m")), _db.TableAlias("m"))
            .Where(cx => Sql.TableAlias(cx.ContactId, "cx") == contactId &&
                         Sql.TableAlias(cx.SystemId, "cx") == systemId)
            .SelectDistinct<DbCore.Matter>(m => new {
                Id = Sql.TableAlias(m.Id, "m")
            });
16

Yeah you can only add a table alias to a joined table with a source and a target generic arguments.

As such you’re going to have to sprinkle in some custom SQL to avoid needing the 3rd table join:

var query = _db.From<DbCore.Context>(_db.TableAlias("cx"))
    .LeftJoin<DbCore.Context, DbCore.ClientContact>(
        (cx, cc) => cx.ContactId == cc.ContactId && cx.SystemId == cc.SystemId, _db.TableAlias("cc"))
    .LeftJoin<DbCore.Matter>((cc, m) =>
        (cc.ClientId == m.ClientId && cc.SystemId == m.SystemId)
        || Sql.Custom<bool>("(cx.MatterId = m.Id AND cx.SystemId = m.SystemId)"), _db.TableAlias("m"))
    .Where(cx => cx.ContactId == contactId && cx.SystemId == systemId)
    .SelectDistinct<DbCore.Matter>(m => m.Id);

If you want to use typed references in your custom SQL you can do it with something like:

var q = _db.From<DbCore.Context>(_db.TableAlias("cx"));
expr = string.Format("(cx.{0} = m.{1} AND cx.{2} = m.{3})",
    q.Column<DbCore.Context>(cx => cx.MatterId),
    q.Column<DbCore.Matter>(m => m.Id),
    q.Column<DbCore.Context>(cx => cx.SystemId),
    q.Column<DbCore.Matter>(m => m.SystemId));

var query = q
    .LeftJoin<DbCore.Context, DbCore.ClientContact>((cx, cc) => 
        cx.ContactId == cc.ContactId && cx.SystemId == cc.SystemId, _db.TableAlias("cc"))
    .LeftJoin<DbCore.Matter>((cc, m) =>
        (cc.ClientId == m.ClientId && cc.SystemId == m.SystemId)
        || Sql.Custom<bool>(expr), _db.TableAlias("m"))
    .Where(cx => cx.ContactId == contactId && cx.SystemId == systemId)
    .SelectDistinct<DbCore.Matter>(m => m.Id);

Which looks like it generates:

SELECT DISTINCT "Matter"."Id"
FROM "Context" "cx"
    LEFT JOIN "ClientContact" "cc" ON (
        ("cx"."ContactId" = "cc"."ContactId")
        AND ("cx"."SystemId" = "cc"."SystemId")
    )
    LEFT JOIN "Matter" "m" ON (
        (("cx"."ClientId" = "m"."ClientId") AND ("cx"."SystemId" = "m"."SystemId"))
        OR (cx."MatterId" = m."Id" AND cx."SystemId" = m."SystemId")
    )
WHERE (("cx"."ContactId" = @0) AND ("cx"."SystemId" = @1))
17

I see I read that and was thinking some other way.
Thanks for the way you provided.

var q = _db.From<DbCore.Context>(_db.TableAlias("cx"));
expr = string.Format("(cx.{0} = m.{1} AND cx.{2} = m.{3})",
    q.Column<DbCore.Context>(cx => cx.MatterId),
    q.Column<DbCore.Matter>(m => m.Id),
    q.Column<DbCore.Context>(cx => cx.SystemId),
    q.Column<DbCore.Matter>(m => m.SystemId));

var query = q
    .LeftJoin<DbCore.Context, DbCore.ClientContact>((cx, cc) => 
        cx.ContactId == cc.ContactId && cx.SystemId == cc.SystemId, _db.TableAlias("cc"))
    .LeftJoin<DbCore.Matter>((cc, m) =>
        (cc.ClientId == m.ClientId && cc.SystemId == m.SystemId)
        || Sql.Custom<bool>(expr), _db.TableAlias("m"))
    .Where(cx => cx.ContactId == contactId && cx.SystemId == systemId)

The cc in next second join is refered as Context and not ClientContact which is getting wrong
Also if I explicitly add

.LeftJoin<DbCore.ClientContact,DbCore.Matter>

Then I get error as
The multi-part identifier "Core.Client_Contact.ClientId" could not be bound. The multi-part identifier "Core.Client_Contact.SystemId" could not be bound. The multi-part identifier "Core.Matter.Id" could not be bound.

18

You should be able use Sql.TableAlias, e.g:

var query = q
.LeftJoin<DbCore.Context, DbCore.ClientContact>((cx, cc) => 
  cx.ContactId == cc.ContactId && cx.SystemId == cc.SystemId, _db.TableAlias("cc"))
.LeftJoin<DbCore.ClientContact, DbCore.Matter>((cc, m) =>
    (Sql.TableAlias(cc.ClientId, "cc") == m.ClientId && 
     Sql.TableAlias(cc.SystemId, "cc") == m.SystemId)
    || Sql.Custom<bool>(expr), _db.TableAlias("m"))
.Where(cx => cx.ContactId == contactId && cx.SystemId == systemId)
.SelectDistinct<DbCore.Matter>(m => m.Id);
19

Thanks a lot @mythz that worked perfectly just I added TableAlias for select statement too.

Just one query that for third join we created one custom join condition in our expr variable right? So it would support the Aliasing?

20

We needed to use a Custom SQL fragment to avoid a Join Expression with 3 tables so we can use a TableAlias("m") for the target join table.