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?
dyaroshAsked:
Who is Participating?
 
SriVaddadiCommented:
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 &&
ar.referraltypeids.contains(rm.ReferralTypeID)
select rm;

You could extend it. I would suggest to break into multiple linq queries.
0
 
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 &&
                                                                      ar.referraltypeids.Contains(rt.ReferralTypeID))
                                                   .OrderBy(rt => rt.ReferralTypeName)
                                                   .ToList()
                    select new ReferralScreenModel { Group = dbReferralGroupRecord, Type = dbReferralTypeRecords })
                    .OrderBy(rs => rs.Group.ReferralGroupName).ToList();
0
 
SriVaddadiCommented:
sounds good. Thx for sharing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.