Complex lambda expression in entity framework linq to sql

Here is my problem:

My system has a bunch of referral groups.  Each referral group has a list of associated referral types with the referral group.

Each referral group can have an authorization code which is SQL that determines whether the user has access to the referral group.  If the SQL returns a record the user has access otherwise they don't.  If a user doesn't have access to a referral group, the user can not access any of the referral types within that referral group.

Each referral type within a referral group can have an authorization code which is SQL that determines whether the user has access to the referral type.  If the SQL returns a record the user has access otherwise they don't.  In theory it is possible for a user to have access to the referral group but not be granted access to the referral types within that referral group (probably wouldn't happen in production).

I have code that creates a List of Authorized Referrals.  Here is my definition for Authorized Referrals:
public class AuthorizedReferral
     public int referralgroupid;
     List<int> referraltypeids;

List<AuthorizedReferral> authorizedreferrals = GetAuthorizedReferrals();

Open in new window

What I want to do now is to use the data in authorizedreferrals to retrieve the Referral Group and Referral Type information for the referralgroupid and referraltypeids but I can't quite figure out how to do it.  Here is what I am working with:
var referralmenu = _db.REF_ReferralGroup
                                       .Include(rt => rt.REF_ReferralType)
                                       .Where(rg => rg.ReferralGroupStatusFlag == true && <here is where I need help> == rg.ReferralGroupID)
                                       .OrderBy(rg => rg.ReferralGroupName)
                                       .Select(rsm => new ReferralScreenModel
                                                 Group = rg,
                                                 Type = rg.REF_ReferralType,
                                                 .Where( x => x.ReferralTypeActiveStatusFlag == true & <here is where I need help> == x.ReferralTypeID)
                                                 .OrderBy(x => x.ReferralTypeName)

Open in new window

How do I tell it to restrict the Referral Groups selected to those referral groups in the authorizedreferrals list whose referralgroupid matches the referralgroupid of the data returned?

How do I do the same thing for the Referral Types?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How about writing something like below

var referralMenu = _db.REF_ReferralGroup .Include(rt => rt.REF_ReferralType).ToList();

var authorizeReferrals =
from rm in referralMenu
join ar in authorizedreferrals
on rm.ReferralGroupID equals ar.referralgroupid &&
select rm;

You could extend it. I would suggest to break into multiple linq queries.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dyaroshAuthor Commented:
Here is what I ended up using and it works for me.  Thanks.

            return (from ar in referralGroups
                    let dbReferralGroupRecord = _db.REF_ReferralGroup.SingleOrDefault(rg => rg.ReferralGroupID == ar.referralgroupid)
                    where dbReferralGroupRecord != null
                    let dbReferralTypeRecords = _db.REF_ReferralType
                                                   .Where(rt => rt.REF_ReferralGroupReferralGroupID == ar.referralgroupid &&
                                                   .OrderBy(rt => rt.ReferralTypeName)
                    select new ReferralScreenModel { Group = dbReferralGroupRecord, Type = dbReferralTypeRecords })
                    .OrderBy(rs => rs.Group.ReferralGroupName).ToList();
sounds good. Thx for sharing.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.