Solved

Left Join LINQ

Posted on 2011-09-02
14
350 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:KaranGupta
  • 6
  • 5
14 Comments
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472717
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,
.....
....                      
};
0
 

Author Comment

by:KaranGupta
ID: 36472742
Hi sonawanekiran

Where can I add where condition
0
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472758
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,
.....
....                      
};
0
 

Author Comment

by:KaranGupta
ID: 36472826
Hi sonawanekiran
How can I put the where condition for FriendsList table


0
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472840
What condition you want?
You can do like this
where MFL.ID == 123
0
 

Author Comment

by:KaranGupta
ID: 36472853
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.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472877
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();
0
 

Author Comment

by:KaranGupta
ID: 36472887
The name 'MFL' does not exist in the current context
0
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472889
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

0
 

Author Comment

by:KaranGupta
ID: 36472908
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.
0
 

Author Comment

by:KaranGupta
ID: 36476944
Hi

any upates
0
 
LVL 19

Accepted Solution

by:
Shahan Ayyub earned 500 total points
ID: 36505241
Hi!

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now