Query In Linq using joins

Question

I have a query from sql but I can't seem to make it work in linq here is the query from sql server:

SELECT     MembersBU.MemberID, MembersBU.MemberFirst, MembersBU.MemberLast,
           MembersBU.MemberEmail, MembersBU.ValidEmail, 
                  MembersBU.EmailCap
FROM         groupMembers INNER JOIN
                  MembersBU ON groupMembers.MemID = MembersBU.MemberID
WHERE     (groupMembers.groupID = 3) AND (MembersBU.EmailCap = 1)

and here is the linq query:

 var query = from M in MembersBUs
 join GM in GroupMembers on M.MemberID equals GM.MemID
 where (GM.GroupID = 3) & (M.EmailCap = 1)
 select new {M.MemberID, M.MemberFirst, M.MemberLast, 
             M.MemberEmail, M.ValidEmail, M.EmailCap};

any help is appreciated.

Answer 1

You just need to use && instead of & and == instead of =. Otherwise, your Linq query seems OK to me...

Answer 2

var query = from M in MembersBUs 
join GM in GroupMembers on M.MemberID equals GM.MemID 
where (GM.GroupID == 3) && (M.EmailCap == 1) 
select new {M.MemberID, M.MemberFirst, M.MemberLast,  
         M.MemberEmail, M.ValidEmail, M.EmailCap}; 

The where clause uses normal C# syntax, == for equality and && of logical AND

Also, if you have the proper foreign-key relationship set up for the tables, you don't need to explcited state the join. Linq will infer it automatically:

var query = from M in MembersBUs 
where (M.GroupMember.GroupID == 3) && (M.EmailCap == 1) 
select new {M.MemberID, M.MemberFirst, M.MemberLast,  
         M.MemberEmail, M.ValidEmail, M.EmailCap}; 

Answer 3

You can't use sql style where clause! You must use the language style. It looks like c# - you need to use == instead:

 var query = from M in MembersBUs
 join GM in GroupMembers on M.MemberID equals GM.MemID
 where (GM.GroupID == 3) && (M.EmailCap == 1)
 select new {M.MemberID, M.MemberFirst, M.MemberLast, 
             M.MemberEmail, M.ValidEmail, M.EmailCap};

And && instead of &.

It might be easier to use this query - because it's not an anonymous type:

IQueryable<MembersBU> = from M in MembersBUs 
     where (M.GroupMember.GroupID == 3) && (M.EmailCap == 1) 
     select M;