public List <SizeEnum.Size> GetSizeOptions()
{
var sizes = new List <SizeEnum.Size>();
using (var connection = new MySqlConnection(_connectionString))
{
connection.Open();
string query = "SELECT SizeName FROM Size;";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = query;
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
sizes.Add(SizeEnum.GetSize((string)dataReader["SizeName"]));
}
return(sizes);
}
}
public List <User> Select()
{
List <User> users = new List <User>();
using (var connection = new MySqlConnection(_connectionString))
{
connection.Open();
string query = @"SELECT u.Email, u.Password, u.IsBeginner, topic.TopicName, language.LanguageName, size.SizeName FROM
User AS u
LEFT JOIN(SELECT ut.UserId, ut.TopicId, t.TopicName FROM UserTopic AS ut
INNER JOIN Topic AS t ON ut.TopicId = t.TopicId)
AS topic ON u.UserId = topic.UserId
LEFT JOIN(SELECT ul.UserId, ul.LanguageId, l.LanguageName FROM UserLanguage AS ul
INNER JOIN Language AS l ON ul.LanguageId = l.LanguageId)
AS language ON u.UserId = language.UserId
LEFT JOIN(SELECT us.UserId, us.SizeId, s.SizeName FROM UserSize AS us
INNER JOIN Size AS s ON us.SizeId = s.SizeId)
AS size ON u.UserId = size.UserId;";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = query;
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
users.Add(new User
{
Email = (string)dataReader["Email"],
Password = (string)dataReader["Password"],
Preference = new Preference
{
IsBeginner = (bool)dataReader["IsBeginner"],
Topics = new List <string> {
Convert.IsDBNull(dataReader["TopicName"]) ?
null : (string)dataReader["TopicName"]
},
Languages = new List <string> {
Convert.IsDBNull(dataReader["LanguageName"]) ?
null : (string)dataReader["LanguageName"]
},
Sizes = new List <SizeEnum.Size> {
SizeEnum.GetSize((string)dataReader["SizeName"])
}
}
});
}
}
var userGroups = users.GroupBy(x => x.Email);
List <User> cleanedUsers = new List <User>();
foreach (var userGroup in userGroups)
{
var cleanedPreferences = new Preference
{
Languages = userGroup.SelectMany(x => x.Preference.Languages).Distinct().ToList(),
Topics = userGroup.SelectMany(x => x.Preference.Topics).ToList().Distinct().ToList(),
Sizes = userGroup.SelectMany(x => x.Preference.Sizes).Distinct().ToList()
};
cleanedUsers.Add(new User
{
Email = userGroup.Key,
Password = userGroup.First().Password,
Preference = cleanedPreferences
});
}
return(cleanedUsers);
}
