Solved

Left Join LINQ

Posted on 2011-09-02
14
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

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
 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Web Add-in Where is Visual Basic used 9 71
C# DateTime Help 6 46
Read top line from CSV file 14 56
modify font on ninja form 1 19
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…
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

738 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