Optional condition in join clause

I have below linq query

 var resultGuardian = from s in _db.Students

     join sg in _db.StudentGuardians on s.StudentID equals sg.StudentID

     join g in _db.Guardians on sg.GuardianId equals g.GuardianId

     join lr in _db.luRelationTypes on sg.RelationTypeID equals lr.RelationTypeID

     join ga in _db.GuardianAddresses on g.GuardianId equals ga.GuardianId

     join ad in _db.Addresses on ga.AddressID equals ad.AddressID

     join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

     join lg in _db.luGenders on g.GenderID equals (int?)lg.GenderID into ssts

     from gdnr in ssts.DefaultIfEmpty()

     where

         s.TenantID == tenantid && sg.TenantID == tenantid && g.TenantID == tenantid &&

         s.StatusId == (int?)Extension.StatusType.Active //1

         && g.StatusID == (int?)Extension.StatusType.Active &&

         lr.StatusID == (int?)Extension.StatusType.Active && lr.TenantID == tenantid &&

         s.StudentID == sid

     select new

     {

         g.FirstName,

         g.LastName,

         IsPrimary = sg.IsPrimaryGuardian,

         g.Education,

         g.Email,

         g.Phone,

         lr.RelationCD,

         ga.IsStudentAddress,

         gdnr.GenderCD,

         lt.AddressName,

         ad.Address1,

         ad.Address2,

         ad.City,

         ad.State,

         ad.Zipcode



     };

In above query when ad.AddressTypeID is null, it is not returning any result.

I have requirement if ad.AddressTypeID is null,than from LuAddressTypes fetch default record where AddressTypeCd=1. If I try this way

join lt in LuAddressTypes on ad.AddressTypeID equals lt.AddressTypeID into v1

from v2 in v1.DefaultIfEmpty()

select new

     {      

         v2.AddressName,

         g.Phone,..



     });

in result v2.AddressName always returning null. I am unable to specify AddressTypeCd=1 where condition as well. AddressTypeCd=1 is not ad.AddressTypeID.

I need v2.AddressName where AddressTypeCd=1. How can I do that?
Find related entities all related entities

edited Nov 22 '18 at 12:35

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

search for linq left outer join.

– Access Denied
Nov 22 '18 at 10:21

Possible duplicate of LEFT OUTER JOIN in LINQ

– Access Denied
Nov 22 '18 at 10:22

@AccessDenied - Thank you for your suggestion.

– CGPA6.4
Nov 22 '18 at 10:44

Please post the involved entity classes. Also fix the sample query - currently it's using some not existing aliases - ga, g, lr.

– Ivan Stoev
Nov 22 '18 at 11:24

@IvanStoev - Sure, doing it.

– CGPA6.4
Nov 22 '18 at 11:25

|
show 7 more comments

I have below linq query

 var resultGuardian = from s in _db.Students

     join sg in _db.StudentGuardians on s.StudentID equals sg.StudentID

     join g in _db.Guardians on sg.GuardianId equals g.GuardianId

     join lr in _db.luRelationTypes on sg.RelationTypeID equals lr.RelationTypeID

     join ga in _db.GuardianAddresses on g.GuardianId equals ga.GuardianId

     join ad in _db.Addresses on ga.AddressID equals ad.AddressID

     join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

     join lg in _db.luGenders on g.GenderID equals (int?)lg.GenderID into ssts

     from gdnr in ssts.DefaultIfEmpty()

     where

         s.TenantID == tenantid && sg.TenantID == tenantid && g.TenantID == tenantid &&

         s.StatusId == (int?)Extension.StatusType.Active //1

         && g.StatusID == (int?)Extension.StatusType.Active &&

         lr.StatusID == (int?)Extension.StatusType.Active && lr.TenantID == tenantid &&

         s.StudentID == sid

     select new

     {

         g.FirstName,

         g.LastName,

         IsPrimary = sg.IsPrimaryGuardian,

         g.Education,

         g.Email,

         g.Phone,

         lr.RelationCD,

         ga.IsStudentAddress,

         gdnr.GenderCD,

         lt.AddressName,

         ad.Address1,

         ad.Address2,

         ad.City,

         ad.State,

         ad.Zipcode



     };

In above query when ad.AddressTypeID is null, it is not returning any result.

I have requirement if ad.AddressTypeID is null,than from LuAddressTypes fetch default record where AddressTypeCd=1. If I try this way

join lt in LuAddressTypes on ad.AddressTypeID equals lt.AddressTypeID into v1

