c# get records which exist in each group ASP.NET MVC Entity Framework

user_11411218 0
Viewed 89

Problem: Get users which exists in all entities, like Ali exists in ENT01,ENT02,ENT03

I have a list I want to get users which exist in each group. In given Image detail OfficerName"Ali" exist in all three EntityCode. I need to get users which exist in given list of EntityCodes

result set

List of EntitUser retrieved from Database table. using below query

List<string> EntityCodes = new List<string>();// have 3 EntCodes, can have more

var Contacts=_db.EntityUser.Where(x => x.MasterGroupCode == GroupCode  && x.IsCurrent==true).ToList();

I get a list of EntityUser consisting these properties

public partial class EntityUser
{
        public string Email { get; set; }
        public System.Guid Id { get; set; }
        public string EntityCode { get; set; }
        public string OfficerType { get; set; }
        public string OfficerName { get; set; }
}

Any Help would be much appricated

entity-framework-6 entity-framework asp.net-mvc asp.net c#
1 Answers

If I understand the problem correctly this example should solve your problem.

note: just use your dbContext instead of EntityUsers collection

// sample test data
var EntityUsers = new List<EntityUser>()
{
    new EntityUser(){EntityCode = "ENT01" , OfficerName = "ali"},
    new EntityUser(){EntityCode = "ENT02" , OfficerName = "ali"},
    new EntityUser(){EntityCode = "ENT03" , OfficerName = "ali"},
    new EntityUser(){EntityCode = "ENT01" , OfficerName= "jorj"},
    new EntityUser(){EntityCode = "ENT01" , OfficerName= "reza"},
    new EntityUser(){EntityCode = "ENT01" , OfficerName= "hamid"},
    new EntityUser(){EntityCode = "ENT01" , OfficerName= "farid"},
    new EntityUser(){EntityCode = "ENT02" , OfficerName= "farid"},
    new EntityUser(){EntityCode = "ENT03" , OfficerName= "sasan"},
    new EntityUser(){EntityCode = "ENT02" , OfficerName= "jorj"},
};

// this also can be load from database.  
List<string> entityCodes = new List<string>() { "ENT01" , "ENT02" , "ENT03" }; 

// linq query to select users that has all EntityCodes
var usersInAllEntities = from u in EntityUsers 
                         group u by u.OfficerName into gr
                         where entityCodes.All(c => gr.Any(g => g.EntityCode == c ) )
                         from usr in gr
                         select usr;
user_7863760
Related