Mixing
How to use Left Join in My LINQ to EF query?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
My LINQ to EF query is,
var query = (from r in DB.region
join g in DB.governorate on r.region_id equals g.region_id
join s in DB.IndividualReports on g.governorate_id equals s.governorate_id
join t in DB.AgencyReports on g.governorate_id equals t.governorate_id
group new { r, s, t } by new { r.region_id, r.region_name }
into grp
select new
{
RId= grp.Key.region_id,
RName=grp.Key.region_name,
IndividualCount = grp.Select(s => s.s.Id).Distinct().Count(),
AgencyCount = grp.Select(t => t.t.Id).Distinct().Count()
}
).ToList().Select(s => new CitysWithComplaintsall
{
CityId = s.RId,
City_NM = s.RName,
Num_Complaints_Agency = s.IndividualCount,
Num_Complaints_Ind = s.AgencyCount
}).ToList();
I want to add left join everywhere instead of simple join.
Same query in SQL Server with left join is like this:
select
R.region_id, R.region_name,
count(IR.Id) as Individual,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
IndividualReports IR on g.governorate_id = IR.governorate_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
One more thing is that this raw SQL query returns some different result if I try to change this query for checking count make changing it to:
select
R.region_id, R.region_name,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
SQL queries return slightly different result.
asp.net
sql-server linq linq-to-entities left-join edited Jan 3 at 21:14
marc_s
585k13011251272
asked Jan 3 at 11:53
john derikjohn derik
378
add a comment |
0
My LINQ to EF query is,
var query = (from r in DB.region
join g in DB.governorate on r.region_id equals g.region_id
join s in DB.IndividualReports on g.governorate_id equals s.governorate_id
join t in DB.AgencyReports on g.governorate_id equals t.governorate_id
group new { r, s, t } by new { r.region_id, r.region_name }
into grp
select new
{
RId= grp.Key.region_id,
RName=grp.Key.region_name,
IndividualCount = grp.Select(s => s.s.Id).Distinct().Count(),
AgencyCount = grp.Select(t => t.t.Id).Distinct().Count()
}
).ToList().Select(s => new CitysWithComplaintsall
{
CityId = s.RId,
City_NM = s.RName,
Num_Complaints_Agency = s.IndividualCount,
Num_Complaints_Ind = s.AgencyCount
}).ToList();
I want to add left join everywhere instead of simple join.
Same query in SQL Server with left join is like this:
select
R.region_id, R.region_name,
count(IR.Id) as Individual,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
IndividualReports IR on g.governorate_id = IR.governorate_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
One more thing is that this raw SQL query returns some different result if I try to change this query for checking count make changing it to:
select
R.region_id, R.region_name,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
SQL queries return slightly different result.
asp.net
sql-server linq linq-to-entities left-join edited Jan 3 at 21:14
marc_s
585k13011251272
asked Jan 3 at 11:53
john derikjohn derik
378
1
You're almost repeating this question. What was not clear in the suggested duplicate there? It perfectly indicates how to do left outer join in LINQ. I also recommended you use navigation properties instead of joins. You don't seem to pay much attention to input from the developer community.
– Gert Arnold
Jan 3 at 12:27
1
Possible duplicate of LEFT OUTER JOIN in LINQ
– Tetsuya Yamamoto
Jan 4 at 2:05
add a comment |
0
0
0
My LINQ to EF query is,
var query = (from r in DB.region
join g in DB.governorate on r.region_id equals g.region_id
join s in DB.IndividualReports on g.governorate_id equals s.governorate_id
join t in DB.AgencyReports on g.governorate_id equals t.governorate_id
group new { r, s, t } by new { r.region_id, r.region_name }
into grp
select new
{
RId= grp.Key.region_id,
RName=grp.Key.region_name,
IndividualCount = grp.Select(s => s.s.Id).Distinct().Count(),
AgencyCount = grp.Select(t => t.t.Id).Distinct().Count()
}
).ToList().Select(s => new CitysWithComplaintsall
{
CityId = s.RId,
City_NM = s.RName,
Num_Complaints_Agency = s.IndividualCount,
Num_Complaints_Ind = s.AgencyCount
}).ToList();
I want to add left join everywhere instead of simple join.
Same query in SQL Server with left join is like this:
select
R.region_id, R.region_name,
count(IR.Id) as Individual,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
IndividualReports IR on g.governorate_id = IR.governorate_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
One more thing is that this raw SQL query returns some different result if I try to change this query for checking count make changing it to:
select
R.region_id, R.region_name,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
SQL queries return slightly different result.
asp.net
sql-server linq linq-to-entities left-join edited Jan 3 at 21:14
marc_s
585k13011251272
asked Jan 3 at 11:53
john derikjohn derik
378
My LINQ to EF query is,
var query = (from r in DB.region
join g in DB.governorate on r.region_id equals g.region_id
join s in DB.IndividualReports on g.governorate_id equals s.governorate_id
join t in DB.AgencyReports on g.governorate_id equals t.governorate_id
group new { r, s, t } by new { r.region_id, r.region_name }
into grp
select new
{
RId= grp.Key.region_id,
RName=grp.Key.region_name,
IndividualCount = grp.Select(s => s.s.Id).Distinct().Count(),
AgencyCount = grp.Select(t => t.t.Id).Distinct().Count()
}
).ToList().Select(s => new CitysWithComplaintsall
{
CityId = s.RId,
City_NM = s.RName,
Num_Complaints_Agency = s.IndividualCount,
Num_Complaints_Ind = s.AgencyCount
}).ToList();
I want to add left join everywhere instead of simple join.
Same query in SQL Server with left join is like this:
select
R.region_id, R.region_name,
count(IR.Id) as Individual,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
IndividualReports IR on g.governorate_id = IR.governorate_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
One more thing is that this raw SQL query returns some different result if I try to change this query for checking count make changing it to:
select
R.region_id, R.region_name,
count(AR.Id) as Agency
from
region R
left join
governorate G on r.region_id = g.region_id
left join
AgencyReports AR on g.governorate_id = AR.governorate_id
group by
R.region_id, R.region_name
SQL queries return slightly different result.
asp.net
sql-server linq linq-to-entities left-join asp.net
sql-server linq linq-to-entities left-join edited Jan 3 at 21:14
marc_s
585k13011251272
asked Jan 3 at 11:53
john derikjohn derik
378
edited Jan 3 at 21:14
marc_s
585k13011251272
asked Jan 3 at 11:53
john derikjohn derik
378
edited Jan 3 at 21:14
marc_s
585k13011251272
edited Jan 3 at 21:14
marc_s
585k13011251272
edited Jan 3 at 21:14
marc_s
585k13011251272
585k13011251272
asked Jan 3 at 11:53
john derikjohn derik
378
asked Jan 3 at 11:53
john derikjohn derik
378
asked Jan 3 at 11:53
john derikjohn derik
378
378
1
You're almost repeating this question. What was not clear in the suggested duplicate there? It perfectly indicates how to do left outer join in LINQ. I also recommended you use navigation properties instead of joins. You don't seem to pay much attention to input from the developer community.
– Gert Arnold
Jan 3 at 12:27
1
Possible duplicate of LEFT OUTER JOIN in LINQ
– Tetsuya Yamamoto
Jan 4 at 2:05
add a comment |
1
You're almost repeating this question. What was not clear in the suggested duplicate there? It perfectly indicates how to do left outer join in LINQ. I also recommended you use navigation properties instead of joins. You don't seem to pay much attention to input from the developer community.
– Gert Arnold
Jan 3 at 12:27
1
Possible duplicate of LEFT OUTER JOIN in LINQ
– Tetsuya Yamamoto
Jan 4 at 2:05
1
1
You're almost repeating this question. What was not clear in the suggested duplicate there? It perfectly indicates how to do left outer join in LINQ. I also recommended you use navigation properties instead of joins. You don't seem to pay much attention to input from the developer community.
– Gert Arnold
Jan 3 at 12:27
You're almost repeating this question. What was not clear in the suggested duplicate there? It perfectly indicates how to do left outer join in LINQ. I also recommended you use navigation properties instead of joins. You don't seem to pay much attention to input from the developer community.
– Gert Arnold
Jan 3 at 12:27
1
1
Possible duplicate of LEFT OUTER JOIN in LINQ
– Tetsuya Yamamoto
Jan 4 at 2:05
Possible duplicate of LEFT OUTER JOIN in LINQ
– Tetsuya Yamamoto
Jan 4 at 2:05
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f54021776%2fhow-to-use-left-join-in-my-linq-to-ef-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54021776%2fhow-to-use-left-join-in-my-linq-to-ef-query%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
1
You're almost repeating this question. What was not clear in the suggested duplicate there? It perfectly indicates how to do left outer join in LINQ. I also recommended you use navigation properties instead of joins. You don't seem to pay much attention to input from the developer community.
– Gert Arnold
Jan 3 at 12:27
1
Possible duplicate of LEFT OUTER JOIN in LINQ
– Tetsuya Yamamoto
Jan 4 at 2:05