from v2 in v1.DefaultIfEmpty()

select new

     {      

         v2.AddressName,

         g.Phone,..



     });

in result v2.AddressName always returning null. I am unable to specify AddressTypeCd=1 where condition as well. AddressTypeCd=1 is not ad.AddressTypeID.

I need v2.AddressName where AddressTypeCd=1. How can I do that?
Find related entities all related entities

edited Nov 22 '18 at 12:35

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

search for linq left outer join.

– Access Denied
Nov 22 '18 at 10:21

Possible duplicate of LEFT OUTER JOIN in LINQ

– Access Denied
Nov 22 '18 at 10:22

@AccessDenied - Thank you for your suggestion.

– CGPA6.4
Nov 22 '18 at 10:44

Please post the involved entity classes. Also fix the sample query - currently it's using some not existing aliases - ga, g, lr.

– Ivan Stoev
Nov 22 '18 at 11:24

@IvanStoev - Sure, doing it.

– CGPA6.4
Nov 22 '18 at 11:25

|
show 7 more comments

I have below linq query

 var resultGuardian = from s in _db.Students

     join sg in _db.StudentGuardians on s.StudentID equals sg.StudentID

     join g in _db.Guardians on sg.GuardianId equals g.GuardianId

     join lr in _db.luRelationTypes on sg.RelationTypeID equals lr.RelationTypeID

     join ga in _db.GuardianAddresses on g.GuardianId equals ga.GuardianId

     join ad in _db.Addresses on ga.AddressID equals ad.AddressID

     join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

     join lg in _db.luGenders on g.GenderID equals (int?)lg.GenderID into ssts

     from gdnr in ssts.DefaultIfEmpty()

     where

         s.TenantID == tenantid && sg.TenantID == tenantid && g.TenantID == tenantid &&

         s.StatusId == (int?)Extension.StatusType.Active //1

         && g.StatusID == (int?)Extension.StatusType.Active &&

         lr.StatusID == (int?)Extension.StatusType.Active && lr.TenantID == tenantid &&

         s.StudentID == sid

     select new

     {

         g.FirstName,

         g.LastName,

         IsPrimary = sg.IsPrimaryGuardian,

         g.Education,

         g.Email,

         g.Phone,

         lr.RelationCD,

         ga.IsStudentAddress,

         gdnr.GenderCD,

         lt.AddressName,

         ad.Address1,

         ad.Address2,

         ad.City,

         ad.State,

         ad.Zipcode



     };

In above query when ad.AddressTypeID is null, it is not returning any result.

I have requirement if ad.AddressTypeID is null,than from LuAddressTypes fetch default record where AddressTypeCd=1. If I try this way

join lt in LuAddressTypes on ad.AddressTypeID equals lt.AddressTypeID into v1

from v2 in v1.DefaultIfEmpty()

select new

     {      

         v2.AddressName,

         g.Phone,..



     });

in result v2.AddressName always returning null. I am unable to specify AddressTypeCd=1 where condition as well. AddressTypeCd=1 is not ad.AddressTypeID.

I need v2.AddressName where AddressTypeCd=1. How can I do that?
Find related entities all related entities

edited Nov 22 '18 at 12:35

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

I have below linq query

 var resultGuardian = from s in _db.Students

     join sg in _db.StudentGuardians on s.StudentID equals sg.StudentID

     join g in _db.Guardians on sg.GuardianId equals g.GuardianId

     join lr in _db.luRelationTypes on sg.RelationTypeID equals lr.RelationTypeID

     join ga in _db.GuardianAddresses on g.GuardianId equals ga.GuardianId

     join ad in _db.Addresses on ga.AddressID equals ad.AddressID

     join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

     join lg in _db.luGenders on g.GenderID equals (int?)lg.GenderID into ssts

     from gdnr in ssts.DefaultIfEmpty()

     where

         s.TenantID == tenantid && sg.TenantID == tenantid && g.TenantID == tenantid &&

         s.StatusId == (int?)Extension.StatusType.Active //1

         && g.StatusID == (int?)Extension.StatusType.Active &&

         lr.StatusID == (int?)Extension.StatusType.Active && lr.TenantID == tenantid &&

         s.StudentID == sid

     select new

     {

         g.FirstName,

         g.LastName,

         IsPrimary = sg.IsPrimaryGuardian,

         g.Education,

         g.Email,

         g.Phone,

         lr.RelationCD,

         ga.IsStudentAddress,

         gdnr.GenderCD,

         lt.AddressName,

         ad.Address1,

         ad.Address2,

         ad.City,

         ad.State,

         ad.Zipcode



     };

