Как реализовать отношения один ко многим

У меня есть отношение "один ко многим", исходящее из хранимой процедуры. У меня есть несколько отношений один ко многим в запросе, и я пытаюсь сопоставить эти поля объекту C#. Проблема, с которой я сталкиваюсь, заключается в том, что я получаю дублирующиеся данные из-за отношений "один ко многим". Вот упрощенная версия моего кода:

Вот классы объектов: 

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Color> FavoriteColors { get; set; }
    public List<Hobby> Hobbies { get; set; }

    public Person()
    {
        FavoriteColors = new List<Color>();
        Hobbies = new List<Hobby>();
    }
}

public class Color
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Hobby
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Вот как я получаю данные: 

using (SqlConnection conn = new SqlConnection("connstring.."))
{
    string sql = @"
                    SELECT 
                        Person.Id AS PersonId, 
                        Person.Name AS PersonName, 
                        Hobby.Id AS HobbyId,
                        Hobby.Name AS HobbyName,
                        Color.Id AS ColorId,
                        Color.Name AS ColorName
                    FROM Person
                    INNER JOIN Color on Person.Id = Color.PersonId
                    INNER JOIN Hobby on Person.Id = Hobby.PersonId";
    using (SqlCommand comm = new SqlCommand(sql, conn))
    {
        using (SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection))
        {
            List<Person> persons = new List<Person>();
            while (reader.Read())
            {
                Person person = new Person();
                //What to do
            }
        }
    }
}

Как вы можете видеть, может быть несколько цветов и хобби для данного Человек. Обычно я использую Entity Framework для решения этого сопоставления, но нам не разрешается использовать какие-либо orms. Есть ли техника, чтобы должным образом нейтрализовать эти данные?

6 24
c# ado.net sqldatareader

6 ответов:

Идея заключается в том, чтобы во время итерации на читателе проверить, существует ли существующий идентификатор строки person в списке person. Если нет, создайте новый объект person и объявите два отдельных списка для хранения информации о хобби и цвете. Для последующих итераций продолжайте заполнять эти два списка, потому что это всегда будут одни и те же данные о персонах. Вы получаете новую запись для нового человека, добавляете эти списки к объекту person и начинаете с нового объекта person

Ниже приведен пример кода: 

                string sql = @"
                SELECT 
                    Person.Id AS PersonId, 
                    Person.Name AS PersonName, 
                    Hobby.Id AS HobbyId,
                    Hobby.Name AS HobbyName,
                    Color.Id AS ColorId,
                    Color.Name AS ColorName
                FROM Person
                INNER JOIN Color on Person.Id = Color.PersonId
                INNER JOIN Hobby on Person.Id = Hobby.PersonId
                Order By PersonId"; // Order By is required to get the person data sorted as per the person id
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                using (SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    List<Person> persons = new List<Person>();
                    while (reader.Read())
                    {
                        var personId = reader.GetInt32(0);
                        var personName = reader.GetString(1);
                        var hobbyId = reader.GetInt32(3);
                        var hobbyName = reader.GetString(4);
                        var colorId = reader.GetInt32(5);
                        var colorName = reader.GetString(6);

                        var person = persons.Where(p => p.Id == personId).FirstOrDefault();
                        if (person == null)
                        {
                            person = new Person();
                            person.Id = personId;
                            person.Name = personName;

                            hobby = new Hobby() { Id = hobbyId, Name = hobbyName };
                            color = new Color() { Id = colorId, Name = colorName };

                            person.FavoriteColors = new List<Color>();
                            person.Hobbies = new List<Hobby>();

                            person.FavoriteColors.Add(color);
                            person.Hobbies.Add(hobby);

                            persons.Add(person);
                        }
                        else
                        {
                            hobby = new Hobby() { Id = hobbyId, Name = hobbyName };
                            color = new Color() { Id = colorId, Name = colorName };

                            //JT Edit: if the colour/hobby doesn't already exists then add it
                            if (!person.FavoriteColors.Contains(color))
                               person.FavoriteColors.Add(color);

                            if (!person.Hobbies.Contains(hobby))
                               person.Hobbies.Add(hobby);
                        }
                    }
                }
            }
        }
