CODEWITHSUNDEEP
c#sqllinqentity-framework
Slight
Slight

Reputation: 1582

How to query with a list against EntityFramework classes

I know linq a bit, but I need to be able to have a list populated by code and "iterate" over it with a linq expression so to speak and compare it to DB rows.

Here is my current code

        foreach (IdentityReference group in windowsIdentity.Groups)
        {
            string groupName = group.Translate(typeof(NTAccount)).ToString();

            //Query the db for the role(s) associated with this groupname
            var query = from adGroup in AuthDbContext.ADGroups
                        from adGroupRole in AuthDbContext.ADGroupRoles
                        where adGroup.Name == groupName
                            && adGroup.Id == adGroupRole.ADGroupId
                        select adGroupRole.Role.Name
                        ;

            //Add any found roles as claims to be added to the identity
            foreach (string Role in query)
            {
                claims.Add(new Claim(ClaimTypes.Role, Role));
            }
        }

But I want to eliminate the first foreach loop by generating a string[] (from windowsIdentity.Groups) and somehow using it in linq to compare each string entry (groupname) against the where clause.

I want to do this because I presume every time that query is run it hits the DB which means if there are 50 groups, it will hit the db 50 times. If its all in one expression I assume it will only hit the DB once.

What is the proper Linq query syntax to do this?

Upvotes: 0

Views: 146

Answers (1)

Florian Schmidinger
Florian Schmidinger

Reputation: 4692

On closer look:

string[] groupNames = windowsIdentity.Groups.Select(g=> g.Translate(typeof(NTAccount)).ToString()).ToArray();

//Query the db for the role(s) associated with this groupname
var query = from adGroup in AuthDbContext.ADGroups
            join adGroupRole in AuthDbContext.ADGroupRoles on adGroup.Id equals adGroupRole.ADGroupId
            where groupNames.Contains(adGroup.Name)
            select adGroupRole.Role.Name;

//Add any found roles as claims to be added to the identity
foreach (string Role in query)
{
    claims.Add(new Claim(ClaimTypes.Role, Role));
}

Assuming claims is a List<Claim> or some supertype of Claim you can add them all at once using:

claims.AddRange(query.Select(r=> new Claim(ClaimTypes.Role, r)));

Upvotes: 4

Related Questions