c# - tipos - relacion varios a varios

Cómo implementar una relación de uno a muchos.

Question

Tengo una relación de uno a muchos proveniente de un procedimiento almacenado. Tengo varias relaciones de uno a muchos en la consulta y estoy tratando de asignar estos campos a un objeto C #. El problema que tengo es que obtengo datos duplicados debido a la relación de uno a muchos. Aquí hay una versión simplificada de mi código:

Aquí están las clases de objetos:

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; }
}

Aquí es cómo estoy recuperando los datos:

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
            }
        }
    }
}

Como puede ver, puede haber varios colores y pasatiempos para una Persona determinada. Por lo general, usaría Entity Framework para resolver este mapeo, pero no estamos autorizados a usar ningún orms. ¿Existe una técnica para desentrañar adecuadamente estos datos?

Answer 1

Creo que el problema no es cómo asignar los datos recuperados a un objeto (por lo que sugeriría usar el enfoque de Koders), sino que la declaración de selección arroja demasiados resultados.

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";

Me parece que las tablas Color y Hobby contienen un PersonId que las asigna a una única persona única. (Por lo tanto, la combinación interna devuelve, por ejemplo, {personId, azul, pesca}, {personId, rojo, pesca}, {personId, azul, natación}, {personId, rojo, natación}

en lugar del deseado {personId, rojo, pesca}, {personId, azul, natación}

En caso de que no haya leído mal esto, sugeriría agregar una columna ColorId y HobbyId a la tabla Person . Si hizo esto, puede recuperar sus datos sin redundancia utilizando

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";

y el enfoque de Koders para vincular el resultado a su clase de Person le dará el resultado deseado.

edición: en realidad, el código de Koders devuelve el resultado correcto de cualquier manera debido a

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

y

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

Answer 2

Creo que en última instancia, todos los enfoques mencionados aquí funcionan. Podríamos mejorar las soluciones centrándonos en el rendimiento.

@Mark Menchavez comentó sobre el impacto en el rendimiento de volver repetidamente a la base de datos cuando comenzamos con una simple lista de personas. Para una lista enorme, este impacto es significativo y debe evitarse tanto como sea posible.

En última instancia, lo mejor es obtener los datos en el menor número posible de fragmentos; en este caso, una pieza será ideal (si las uniones no son demasiado caras). Las bases de datos están optimizadas para trabajar en conjuntos de datos y usaremos esto para evitar la sobrecarga de configurar conexiones múltiples y repetidas (especialmente si estamos pasando el cable a una instancia de Sql ejecutándose en otra máquina).

Usaré el enfoque de @ Luke101 pero solo cambiaré a la Lista a un Diccionario de valores. La búsqueda de claves de hash será más rápida que el uso de la respuesta de Where in @ Koder. También tenga en cuenta que he cambiado el SQL para leerlo como LEFT JOIN para acomodar a aquellas Personas que no tienen un Hobby o Color en el registro y permitir que se devuelvan como NULL (DBNull en .NET).

También tenga en cuenta que debido a la forma de las tablas y los datos, es posible repetir los colores y / o los pasatiempos varias veces, por lo que también debemos verificarlos y no solo asumir que habrá un color y un pasatiempo.

No me molesté en repetir las clases aquí.

        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;
    }

Answer 3

La idea es, mientras se está iterando en el lector, verificar si la ID de persona de fila existente existe en la lista de personas. Si no es así, cree un nuevo objeto de persona y declare dos listas separadas para mantener el pasatiempo y la información de color. Para las iteraciones posteriores, siga rellenando estas dos listas porque siempre serán los mismos datos de personas. Cuando llegue a un nuevo registro para una nueva persona, agregue estas listas al objeto person y comience de nuevo con un nuevo objeto person

A continuación se muestra el código de ejemplo:

                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);
                        }
                    }
                }
            }
        }

Answer 4

Podría usar la siguiente consulta, que devuelve una fila para cada persona. Colors and Hobbies se devuelve como una cadena xml, puedes analizarlo en tu código.

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

A continuación, puede utilizar este código para analizar los colores

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();

Answer 5

Probablemente sea más fácil usar 3 consultas separadas para lograr esto.

Consulta de persona

SELECT * FROM Person

A continuación, haga su bucle while en los resultados de esta consulta.

...
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

}

Answer 6

SqlDataReader admite el conjunto de resultados. Prueba esto.

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);
                        }
                    }
                }
            }
        }