15

Я думаю, что в конечном счете все подходы, упомянутые здесь, работают. Мы могли бы улучшить решения, сосредоточившись на производительности.

@Mark Menchavez прокомментировал влияние на производительность многократного возврата к базе данных, когда мы начинаем с простого списка людей. Для огромного списка это влияние является значительным, и его следует избегать, насколько это возможно.

В конечном счете, лучше всего получить данные в как можно меньшем количестве фрагментов; в этом случае один фрагмент будет идеальным (если соединения не слишком дорогие). Базы данных оптимизированы для работы с наборами данных, и мы будем использовать это, чтобы избежать накладных расходов на установку нескольких повторяющихся соединений (особенно если мы собираемся по проводу к экземпляру Sql, работающему на другой машине).

Я буду использовать подход @Luke101, но просто изменю список на словарь значений. Хэш-поиск ключей будет быстрее, чем использование Where в ответе @Koder. Также обратите внимание, что я изменил SQL, чтобы читать как Левое соединение для размещения тех людей, у которых нет хобби или цвета на записи, и позволяет им быть возвращенными как NULL (DBNull в .NET).

Также обратите внимание, что из-за формы таблиц и данных возможно многократное повторение цветов и/или хобби, поэтому нам нужно проверить и их, а не просто предположить, что будет один цвет и одно хобби.

Я не утруждал себя повторением уроков здесь.

        public static IEnumerable<Person> DataFetcher(string connString)
    {
        Dictionary<int, Person> personDict = new Dictionary<int,Person>(1024);  //1024 was arbitrarily chosen to reduce the number of resizing operations on the underlying arrays; 
                                                                                //we can rather issue a count first to get the number of rows that will be returned (probably divided by 2).

        using (SqlConnection conn = new SqlConnection(connString))
        {
            string sql = @"
                SELECT 
                    Person.Id AS PersonId, 
                    Person.Name AS PersonName, 
                    Hobby.Id AS HobbyId,
                    Hobby.Name AS HobbyName,
                    Color.Id AS ColorId,
                    Color.Name AS ColorName
                FROM Person
                LEFT JOIN Color on Person.Id = Color.PersonId
                LEFT JOIN Hobby on Person.Id = Hobby.PersonId";

            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                using (SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        int personId = reader.GetInt32(0);
                        string personName = reader.GetString(1);

                        object hobbyIdObject = reader.GetValue(2);
                        object hobbyNameObject = reader.GetValue(3);
                        object colorIdObject = reader.GetValue(4);
                        object colorNameObject = reader.GetValue(5);

                        Person person;

                        personDict.TryGetValue(personId, out person);

                        if (person == null)
                        {
                            person = new Person
                            {
                                Id = personId,
                                Name = personName,

                                FavoriteColors = new List<Color>(),
                                Hobbies = new List<Hobby>()
                            };

                            personDict[personId] = person;
                        }

                        if (!Convert.IsDBNull(hobbyIdObject))
                        {
                            int hobbyId = Convert.ToInt32(hobbyIdObject);
                            Hobby hobby = person.Hobbies.FirstOrDefault(ent => ent.Id == hobbyId);

                            if (hobby == null)
                            {
                                hobby = new Hobby
                                {
                                    Id = hobbyId,
                                    Name = hobbyNameObject.ToString()
                                };

                                person.Hobbies.Add(hobby);
                            }
                        }

                        if (!Convert.IsDBNull(colorIdObject))
                        {
                            int colorId = Convert.ToInt32(colorIdObject);
                            Color color = person.FavoriteColors.FirstOrDefault(ent => ent.Id == colorId);

                            if (color == null)
                            {
                                color = new Color
                                {
                                    Id = colorId,
                                    Name = colorNameObject.ToString()
                                };

                                person.FavoriteColors.Add(color);
                            }
                        }
                    }
                }
            }
        }

        return personDict.Values;
    }
11

SqlDataReader поддерживает результирующий набор. Попробовать это.

