Example petapoco use

gistfile1.cs

    public class Security
    {
        public int SecurityId { get; set; }
        public string Name { get; set; }
    }

    public class User
    {
        public int UserId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public bool Active { get; set; }
    }

    public class UserSecurity
    {
        public int UserId { get; set; }
        public int SecurityId { get; set; }
    }

    public class Queries
    {
        private IDatabase db = new Database("peta");

        public List<User> GetUsersWithSecurityName(string name)
        {
            var sql = @"select u.* from users u
inner join usersecurity us on u.userid = us.userid
inner join security s on us.securityid = s.securityid
where s.name = @name";

            return db.Fetch<User>(sql, new {name});
        }

        public List<UserSecurityViewModel.UserWithSecurity> GetUsersWithSecurity()
        {
            var sql = @"select u.* from users u
inner join usersecurity us on u.userid = us.userid
inner join security s on us.securityid = s.securityid";

            var flat = db.Fetch<UserIdWithSecurityFlat>(sql);

            var result = flat
                .GroupBy(x => x.UserId)
                .Select(x => new UserSecurityViewModel.UserWithSecurity
                {
                    UserId = x.Key,
                    SecurityName = x.Select(y => y.Name).ToList()
                })
                .ToList();

            return result;
        }

        public UserSecurityViewModel GetViewModelForUsersSlashSecurity()
        {
            var vm = new UserSecurityViewModel
                         {
                             Users = GetUsersWithSecurity(),
                             AvailableSecurity = db.Fetch<Security>()
                                 .Select(x => new SelectListItem {Text = x.Name, Value = x.SecurityId.ToString()})
                                 .ToList()
                         };
            return vm;
        }

    }

    public class UserIdWithSecurityFlat
    {
        public int UserId { get; set; }
        public string Name { get; set; }
    }
    
    public class UserSecurityViewModel
    {
        public UserSecurityViewModel()
        {
            Users = new List<UserWithSecurity>();
            AvailableSecurity = new List<SelectListItem>();
        }

        public List<SelectListItem> AvailableSecurity { get; set; }
        public List<UserWithSecurity> Users { get; set; }

        public class UserWithSecurity
        {
            public int UserId { get; set; }
            public List<string> SecurityName { get; set; }
        }
       
    }