In above query when ad.AddressTypeID is null, it is not returning any result.

I have requirement if ad.AddressTypeID is null,than from LuAddressTypes fetch default record where AddressTypeCd=1. If I try this way

join lt in LuAddressTypes on ad.AddressTypeID equals lt.AddressTypeID into v1

from v2 in v1.DefaultIfEmpty()

select new

     {      

         v2.AddressName,

         g.Phone,..



     });

in result v2.AddressName always returning null. I am unable to specify AddressTypeCd=1 where condition as well. AddressTypeCd=1 is not ad.AddressTypeID.

I need v2.AddressName where AddressTypeCd=1. How can I do that?
Find related entities all related entities

c# .net linq linq-to-entities

edited Nov 22 '18 at 12:35

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

edited Nov 22 '18 at 12:35

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

edited Nov 22 '18 at 12:35

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

asked Nov 22 '18 at 10:09

CGPA6.4

2,85121031

search for linq left outer join.

– Access Denied
Nov 22 '18 at 10:21

Possible duplicate of LEFT OUTER JOIN in LINQ

– Access Denied
Nov 22 '18 at 10:22

@AccessDenied - Thank you for your suggestion.

– CGPA6.4
Nov 22 '18 at 10:44

Please post the involved entity classes. Also fix the sample query - currently it's using some not existing aliases - ga, g, lr.

– Ivan Stoev
Nov 22 '18 at 11:24

@IvanStoev - Sure, doing it.

– CGPA6.4
Nov 22 '18 at 11:25

|
show 7 more comments

search for linq left outer join.

– Access Denied
Nov 22 '18 at 10:21

Possible duplicate of LEFT OUTER JOIN in LINQ

– Access Denied
Nov 22 '18 at 10:22

@AccessDenied - Thank you for your suggestion.

– CGPA6.4
Nov 22 '18 at 10:44

Please post the involved entity classes. Also fix the sample query - currently it's using some not existing aliases - ga, g, lr.

– Ivan Stoev
Nov 22 '18 at 11:24

@IvanStoev - Sure, doing it.

– CGPA6.4
Nov 22 '18 at 11:25

search for linq left outer join.

– Access Denied
Nov 22 '18 at 10:21

Possible duplicate of LEFT OUTER JOIN in LINQ

– Access Denied
Nov 22 '18 at 10:22

@AccessDenied - Thank you for your suggestion.

– CGPA6.4
Nov 22 '18 at 10:44

Please post the involved entity classes. Also fix the sample query - currently it's using some not existing aliases - ga, g, lr.

– Ivan Stoev
Nov 22 '18 at 11:24

@IvanStoev - Sure, doing it.

– CGPA6.4
Nov 22 '18 at 11:25

|
show 7 more comments

1 Answer
1

active

oldest

votes

You can't use the standard LINQ join, but in LINQ to Entities you could use the alternative join syntax based on correlated Where - EF is smart enough to translate it to JOIN.

In your case, instead of

join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

you could use

from lt in _db.luAddressTypes.Where(lt => ad.AddressTypeID == lt.AddressTypeID

    || (ad.AddressTypeID == null && lt.AddressTypeCd == 1))

which is translated to something like this

INNER JOIN [dbo].[LuAddressTypes] AS [Extent3]

    ON ([Extent2].[AddressTypeID] = [Extent3].[AddressTypeID])

        OR (([Extent2].[AddressTypeID] IS NULL) AND (1 = [Extent3].[AddressTypeCd]))

If you want LEFT OUTER JOIN, simply add .DefaultIfEmpty() at the end of the above line.

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

Thank you for your answer. It is returning multiple records. Can you please suggest me how to avoid this.

– CGPA6.4
Nov 22 '18 at 13:13

Add .Take(1) :) It would change the JOIN to CROSS APPLY, but the effect should be the same.

– Ivan Stoev
Nov 22 '18 at 13:31

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53428500%2foptional-condition-in-join-clause-linq%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You can't use the standard LINQ join, but in LINQ to Entities you could use the alternative join syntax based on correlated Where - EF is smart enough to translate it to JOIN.

In your case, instead of

join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

you could use

from lt in _db.luAddressTypes.Where(lt => ad.AddressTypeID == lt.AddressTypeID

    || (ad.AddressTypeID == null && lt.AddressTypeCd == 1))