using (SqlConnection connection = new SqlConnection("connection string here"))
        {
            using (SqlCommand command = new SqlCommand
                   ("SELECT Id, Name FROM Person WHERE Id=1; SELECT Id, Name FROM FavoriteColors WHERE PersonId=1;SELECT Id, Name FROM Hobbies WHERE PersonId=1", connection))
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    Person p = new Person();
                    while (reader.Read())
                    {
                        p.Id = reader.GetInteger(0);
                        p.Name = reader.GetString(1);
                    }

                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            var clr = new Color();
                            clr.Id = reader.GetInteger(0);
                            clr.Name = reader.GetString(1);
                            p.FavoriteColors.Add(clr);
                        }
                    }
                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            var hby = new Hobby();
                            hby.Id = reader.GetInteger(0);
                            hby.Name = reader.GetString(1);
                            p.Hobbies.Add(clr);
                        }
                    }
                }
            }
        }
5

Для этого, вероятно, проще использовать 3 отдельных запроса.

Запрос лица 

SELECT * FROM Person

Затем выполните цикл while по результатам этого запроса.

...
var persons = new List<Person>();
while (reader.Read())
{
    var person = new Person();
    Person.Id = reader.GetInt32(0);
    ... // populate the other Person properties as required

    // Get list of hobbies for this person
    // Use a query to get hobbies for this person id
    // e.g. "SELECT * FROM Hobby WHERE Hobby.PersonId = " + Person.Id

    // Get a list of colours
    // Use a query to get colours for this person id

}
3

Я думаю, что проблема заключается не в том, как сопоставить полученные данные с объектом (для этого я бы предложил использовать подход кодеров), а в том, что оператор select возвращает слишком много результатов.

SELECT 
    Person.Id AS PersonId, 
    Person.Name AS PersonName, 
    Hobby.Id AS HobbyId,
    Hobby.Name AS HobbyName,
    Color.Id AS ColorId,
    Color.Name AS ColorName
FROM Person
INNER JOIN Color on Person.Id = Color.PersonId
INNER JOIN Hobby on Person.Id = Hobby.PersonId";

Мне кажется, что таблицы Color и Hobby содержат PersonId, которые присваивают их одному уникальному человеку. (Таким образом, внутреннее соединение, т. е. возвращает {personId, синий, рыбалка}, {personId, красный, рыбная ловля}, {personId, синий, плавательный}, {personId, красный, плавательный}

Вместо желанный {personId, красный, рыбалка}, {personId, синий, плавание}

В случае, если я не пропустил это, я бы предложил вместо этого добавить столбец ColorId и HobbyId в таблицу Person. Если вы сделали это, вы можете получить ваши данные без избыточности с помощью 

SELECT 
    Person.Id AS PersonId, 
    Person.Name AS PersonName, 
    Hobby.Id AS HobbyId,
    Hobby.Name AS HobbyName,
    Color.Id AS ColorId,
    Color.Name AS ColorName
FROM Person
INNER JOIN Color on Person.ColorId = Color.Id
INNER JOIN Hobby on Person.HobbyId = Hobby.Id";

И подход кодеров для привязки результата к вашему классу Person даст вам желаемый результат.

Edit: на самом деле кодер возвращает правильный результат в любом случае из-за 

if (!person.FavoriteColors.Contains(color))

И 

if (!person.Hobbies.Contains(hobby))
3

Вы можете использовать запрос ниже, который возвращает одну строку для каждого человека. Colors and Hobbies возвращается в виде xml-строки, вы можете разобрать ее в своем коде.

select p.personId, p.personName
,cast((select colorId,colorName from Color as c where c.personId = p.personId for xml raw) as nvarchar(max)) as Colors
,cast((select hobbyId,hobbyName from Hobby as h where h.personId = p.personId for xml raw) as nvarchar(max)) as Hobbies
from Person as p

Затем вы можете использовать этот код для разбора цветов 

var root = XElement.Parse("<root>" + colorXml + "</root>");
var colors = root.Nodes()
    .Where(n => n.NodeType == XmlNodeType.Element)
    .Select(node =>
    {
        var element = (XElement)node;
        return new Color()
        {
            Id = Convert.ToInt32(element.Attribute("colorId").Value),
            Name = element.Attribute("colorName").Value
        };
    }).ToList();
2