Mixing
Inner Join with two equalities inside on clause in LINQ Lambda
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm trying to convert a Sql query to a Linq Lambda style query. Thought this would be something easy but it turned out not.
SQL Query is as follows;
select distinct t1.ID from table1 t1
inner Join table2 t2on (t2.FromId= t1.Id or t2.ToId= t1.Id)
where t1.TenantId = 12
and t2.wId= 51
All examples I came across are for one clause joins so far. I wrote something like this
actStaList = _db.t1
.Join(_db.t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
)
.Where(a => a.t1.Tenant.Guid == _tenantGuid)
.Select (m=>m.t1.ID)
.ToList();
It is obvious this won't work as the sql query above but still it's a start.
Still I can't figure where should I add the second part inside INNER JOIN and Distinct keyword.
linq lambda
edited Jan 3 at 9:42
piet.t
10.1k73246
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
add a comment |
I'm trying to convert a Sql query to a Linq Lambda style query. Thought this would be something easy but it turned out not.
SQL Query is as follows;
select distinct t1.ID from table1 t1
inner Join table2 t2on (t2.FromId= t1.Id or t2.ToId= t1.Id)
where t1.TenantId = 12
and t2.wId= 51
All examples I came across are for one clause joins so far. I wrote something like this
actStaList = _db.t1
.Join(_db.t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
)
.Where(a => a.t1.Tenant.Guid == _tenantGuid)
.Select (m=>m.t1.ID)
.ToList();
It is obvious this won't work as the sql query above but still it's a start.
Still I can't figure where should I add the second part inside INNER JOIN and Distinct keyword.
linq lambda
edited Jan 3 at 9:42
piet.t
10.1k73246
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
add a comment |
I'm trying to convert a Sql query to a Linq Lambda style query. Thought this would be something easy but it turned out not.
SQL Query is as follows;
select distinct t1.ID from table1 t1
inner Join table2 t2on (t2.FromId= t1.Id or t2.ToId= t1.Id)
where t1.TenantId = 12
and t2.wId= 51
All examples I came across are for one clause joins so far. I wrote something like this
actStaList = _db.t1
.Join(_db.t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
)
.Where(a => a.t1.Tenant.Guid == _tenantGuid)
.Select (m=>m.t1.ID)
.ToList();
It is obvious this won't work as the sql query above but still it's a start.
Still I can't figure where should I add the second part inside INNER JOIN and Distinct keyword.
linq lambda
edited Jan 3 at 9:42
piet.t
10.1k73246
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
I'm trying to convert a Sql query to a Linq Lambda style query. Thought this would be something easy but it turned out not.
SQL Query is as follows;
select distinct t1.ID from table1 t1
inner Join table2 t2on (t2.FromId= t1.Id or t2.ToId= t1.Id)
where t1.TenantId = 12
and t2.wId= 51
All examples I came across are for one clause joins so far. I wrote something like this
actStaList = _db.t1
.Join(_db.t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
)
.Where(a => a.t1.Tenant.Guid == _tenantGuid)
.Select (m=>m.t1.ID)
.ToList();
It is obvious this won't work as the sql query above but still it's a start.
Still I can't figure where should I add the second part inside INNER JOIN and Distinct keyword.
linq lambda
linq lambda
edited Jan 3 at 9:42
piet.t
10.1k73246
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
edited Jan 3 at 9:42
piet.t
10.1k73246
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
edited Jan 3 at 9:42
piet.t
10.1k73246
edited Jan 3 at 9:42
piet.t
10.1k73246
edited Jan 3 at 9:42
piet.t
10.1k73246
10.1k73246
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
asked Jan 3 at 9:20
Ege BayrakEge Bayrak
425825
425825
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
One option you have is to use two separate Linq Queries and concat the result(and eliminating duplicates).
var left = t1.Join(t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var right = t1.Join(t2,
s => s.ID,
wf => wf.FromId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var actStaList = left.Concat(right).Select(m=>m.t1.ID)
.Distinct();
Please note I have omitted the Where Clause in the example as in the OP, both Sql version and your attempted Linq version seem to have different conditions. You can add them yourself.
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up.
– Ege Bayrak
Jan 3 at 9:39
Note that you can doleft.Union(right)and remove.Distinct()because UNION is an implicit DISTINCT.
– Gert Arnold
Jan 3 at 14:28
add a comment |
The LINQ Join statement only supports equi-joins. For other types of equality you can't use the Join statement and have to code the equality manually. This is much easier in query syntax:
actStaList = (
from t1 in _db.table1
from t2 in _db.table2
where t2.FromId == t1.Id || t2.ToId == t1.Id
where t1.TenantId == 12 && t2.wId == 51
select t1.ID
).Distinct();
For the record, you can avoid the Distinct statement by executing this as a SQL EXISTS statement:
actStaList =
from t1 in _db.table1
where t1.TenantId == 12
where (from t2 in _db.table2
where t2.wId == 51 && (t2.FromId == t1.Id || t2.ToId == t1.Id)
select t2).Any()
select t1.ID;
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019356%2finner-join-with-two-equalities-inside-on-clause-in-linq-lambda%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
One option you have is to use two separate Linq Queries and concat the result(and eliminating duplicates).
var left = t1.Join(t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var right = t1.Join(t2,
s => s.ID,
wf => wf.FromId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var actStaList = left.Concat(right).Select(m=>m.t1.ID)
.Distinct();
Please note I have omitted the Where Clause in the example as in the OP, both Sql version and your attempted Linq version seem to have different conditions. You can add them yourself.
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up.
– Ege Bayrak
Jan 3 at 9:39
Note that you can doleft.Union(right)and remove.Distinct()because UNION is an implicit DISTINCT.
– Gert Arnold
Jan 3 at 14:28
add a comment |
One option you have is to use two separate Linq Queries and concat the result(and eliminating duplicates).
var left = t1.Join(t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var right = t1.Join(t2,
s => s.ID,
wf => wf.FromId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var actStaList = left.Concat(right).Select(m=>m.t1.ID)
.Distinct();
Please note I have omitted the Where Clause in the example as in the OP, both Sql version and your attempted Linq version seem to have different conditions. You can add them yourself.
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up.
– Ege Bayrak
Jan 3 at 9:39
Note that you can doleft.Union(right)and remove.Distinct()because UNION is an implicit DISTINCT.
– Gert Arnold
Jan 3 at 14:28
add a comment |
One option you have is to use two separate Linq Queries and concat the result(and eliminating duplicates).
var left = t1.Join(t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var right = t1.Join(t2,
s => s.ID,
wf => wf.FromId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var actStaList = left.Concat(right).Select(m=>m.t1.ID)
.Distinct();
Please note I have omitted the Where Clause in the example as in the OP, both Sql version and your attempted Linq version seem to have different conditions. You can add them yourself.
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
One option you have is to use two separate Linq Queries and concat the result(and eliminating duplicates).
var left = t1.Join(t2,
s => s.ID,
wf => wf.ToId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var right = t1.Join(t2,
s => s.ID,
wf => wf.FromId,
(s, wf) => new { t1= s, t2= wf }
).Select(x=>x);
var actStaList = left.Concat(right).Select(m=>m.t1.ID)
.Distinct();
Please note I have omitted the Where Clause in the example as in the OP, both Sql version and your attempted Linq version seem to have different conditions. You can add them yourself.
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
edited Jan 3 at 9:39
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
answered Jan 3 at 9:38
Anu ViswanAnu Viswan
6,0792526
6,0792526
Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up.
– Ege Bayrak
Jan 3 at 9:39
Note that you can doleft.Union(right)and remove.Distinct()because UNION is an implicit DISTINCT.
– Gert Arnold
Jan 3 at 14:28
add a comment |
Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up.
– Ege Bayrak
Jan 3 at 9:39
Note that you can doleft.Union(right)and remove.Distinct()because UNION is an implicit DISTINCT.
– Gert Arnold
Jan 3 at 14:28
Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up.
– Ege Bayrak
Jan 3 at 9:39
Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up.
– Ege Bayrak
Jan 3 at 9:39
Note that you can do
left.Union(right) and remove .Distinct() because UNION is an implicit DISTINCT.– Gert Arnold
Jan 3 at 14:28
Note that you can do
left.Union(right) and remove .Distinct() because UNION is an implicit DISTINCT.– Gert Arnold
Jan 3 at 14:28
add a comment |
The LINQ Join statement only supports equi-joins. For other types of equality you can't use the Join statement and have to code the equality manually. This is much easier in query syntax:
actStaList = (
from t1 in _db.table1
from t2 in _db.table2
where t2.FromId == t1.Id || t2.ToId == t1.Id
where t1.TenantId == 12 && t2.wId == 51
select t1.ID
).Distinct();
For the record, you can avoid the Distinct statement by executing this as a SQL EXISTS statement:
actStaList =
from t1 in _db.table1
where t1.TenantId == 12
where (from t2 in _db.table2
where t2.wId == 51 && (t2.FromId == t1.Id || t2.ToId == t1.Id)
select t2).Any()
select t1.ID;
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
add a comment |
The LINQ Join statement only supports equi-joins. For other types of equality you can't use the Join statement and have to code the equality manually. This is much easier in query syntax:
actStaList = (
from t1 in _db.table1
from t2 in _db.table2
where t2.FromId == t1.Id || t2.ToId == t1.Id
where t1.TenantId == 12 && t2.wId == 51
select t1.ID
).Distinct();
For the record, you can avoid the Distinct statement by executing this as a SQL EXISTS statement:
actStaList =
from t1 in _db.table1
where t1.TenantId == 12
where (from t2 in _db.table2
where t2.wId == 51 && (t2.FromId == t1.Id || t2.ToId == t1.Id)
select t2).Any()
select t1.ID;
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
add a comment |
The LINQ Join statement only supports equi-joins. For other types of equality you can't use the Join statement and have to code the equality manually. This is much easier in query syntax:
actStaList = (
from t1 in _db.table1
from t2 in _db.table2
where t2.FromId == t1.Id || t2.ToId == t1.Id
where t1.TenantId == 12 && t2.wId == 51
select t1.ID
).Distinct();
For the record, you can avoid the Distinct statement by executing this as a SQL EXISTS statement:
actStaList =
from t1 in _db.table1
where t1.TenantId == 12
where (from t2 in _db.table2
where t2.wId == 51 && (t2.FromId == t1.Id || t2.ToId == t1.Id)
select t2).Any()
select t1.ID;
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
The LINQ Join statement only supports equi-joins. For other types of equality you can't use the Join statement and have to code the equality manually. This is much easier in query syntax:
actStaList = (
from t1 in _db.table1
from t2 in _db.table2
where t2.FromId == t1.Id || t2.ToId == t1.Id
where t1.TenantId == 12 && t2.wId == 51
select t1.ID
).Distinct();
For the record, you can avoid the Distinct statement by executing this as a SQL EXISTS statement:
actStaList =
from t1 in _db.table1
where t1.TenantId == 12
where (from t2 in _db.table2
where t2.wId == 51 && (t2.FromId == t1.Id || t2.ToId == t1.Id)
select t2).Any()
select t1.ID;
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
answered Jan 3 at 10:29
Gert ArnoldGert Arnold
79.9k17141205
79.9k17141205
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019356%2finner-join-with-two-equalities-inside-on-clause-in-linq-lambda%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