which is translated to something like this

INNER JOIN [dbo].[LuAddressTypes] AS [Extent3]

    ON ([Extent2].[AddressTypeID] = [Extent3].[AddressTypeID])

        OR (([Extent2].[AddressTypeID] IS NULL) AND (1 = [Extent3].[AddressTypeCd]))

If you want LEFT OUTER JOIN, simply add .DefaultIfEmpty() at the end of the above line.

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

Thank you for your answer. It is returning multiple records. Can you please suggest me how to avoid this.

– CGPA6.4
Nov 22 '18 at 13:13

Add .Take(1) :) It would change the JOIN to CROSS APPLY, but the effect should be the same.

– Ivan Stoev
Nov 22 '18 at 13:31

add a comment |

You can't use the standard LINQ join, but in LINQ to Entities you could use the alternative join syntax based on correlated Where - EF is smart enough to translate it to JOIN.

In your case, instead of

join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

you could use

from lt in _db.luAddressTypes.Where(lt => ad.AddressTypeID == lt.AddressTypeID

    || (ad.AddressTypeID == null && lt.AddressTypeCd == 1))

which is translated to something like this

INNER JOIN [dbo].[LuAddressTypes] AS [Extent3]

    ON ([Extent2].[AddressTypeID] = [Extent3].[AddressTypeID])

        OR (([Extent2].[AddressTypeID] IS NULL) AND (1 = [Extent3].[AddressTypeCd]))

If you want LEFT OUTER JOIN, simply add .DefaultIfEmpty() at the end of the above line.

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

Thank you for your answer. It is returning multiple records. Can you please suggest me how to avoid this.

– CGPA6.4
Nov 22 '18 at 13:13

Add .Take(1) :) It would change the JOIN to CROSS APPLY, but the effect should be the same.

– Ivan Stoev
Nov 22 '18 at 13:31

add a comment |

You can't use the standard LINQ join, but in LINQ to Entities you could use the alternative join syntax based on correlated Where - EF is smart enough to translate it to JOIN.

In your case, instead of

join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

you could use

from lt in _db.luAddressTypes.Where(lt => ad.AddressTypeID == lt.AddressTypeID

    || (ad.AddressTypeID == null && lt.AddressTypeCd == 1))

which is translated to something like this

INNER JOIN [dbo].[LuAddressTypes] AS [Extent3]

    ON ([Extent2].[AddressTypeID] = [Extent3].[AddressTypeID])

        OR (([Extent2].[AddressTypeID] IS NULL) AND (1 = [Extent3].[AddressTypeCd]))

If you want LEFT OUTER JOIN, simply add .DefaultIfEmpty() at the end of the above line.

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

You can't use the standard LINQ join, but in LINQ to Entities you could use the alternative join syntax based on correlated Where - EF is smart enough to translate it to JOIN.

In your case, instead of

join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

you could use

from lt in _db.luAddressTypes.Where(lt => ad.AddressTypeID == lt.AddressTypeID

    || (ad.AddressTypeID == null && lt.AddressTypeCd == 1))

which is translated to something like this

INNER JOIN [dbo].[LuAddressTypes] AS [Extent3]

    ON ([Extent2].[AddressTypeID] = [Extent3].[AddressTypeID])

        OR (([Extent2].[AddressTypeID] IS NULL) AND (1 = [Extent3].[AddressTypeCd]))

If you want LEFT OUTER JOIN, simply add .DefaultIfEmpty() at the end of the above line.

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

answered Nov 22 '18 at 13:08

Ivan Stoev

106k779129

Thank you for your answer. It is returning multiple records. Can you please suggest me how to avoid this.

– CGPA6.4
Nov 22 '18 at 13:13

Add .Take(1) :) It would change the JOIN to CROSS APPLY, but the effect should be the same.

– Ivan Stoev
Nov 22 '18 at 13:31

add a comment |

Thank you for your answer. It is returning multiple records. Can you please suggest me how to avoid this.

– CGPA6.4
Nov 22 '18 at 13:13

Add .Take(1) :) It would change the JOIN to CROSS APPLY, but the effect should be the same.

– Ivan Stoev
Nov 22 '18 at 13:31

Thank you for your answer. It is returning multiple records. Can you please suggest me how to avoid this.

– CGPA6.4
Nov 22 '18 at 13:13

Add .Take(1) :) It would change the JOIN to CROSS APPLY, but the effect should be the same.

– Ivan Stoev
Nov 22 '18 at 13:31

add a comment |

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu