Left Join LINQ

KaranGupta
KaranGupta used Ask the Experts™
on
from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID

Open in new window


Is this situation sufficient for left join in LINQ.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kiran SonawaneProject Lead
Top Expert 2011

Commented:
var result = from M in Member
join MFL in FriendsList
on M.MemberID equals MFL.FriendID into MemberFrdList
from MF in MemberFrdList.DefaultIfEmpty()
select new                          
{
MemberName = MF.Name,
.....
....                      
};

Author

Commented:
Hi sonawanekiran

Where can I add where condition
Kiran SonawaneProject Lead
Top Expert 2011

Commented:
var result = from M in Member
join MFL in FriendsList
on M.MemberID equals MFL.FriendID into MemberFrdList
// here condition goes like where M.name =="MyName"
from MF in MemberFrdList.DefaultIfEmpty()
select new                          
{
MemberName = MF.Name,
.....
....                      
};
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi sonawanekiran
How can I put the where condition for FriendsList table


Kiran SonawaneProject Lead
Top Expert 2011

Commented:
What condition you want?
You can do like this
where MFL.ID == 123

Author

Commented:
Hi

var searchResult = (from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID into MemberList
                                where (M.MemberFullName.Contains(searchString) || 
                                            M.MemberEmailID.Contains(searchString))
                                            && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID
                                            && MFL.MemberID == ConfigurationKeys.MemberID
                                from result in MemberList.DefaultIfEmpty() 
                                select new {M.MemberID,M.MemberFullName,M.MemberEmailID,result.IsRelationApproved});

Open in new window


I am using this query to get the value from 2 tables, but I am getting the error. Secondly once we get the records in the variable searchresult how can I bind it to a gridview.
Kiran SonawaneProject Lead
Top Expert 2011

Commented:
1) What error you are getting
2)

1:
2:
3:
4:
5:
6:
7:
8:
9:
var searchResult = (from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID into MemberList
                                where (M.MemberFullName.Contains(searchString) ||
                                            M.MemberEmailID.Contains(searchString))
                                            && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID
                                            && MFL.MemberID == ConfigurationKeys.MemberID
                                from result in MemberList.DefaultIfEmpty()
                                select new {M.MemberID,M.MemberFullName,M.MemberEmailID,result.IsRelationApproved}).ToList();


GridView1.DataSource = searchResult;
GridView1.DataBind();

Author

Commented:
The name 'MFL' does not exist in the current context
Kiran SonawaneProject Lead
Top Expert 2011

Commented:
I think there is syntax error at where condition .
Try this
(from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID into MemberList
                                where (
                                            (M.MemberFullName.Contains(searchString) || M.MemberEmailID.Contains(searchString)) 
                                            && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID
                                            && MFL.MemberID == ConfigurationKeys.MemberID
                                      )
                                            
                                from result in MemberList.DefaultIfEmpty() 
                                select new {M.MemberID,M.MemberFullName,M.MemberEmailID,result.IsRelationApproved});

Open in new window

Author

Commented:
Hi

I am getting the same error again but when I change the code to

 var searchResult = (from M in Member
         join MFL in FriendsList
             on M.MemberID equals MFL.FriendID into MemberList
         where (
                     (M.MemberFullName.Contains(searchString) || M.MemberEmailID.Contains(searchString))
                     && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID                     
               )
                            from result in MemberList.DefaultIfEmpty()
                            where result.MemberID == ConfigurationKeys.MemberID
                select new { M.MemberID, M.MemberFullName, M.MemberEmailID, result.IsRelationApproved }).ToList();

Open in new window


then I am not getting any compile time error but I am getting runtime error
LINQ to Entities does not recognize the method
'System.Collections.Generic.IEnumerable`1[Model.FriendsList] DefaultIfEmpty[FriendsList](System.Collections.Generic.IEnumerable`1[Model.FriendsList])' method, and this method cannot be translated into a store expression.

Author

Commented:
Hi

any upates
Senior Software Engineer
Commented:
Hi!

Why not use "Left" keyword with join like this:
http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial