none
C# - Dealing with many-to-many without an ORM RRS feed

  • Question

  • I have two database tables that are many-to-many

    tblBook idBook Title Writer ect...

    tblBookGenre id idBook idGenre

    tblGenre idGenre NameGenre

    And tblBook idBook Title Writer ect...

    tblBookTag id idBook idGenre

    tblTag idTag NameTag

    public class Book:INotifyPropertyChanged
        {
            Connection con = new Connection();
    
            private int _idBook;
            private string _title;
            private List<Genre> _genres;
            private List<Tag> _tag;
            private ObservableCollection<Book> _books;
    
          public int IdBook
            {
                get { return _idBook; }
                set { _idBook = value; }
            }
    
            public string Title
            {
                get { return _title; }
                set { _title = value; }
            }
    
           public List<Genre> Genres
            {
                get { return _genres; }
                set { _genres = value; }
            }
    
            public List<Tag> Tags
            {
                get { return _tag; }
                set { _tag = value; }
            }
    
            public ObservableCollection<Book> Books
            {
                get { return _books; }
                set { _books = value; }
            }
           public Book()
            {
                Tags = new List<Tag>();
                Books = new ObservableCollection<Book>();
                Genres = new List<Genre>();
            }

    I use a sqlcommand and reader to read from the database

      public ObservableCollection<Book> GetBooks()
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con.con;
    
                cmd.CommandText = "SELECT idBook,Title FROM tblBook WHERE Active = 1 ORDER BY b.idBook";
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        Book book = new Book();
    
                        var id = Convert.ToInt32(rd["IdBook"]);
                        var title = rd["Title"].ToString();
    
                        book.IdBook = id;
                        book.Title = title;
    
                        List<Tag> tags = GetTagsThatMatch(id);
                        book.Tags = tags;
                        book.Genres = GetGenresThatMatch(id);
    
                        Books.Add(book);
    
                    }
                    rd.Close();
                }
                con.Close();
                return Books;
            }


    public ObservableCollection<Tag> GetTagsThatMatch(int id)
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con.con;
                cmd.CommandText = "SELECT t.IdTag,t.Name FROM tblTag t LEFT OUTER JOIN tblBookTag bt ON bt.idTag = t.idTag WHERE bt.idBook = @ID";
                cmd.Parameters.AddWithValue("@ID", id);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        Tag tag = new Tag();
                        tag.IdTag = Convert.ToInt32(rd["IdTag"]);
                        tag.Name = rd["Name"].ToString();
                        Tags.Add(tag);
                    }
                }
                return Tags;
            }


         public List<Genre> GetGenresThatMatch(int id)
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con.con;
                cmd.CommandText = "SELECT g.Name FROM tblGenre g LEFT JOIN tblBookGenre bg ON g.IdGenre = bg.IdGenre LEFT OUTER JOIN tblBook b ON bg.IdBook = b.IdBook WHERE b.IdBook = @Id";
                cmd.Parameters.AddWithValue("@ID", id);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        Genre genre = new Genre();
                        genre.Name = rd["Name"].ToString();
                        Genres.Add(genre);
                    }
                }
                return Genres;
            }

    Problem is it displays all genres and tags for all books

    Book 1 - Genre 1, Genre 2, Genre3 - Tag1, Tag2, Tag3

    Book 2 - Genre 1, Genre 2, Genre3 - Tag1, Tag2, Tag3

    When it should display only the ones that match

    Book 1- Genre 1, Genre 2 -Tag1, Tag2

    Book 2 - Genre3 -Tag3

    My question would be how to populate an observablecollection from many to many. Should I maybe use a sql join query like this 

    SELECT b.IdBook,b.Title,t.IdTag,t.Name,g.IdGenre,g.Name as NameGenre FROM tblBook b LEFT OUTER JOIN tblBookGenre bg ON bg.IdBook = b.idBook LEFT OUTER JOIN tblGenre g ON g.IdGenre = bg.IdGenre LEFT OUTER JOIN tblBookTag bt ON bt.IdBook = b.IdBook LEFT OUTER JOIN tblTag t ON t.IdTag = bt.IdTag 

    Problem is all the duplicates I get then I don't know how to deal with them.

    I would really love to do this without an ORM and it seems like its simple, but I cannot find a lot of resources on it without an ORM. Also any articles or examples would be appreciated.

    Thursday, May 16, 2019 7:45 PM

All replies


  • I use a sqlcommand and reader to read from the database

      public ObservableCollection<Book> GetBooks()
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con.con;
    
                cmd.CommandText = "SELECT idBook,Title FROM tblBook WHERE Active = 1 ORDER BY b.idBook";
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        Book book = new Book();
    
                        var id = Convert.ToInt32(rd["IdBook"]);
                        var title = rd["Title"].ToString();
    
                        book.IdBook = id;
                        book.Title = title;
    
                        List<Tag> tags = GetTagsThatMatch(id);
                        book.Tags = tags;
                        book.Genres = GetGenresThatMatch(id);
    
                        Books.Add(book);
    
                    }
                    rd.Close();
                }
                con.Close();
                return Books;
            }

    Hi lskra95,

    Please take a look the code above, I think you have some issue. You could need to use Group by to count Book, like this:

    select IdBook, COUNT(*) from book group by IdBook

    Then for each this collection to get Book with different IdBook. One Book has only IdBook, has one collection books, so please take a care with Book.Books and Observablecollection<Book>.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 17, 2019 7:34 AM
    Moderator
  • My question would be how to populate an observablecollection from many to many. Should I maybe use a sql join query like this 

    It should be two separate reads. One read should be for the parent record and creating a custom object for the parent record. And within the parent object you create a List<T> for the children objects.

    You then take the parent record's primary-key, which is the foreign-key to all child records, do the reads for the child records, make child objects and load them into the parent object's collection/List<T>   for the children objects.  

    That's how it's done. That's how an ORM does it under the hood.

    Friday, May 17, 2019 8:31 PM
  • Something like this 

            public ObservableCollection<Book> GetBooks()
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con.con;
    
                Book book = new Book();
                List<int> IdBooks=  new List<int>();
                List<Tag> tags = new List<Tag>();
               
                cmd.CommandText = "SELECT b.idBook,b.Title,b.Writer,t.IdTypeOfBook,t.Type FROM tblBook b LEFT OUTER JOIN tblTypeOfBook t ON b.idTypeOfBook = t.idTypeOfBook WHERE b.Active = 1 ORDER BY b.idBook";
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        var idBook = Convert.ToInt32(rd["IdBook"]);
                        var title = rd["Title"].ToString();
                        var writer = rd["Writer"].ToString();
                        var idType = Convert.ToInt32(rd["IdTypeOfBook"]);
                        var type = rd["Type"].ToString();
    
                        book = new Book()
                        {
                            IdBook = idBook,
                            Title = title
                        };
    
                        book.Tags = tags;
                        Books.Add(book);
    
                        IdBooks.Add(idBook);
                    }
                    rd.Close();
                }
                
                foreach (var idBook in IdBooks)
                {
                    SqlCommand tagCommand = new SqlCommand();
                    tagCommand.Connection = con.con;
    
                    tagCommand.CommandText = "SELECT t.IdTag,t.Name FROM tblTag t LEFT OUTER JOIN tblBookTag bt ON bt.idTag = t.idTag WHERE bt.idBook = @ID";
                    tagCommand.Parameters.AddWithValue("@ID", idBook);
    
    
                    SqlDataReader tagReader = tagCommand.ExecuteReader();
                    if (tagReader.HasRows)
                    {
                        while (tagReader.Read())
                        {
                            Tag tag = new Tag();
                            var idTag = Convert.ToInt32(tagReader["IdTag"]);
                            var name = tagReader["Name"].ToString();
                            tag.IdTag = idTag;
                            tag.Name = name;
    
                            
                            tags.Add(tag);
                            
                        }
                        tagReader.Close();
                    }
                }
    
    
                con.Close();
                return Books;
            }

    But it still displays all the tags for all the books I am kinda lost on how to display only ones that match

    Saturday, May 18, 2019 3:09 PM
  • Why are you trying to do any join?

    It should be a simple read of all parent records with no join,  getting the parent objects, creating a parent object by itself and with each parent having the collection for its children objects  instanced within each parent object loading the parent object into the parent collection. 

    Then it's a loop on the parent objects in its collection, take an object's primary-key property for an object,  read the  child table based on its foreign-key column,   getting the recods for that particular parent object, creating child objects and loading them into the parent.collection for the children.

    It's either the parent has child objects for it that have been loaded into its child collection, or the child collection for the parent has no objects, because there are no child recods linked to the parent record based on foreign-key in reading the child table.





    • Edited by DA924x Saturday, May 18, 2019 9:05 PM
    Saturday, May 18, 2019 7:45 PM